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:
None 
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
Description:
SELECT DISTINCT .... ORDER BY ... yields unexpected result. It returns random values which are not even in the table. It happens when querying using the index with a table with composite keys and no DEFAULT values for columns given.

How to repeat:
Step 1. Create Table cw2 as below,
CREATE TABLE IF NOT EXISTS `cw2` (
	`k` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`id1` INT(10) UNSIGNED NOT NULL,
	`id4` INT(10) UNSIGNED NOT NULL,
	`id3` BIGINT(20) NOT NULL,
	`id2` INT(11) NOT NULL,
	PRIMARY KEY (`k`),
	INDEX `id3` (`id3`),
	INDEX `combinedIndex` (`id1`, `id2`, `id3`),
	INDEX `id4` (`id4`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Step 2. Insert the following data into cw2
INSERT INTO cw2 (id1, id4, id3, id2) VALUES (1,0,2,3),(1,10,2,3),(1,20,2,3),(1,0,4,5),(1,10,4,2),(1,20,3,5), (1,0,5,3),(1,10,4,3),(1,20,5,3), (1,0,2,3),(1,10,2,3),(1,20,2,3),(1,0,4,5),(1,10,4,2),(1,20,3,5), (1,0,5,3),(1,10,4,3),(1,20,5,3),(1,0,2,3),(1,10,2,3),(1,20,2,3),(1,0,4,5),(1,10,4,2),(1,20,3,5), (1,0,5,3),(1,10,4,3),(1,20,5,3),(1,0,2,3),(1,10,2,3),(1,20,2,3),(1,0,4,5),(1,10,4,2),(1,20,3,5), (1,0,5,3),(1,10,4,3),(1,20,5,3),(1,0,2,3),(1,10,2,3),(1,20,2,3),(1,0,4,5),(1,10,4,2),(1,20,3,5), (1,0,5,3),(1,10,4,3),(1,20,5,3),(1,0,2,3),(1,10,2,3),(1,20,2,3),(1,0,4,5),(1,10,4,2),(1,20,3,5), (1,0,5,3),(1,10,4,3),(1,20,5,3),(1,0,2,3),(1,10,2,3),(1,20,2,3),(1,0,4,5),(1,10,4,2),(1,20,3,5), (1,0,5,3),(1,10,4,3),(1,20,5,3),(1,0,2,3),(1,10,2,3),(1,20,2,3),(1,0,4,5),(1,10,4,2),(1,20,3,5), (1,0,5,3),(1,10,4,3),(1,20,5,3),(1,0,2,3),(1,10,2,3),(1,20,2,3),(1,0,4,5),(1,10,4,2),(1,20,3,5), (1,0,5,3),(1,10,4,3),(1,20,5,3),(1,0,2,3),(1,10,2,3),(1,20,2,3),(1,0,4,5),(1,10,4,2),(1,20,3,5), (1,0,5,3),(1,10,4,3),(1,20,5,3),(1,0,2,3),(1,10,2,3),(1,20,2,3),(1,0,4,5),(1,10,4,2),(1,20,3,5), (1,0,5,3),(1,10,4,3),(1,20,5,3),(1,0,2,3),(1,10,2,3),(1,20,2,3),(1,0,4,5),(1,10,4,2),(1,20,3,5), (1,0,5,3),(1,10,4,3),(1,20,5,3),(1,0,2,3),(1,10,2,3),(1,20,2,3),(1,0,4,5),(1,10,4,2),(1,20,3,5), (1,0,5,3),(1,10,4,3),(1,20,5,3), (1,0,2,3),(1,10,2,3),(1,20,2,3),(1,0,4,5),(1,10,4,2),(1,20,3,5);

Step 3. Use the following SQL to query the cw2 table
SELECT DISTINCT id1, id2, id3 FROM cw2 WHERE id1='1' ORDER BY id2, id3;

The SELECT query returns random result, e.g. 

id1	id2	id3
0	-330789000	54341627896163
0	-330789000	54341627896163
0	-330789000	54341627896163
0	-330789000	54341627896163
0	-330789000	54341627896163
0	-330789000	54341627896163
[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