Bug #71834 | SELECT DISTINCT ... ORDER BY ... yields wrong results. | ||
---|---|---|---|
Submitted: | 26 Feb 2014 1:52 | Modified: | 26 Feb 2014 19:34 |
Reporter: | Jeffrey Bian (OCA) | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server: General | Severity: | S3 (Non-critical) |
Version: | 5.5.14 | OS: | Other (CentOS) |
Assigned to: | CPU Architecture: | Any | |
Tags: | SELECT, SQL |
[26 Feb 2014 1:52]
Jeffrey Bian
[26 Feb 2014 8:10]
MySQL Verification Team
I get this on 5.6.14 and 5.6.16: mysql> SELECT DISTINCT id1, id2, id3 FROM cw2 WHERE id1='1' ORDER BY id2, id3; +-----+-----+-----+ | id1 | id2 | id3 | +-----+-----+-----+ | 1 | 2 | 4 | | 1 | 3 | 2 | | 1 | 3 | 4 | | 1 | 3 | 5 | | 1 | 5 | 3 | | 1 | 5 | 4 | +-----+-----+-----+ 6 rows in set (0.59 sec) Can you please run CHECK TABLE `cw2` EXTENDED and make sure it's okay. And upload mysql error log and my.cnf to this bug. Thanks,
[26 Feb 2014 19:30]
Jeffrey Bian
Hi Shane, I looked into the error log but *nothing* pops up there; it's empty. I am attaching the my.cnf file. CHECK TABLE shows everything is OK, as below. mysql> CHECK TABLE `cw2` EXTENDED; +------------+-------+----------+----------+ | Table | Op | Msg_type | Msg_text | +------------+-------+----------+----------+ | camel3.cw2 | check | status | OK | +------------+-------+----------+----------+ 1 row in set (0.72 sec) I did an EXPLAIN and shows, mysql> EXPLAIN SELECT DISTINCT id1, id2, id3 FROM cw2 WHERE id1='1' ORDER BY id2, id3; +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------------------------------------------------+ | 1 | SIMPLE | cw2 | range | combinedIndex | combinedIndex | 16 | NULL | 123 | Using where; Using index for group-by; Using filesort | +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------------------------------------------------+ 1 row in set (0.00 sec) I did run the query again, and this time it shows, mysql> SELECT DISTINCT id1, id2, id3 FROM cw2 WHERE id1='1' ORDER BY id2, id3; +---------+-----------+-----------+ | id1 | id2 | id3 | +---------+-----------+-----------+ | 7889763 | 228036048 | 241083952 | | 7889763 | 228036048 | 241083952 | | 7889763 | 228036048 | 241083952 | | 7889763 | 228036048 | 241083952 | | 7889763 | 228036048 | 241083952 | | 7889763 | 228036048 | 241083952 | +---------+-----------+-----------+ 6 rows in set (0.00 sec)
[26 Feb 2014 19:31]
Jeffrey Bian
The configuration file.
Attachment: my.cnf (application/octet-stream, text), 3.62 KiB.
[26 Feb 2014 19:34]
MySQL Verification Team
I can repeat it on *5.5.14*. But not on 5.5.36. So, this has been fixed and you should consider upgrading....
[26 Feb 2014 19:42]
MySQL Verification Team
To be precise, this has been fixed since 5.5.32