Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.3.9, 10.2(EOL), 10.3(EOL)
-
None
-
Ubuntu 18.04.1
Description
Window functions don't seem to work in ONLY_FULL_GROUP_BY mode.
Using the example queries from the documentation:
CREATE TABLE student (name CHAR(10), test CHAR(10), score TINYINT);
|
|
INSERT INTO student VALUES
|
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
|
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
|
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
|
('Tatiana', 'SQL', 87), ('Tatiana', 'Tuning', 83);
|
|
SET sql_mode = 'ONLY_FULL_GROUP_BY';
|
|
SELECT name, test, score,
|
AVG(score) OVER (PARTITION BY test) AS average_by_test
|
FROM student;
|
The SELECT query fails with an error:
ERROR 1055 (42000): 'database.student.name' isn't in GROUP BY
From my understanding, this is a valid query. It works on MySQL 8.0 (fiddle) and PostgreSQL (fiddle).