Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Minor
-
Resolution: Not a Bug
-
Affects Version/s: 5.5.30
-
Fix Version/s: 5.5.32
-
Component/s: None
-
Labels:None
-
Environment:Fedora 18
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.
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
patch for mariadb 5.3
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.
ah... or use ORDER BY NULL...
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
Repeatable on 5.3 too and on mysql. I have a patch for mariadb 5.3.