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).
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Affects Version/s | 10.2 [ 14601 ] | |
| Affects Version/s | 10.3 [ 22126 ] |
| Assignee | Varun Gupta [ varun ] |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] |
| Component/s | Optimizer - Window functions [ 13502 ] |
| Status | Open [ 1 ] | In Progress [ 3 ] |
| Assignee | Varun Gupta [ varun ] | Sergei Petrunia [ psergey ] |
| Status | In Progress [ 3 ] | In Review [ 10002 ] |
| Assignee | Sergei Petrunia [ psergey ] | Varun Gupta [ varun ] |
| Status | In Review [ 10002 ] | Stalled [ 10000 ] |
| Fix Version/s | 10.3.11 [ 23141 ] | |
| Fix Version/s | 10.2.19 [ 23207 ] | |
| Fix Version/s | 10.2 [ 14601 ] | |
| Fix Version/s | 10.3 [ 22126 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Stalled [ 10000 ] | Closed [ 6 ] |
| Workflow | MariaDB v3 [ 90216 ] | MariaDB v4 [ 155094 ] |
Patch
http://lists.askmonty.org/pipermail/commits/2018-October/013038.html