[MDEV-17785] Window functions not working in ONLY_FULL_GROUP_BY mode II - Jira

XML

Word

Printable

This is a follow-up to ~~MDEV-17525~~.

The patch fixed the AVG() function from the example query, but other window functions still don't work in ONLY_FULL_GROUP_BY mode.

Using a different example from the documentation:

CREATE TABLE student(course VARCHAR(10), mark int, name varchar(10));

INSERT INTO student VALUES

  ('Maths', 60, 'Thulile'),

  ('Maths', 60, 'Pritha'),

  ('Maths', 70, 'Voitto'),

  ('Maths', 55, 'Chun'),

  ('Biology', 60, 'Bilal'),

   ('Biology', 70, 'Roger');

SET sql_mode = 'ONLY_FULL_GROUP_BY';

SELECT

  RANK() OVER (PARTITION BY course ORDER BY mark DESC) AS rank,

  DENSE_RANK() OVER (PARTITION BY course ORDER BY mark DESC) AS dense_rank,

  ROW_NUMBER() OVER (PARTITION BY course ORDER BY mark DESC) AS row_num,

  course, mark, name

FROM student ORDER BY course, mark DESC;

The SELECT query fails with an error:

Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause