|
Originally filed at http://bugs.mysql.com/bug.php?id=68473 by Balázs Hinel:
Here's the SQL query to create and fill the table:
CREATE TABLE `faulty` (
|
`a` int(11) unsigned NOT NULL AUTO_INCREMENT,
|
`b` int(11) unsigned NOT NULL,
|
`c` datetime NOT NULL,
|
PRIMARY KEY (`a`),
|
UNIQUE KEY `b_and_c` (`b`,`c`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
|
|
INSERT INTO `faulty` (`b`, `c`) VALUES
|
(1801, '2013-02-15 09:00:00'),
|
(1802, '2013-02-28 09:00:00'),
|
(1802, '2013-03-01 09:00:00'),
|
(5, '1990-02-15 09:00:00'),
|
(5, '2013-02-15 09:00:00'),
|
(5, '2009-02-15 17:00:00');
|
After this is done, try running this query:
SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
|
It should return the rows where the b attribute is 1802, but it returns random data instead:
mysql> SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
|
+------------+---------------------+
|
| b | c |
|
+------------+---------------------+
|
| 2795961176 | 0000-42-94 96:72:97 |
|
| 2795961176 | 0000-42-94 96:72:97 |
|
+------------+---------------------+
|
2 rows in set (0.00 sec)
|
If you change the number 1802 to number 5 in the query, it is working properly:
mysql> SELECT DISTINCT b, c FROM faulty WHERE b='5' ORDER BY c;
|
+---+---------------------+
|
| b | c |
|
+---+---------------------+
|
| 5 | 1990-02-15 09:00:00 |
|
| 5 | 2009-02-15 17:00:00 |
|
| 5 | 2013-02-15 09:00:00 |
|
+---+---------------------+
|
3 rows in set (0.00 sec)
|
Some more info...
I tested various versions including mysql 5.5.0, 5.5.8, 5.0.92, mariadb 5.1, 5.2, 10.0.1 and all have some form of this bug. (sometimes random data, sometimes it's repeated row:
+------+---------------------+
|
| b | c |
|
+------+---------------------+
|
| 1802 | 2013-03-01 09:00:00 |
|
| 1802 | 2013-03-01 09:00:00 |
|
+------+---------------------+
|
Similar queries and 3 different explains:
mysql [localhost] {msandbox} (test) > explain SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
|
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------------------------------------------------+
|
| 1 | SIMPLE | faulty | range | b_and_c | b_and_c | 12 | NULL | 2 | Using where; Using index for group-by; Using filesort |
|
+------+-------------+--------+-------+---------------+---------+---------+------+------+-------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|
mysql [localhost] {msandbox} (test) > explain SELECT DISTINCT b, c FROM faulty WHERE b='5' ORDER BY c;
|
+------+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
|
| 1 | SIMPLE | faulty | ref | b_and_c | b_and_c | 4 | const | 3 | Using where; Using index |
|
+------+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
|
1 row in set (0.00 sec)
|
|
mysql [localhost] {msandbox} (test) > explain SELECT DISTINCT b, c FROM faulty WHERE b=1802 ORDER BY c;
|
+------+-------------+--------+-------+---------------+---------+---------+------+------+---------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+-------+---------------+---------+---------+------+------+---------------------------------------+
|
| 1 | SIMPLE | faulty | range | b_and_c | b_and_c | 12 | NULL | 2 | Using where; Using index for group-by |
|
+------+-------------+--------+-------+---------------+---------+---------+------+------+---------------------------------------+
|
1 row in set (0.00 sec)
|
|
mysql [localhost] {msandbox} (test) > explain SELECT DISTINCT b, c FROM faulty WHERE b=5 ORDER BY c;
|
+------+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
|
| 1 | SIMPLE | faulty | ref | b_and_c | b_and_c | 4 | const | 3 | Using where; Using index |
|
+------+-------------+--------+------+---------------+---------+---------+-------+------+--------------------------+
|
1 row in set (0.00 sec)
|
With order by c desc yet another plan and the result is ok:
mysql [localhost] {msandbox} (test) > explain SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c DESC;
|
+------+-------------+--------+-------+---------------+---------+---------+------+------+------------------------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------+-------+---------------+---------+---------+------+------+------------------------------------------------------------------------+
|
| 1 | SIMPLE | faulty | range | b_and_c | b_and_c | 12 | NULL | 2 | Using where; Using index for group-by; Using temporary; Using filesort |
|
+------+-------------+--------+-------+---------------+---------+---------+------+------+------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
|