Details
-
Type:
Bug
-
Status: Closed (View Workflow)
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 10.3.11, 10.2, 10.3, 10.4, 10.5
-
Component/s: Optimizer - Window functions
-
Labels:None
-
Environment:Ubuntu 18.04.1
Description
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
Attachments
Issue Links
- is duplicated by
-
MDEV-24409 NTILE example throws: ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
-
- Closed
-