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

[23 Feb 2013 15:49] Balázs Hinel
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)
[23 Feb 2013 17:30] Valeriy Kravchuk
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)
[24 Feb 2013 15:14] Jørgen Løland
Thank you for the bug report. Verified as described
[25 Mar 2013 18:32] Paul DuBois
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.