[MDEV-4561] GROUP BY on a view does not sort properly Created: 2013-05-22  Updated: 2013-07-10  Resolved: 2013-07-10

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.30
Fix Version/s: 5.5.32

Type: Bug Priority: Minor
Reporter: Alexander Barkov Assignee: Oleksandr Byelkin
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Fedora 18

Attachments: File maria53_mdev4561.diff    

 Description    

DROP TABLE IF EXISTS t1;
 
DROP VIEW IF EXISTS v1;
 
CREATE TABLE t1 (name VARCHAR(32), race VARCHAR(32), number INT); 
INSERT INTO t1 VALUES ('Bill','cat',1),('Donald','dog', 1),('Donald','fish',3),('John','dog',2),('Kevin','bird',6),('Kevin','cat',2),('Lisbeth','rabbit',2),('Mary','cat',1),('Mary','dog',1);
 
CREATE VIEW v1 AS SELECT * FROM t1 ORDER BY race;
 
SELECT name,race,sum(number) FROM t1 GROUP BY name,race;
 
SELECT name,race,sum(number) FROM v1 GROUP BY name,race;

The first SELECT query (from the table) returns results sorted by
name,race as expected:

+---------+--------+-------------+
 
| name    | race   | sum(number) |
 
+---------+--------+-------------+
 
| Bill    | cat    |           1 |
 
| Donald  | dog    |           1 |
 
| Donald  | fish   |           3 |
 
| John    | dog    |           2 |
 
| Kevin   | bird   |           6 |
 
| Kevin   | cat    |           2 |
 
| Lisbeth | rabbit |           2 |
 
| Mary    | cat    |           1 |
 
| Mary    | dog    |           1 |
 
+---------+--------+-------------+

The second SELECT (from the view) sorts results by race:

+---------+--------+-------------+
 
| name    | race   | sum(number) |
 
+---------+--------+-------------+
 
| Kevin   | bird   |           6 |
 
| Bill    | cat    |           1 |
 
| Mary    | cat    |           1 |
 
| Kevin   | cat    |           2 |
 
| John    | dog    |           2 |
 
| Donald  | dog    |           1 |
 
| Mary    | dog    |           1 |
 
| Donald  | fish   |           3 |
 
| Lisbeth | rabbit |           2 |
 
+---------+--------+-------------+

This looks like a bug.

According to the manual, a GROUP BY query should sort
by the groupping fields by default, unless ORDER BY NULL
is specified.

 Comments   
Comment by Patryk Pomykalski [ 2013-06-07 ]

Repeatable on 5.3 too and on mysql. I have a patch for mariadb 5.3.

Comment by Patryk Pomykalski [ 2013-06-07 ]

patch for mariadb 5.3

Comment by Oleksandr Byelkin [ 2013-07-10 ]

According to the manual (http://dev.mysql.com/doc/refman/5.0/en/create-view.html) :

ORDER BY is permitted in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY.

i.e. the query has no its own ORDER BY so used ORDER BY from the view.
SELECT from the view equivalent to:

SELECT name,race,sum(number) FROM t1 GROUP BY name,race ORDER BY race.

One should not use ORDER BY in the VIEW definition if do not want queries with this view sorted accordingly by default.

Comment by Oleksandr Byelkin [ 2013-07-10 ]

ah... or use ORDER BY NULL...

Comment by Sergei Golubchik [ 2013-07-10 ]

Okay. While the manual, indeed, says that «If you use GROUP BY, output rows are sorted according to the GROUP BY columns as if you had an ORDER BY for the same columns.» it also adds «Relying on implicit GROUP BY sorting in MySQL 5.5 is deprecated. To achieve a specific sort order of grouped results, it is preferable to use an explicit ORDER BY clause. GROUP BY sorting is a MySQL extension that may change in a future release; for example, to make it possible for the optimizer to order groupings in whatever manner it deems most efficient and to avoid the sorting overhead»

Thus we won't fix this bug in 5.5

Generated at Thu Feb 08 06:57:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.