MySQL Bugs: #68473: A simple select query returns random data

Bug #68473	A simple select query returns random data
Submitted:	23 Feb 2013 15:49	Modified:	25 Mar 2013 18:32
Reporter:	Balázs Hinel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.5.24, 5.5.29, 5.5.30	OS:	Any
Assigned to:	Jørgen Løland	CPU Architecture:	Any

Description:
I found a table, which is filled with properly, and one exact SQL query returns random data from it.

How to repeat:
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)

5.6.10 is also affected:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3314 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 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 ;
Query OK, 0 rows affected (4.12 sec)

mysql>
mysql> 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');
Query OK, 6 rows affected (0.53 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT DISTINCT b, c FROM faulty WHERE b='1802' ORDER BY c;
+---+---------------------+
| b | c                   |
+---+---------------------+
| 0 | 1897-01-04 17:40:00 |
| 0 | 1897-01-04 17:40:00 |
+---+---------------------+
2 rows in set (0.19 sec)

There is a workaround (use proper data type):

mysql> SELECT DISTINCT b, c FROM faulty WHERE b=1802 ORDER BY c;
+------+---------------------+
| b    | c                   |
+------+---------------------+
| 1802 | 2013-02-28 09:00:00 |
| 1802 | 2013-03-01 09:00:00 |
+------+---------------------+
2 rows in set (0.00 sec)

Here is the difference:

mysql> 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 | 9       | NULL |
    2 | Using where; Using index for group-by; Using filesort |
+----+-------------+--------+-------+---------------+---------+---------+------+
------+-------------------------------------------------------+
1 row in set (0.03 sec)

mysql> 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 | 9       | NULL |
    2 | Using where; Using index for group-by |
+----+-------------+--------+-------+---------------+---------+---------+------+
------+---------------------------------------+
1 row in set (0.00 sec)

Thank you for the bug report. Verified as described

Noted in 5.5.32, 5.6.12, 5.7.2 changelogs.

If Loose Index Scan was used to evaluate a query that compared an
integer column to an integer specified as a quoted string (for
example, col_name = '1'), the query could return incorrect results.