MySQL Bugs: #28281: GROUP BY returning recs in wrong order

Bug #28281	GROUP BY returning recs in wrong order
Submitted:	7 May 2007 14:13	Modified:	19 Jun 2007 13:46
Reporter:	Filipe Martins	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.44-BK, 5.0.37 (Community)	OS:	Windows (XP)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	GROUP BY, Wrong Order

Description:
Hello. When I installed MySQL 5.0.37 I noticed that some of my querys were returning records in a different order. After more than an hour "pulling my hair", I found a strange behavior that I believe indicates a bug in MySQL.

In a nutshell, my query returns a few rows for each category. I'm trying GROUPing them by so I get just the first listed for each category. But, I always get one that isn't nor the first nor the last!

But, there's a case in which I get the right one, don't know why :-)

i-senso
http://www.i-senso.com
Filipe Manuel da Silva Martins

How to repeat:
Run the SQL on the attached file. Do you notice that the output of the two Stored Procs is different, thought it shouldn't?

The only difference between the procs is that one reads directly from "vw_gal_cats_imgs_1" and the other makes a sub-query on it "(select * from vw_gal_cats_imgs_1)"...

Test file with all the SQL to make the "bug" happen

Attachment: groupby-test.sql (application/octet-stream, text), 7.66 KiB.

(My mistake: the OS is MS Windows XP)

Thank you for a bug report. Verified with latest 5.0.44-BK on Linux using your test case uploaded (even without SPs):

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.44-debug |
+--------------+
1 row in set (0.02 sec)

mysql> explain select T.`IdGalCat` AS `IdGalCat`,T.`NomeImg`,T.`l` AS `l`,T.`a`
 AS `a`
    -> from vw_gal_cats_imgs_1 T
    -> group by T.`IdGalCat`;
+----+-------------+--------------+------+---------------+------+---------+-----
-+------+---------------------------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref
 | rows | Extra                           |
+----+-------------+--------------+------+---------------+------+---------+-----
-+------+---------------------------------+
|  1 | SIMPLE      | gal_entradas | ALL  | NULL          | NULL | NULL    | NULL
 |   16 | Using temporary; Using filesort |
|  1 | SIMPLE      | gal_imgs     | ALL  | NULL          | NULL | NULL    | NULL
 |   38 | Using where                     |
+----+-------------+--------------+------+---------------+------+---------+-----
-+------+---------------------------------+
2 rows in set (0.01 sec)

mysql> explain select T.`IdGalCat` AS `IdGalCat`,T.`NomeImg`,T.`l` AS `l`,T.`a`
 AS `a` from (select * from vw_gal_cats_imgs_1) T group by T.`IdGalCat`;
+----+-------------+--------------+------+---------------+------+---------+-----
-+------+---------------------------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref
 | rows | Extra                           |
+----+-------------+--------------+------+---------------+------+---------+-----
-+------+---------------------------------+
|  1 | PRIMARY     | <derived2>   | ALL  | NULL          | NULL | NULL    | NULL
 |   38 | Using temporary; Using filesort |
|  2 | DERIVED     | gal_entradas | ALL  | NULL          | NULL | NULL    | NULL
 |   16 | Using temporary; Using filesort |
|  2 | DERIVED     | gal_imgs     | ALL  | NULL          | NULL | NULL    | NULL
 |   38 | Using where                     |
+----+-------------+--------------+------+---------------+------+---------+-----
-+------+---------------------------------+
3 rows in set (0.01 sec)

mysql> select T.`IdGalCat` AS `IdGalCat`,T.`NomeImg`,T.`l` AS `l`,T.`a` AS `a`
from vw_gal_cats_imgs_1 T group by T.`IdGalCat`;
+----------+----------+-----+-----+
| IdGalCat | NomeImg  | l   | a   |
+----------+----------+-----+-----+
|        3 | 8396.jpg | 464 | 320 |
|        4 | 8404.jpg | 464 | 320 |
|        5 | 8410.jpg | 464 | 320 |
|        6 | 8439.png | 464 | 320 |
+----------+----------+-----+-----+
4 rows in set (0.00 sec)

mysql> select T.`IdGalCat` AS `IdGalCat`,T.`NomeImg`,T.`l` AS `l`,T.`a` AS `a`
from (select * from vw_gal_cats_imgs_1) T group by T.`IdGalCat`;
+----------+----------+-----+-----+
| IdGalCat | NomeImg  | l   | a   |
+----------+----------+-----+-----+
|        3 | 8403.jpg | 464 | 320 |
|        4 | 8432.jpg | 464 | 320 |
|        5 | 8419.jpg | 464 | 320 |
|        6 | 8439.png | 464 | 320 |
+----------+----------+-----+-----+
4 rows in set (0.01 sec)

The results are different, and, I think, this inconsistency is a bug.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Section 12.11.3. of the reference manual (http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-fields.html) says about using hidden fields (T.`NomeImg` in your case):
"Do not use this feature if the columns you omit from the GROUP BY part are not constant in the group. The server is free to return any value from the group, so the results are indeterminate unless all values are the same."