Description:
When doing a SELECT on a table with a datetime column, constants provided in the WHERE are not rounded *unless* the column is defined as an index *and* the optimizer actually decides to use the index. This leads to inconsistent query results.
How to repeat:
Example:
mysql> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`id` int(11) NOT NULL,
`ts` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from t2;
+----+---------------------+
| id | ts |
+----+---------------------+
| 1 | 2019-05-27 06:59:08 |
| 2 | 2019-05-27 06:59:08 |
| 3 | 2019-05-27 06:59:08 |
| 4 | 2019-05-27 07:09:26 |
| 5 | 2019-05-27 06:59:08 |
| 6 | 2019-05-27 06:59:08 |
| 7 | 2019-06-27 06:59:08 |
| 8 | 2019-06-27 06:59:09 |
| 9 | 2019-06-27 06:59:09 |
| 10 | 2019-06-27 06:59:09 |
| 11 | 2016-01-01 09:00:00 |
+----+---------------------+
11 rows in set (0.00 sec)
mysql> select * from t2 where ts='2019-05-27 06:59:08.123456';
Empty set (0.00 sec)
mysql> create index tsindex on t2(ts);
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from t2 where ts='2019-05-27 06:59:08.123456';
+----+---------------------+
| id | ts |
+----+---------------------+
| 1 | 2019-05-27 06:59:08 |
| 2 | 2019-05-27 06:59:08 |
| 3 | 2019-05-27 06:59:08 |
| 5 | 2019-05-27 06:59:08 |
| 6 | 2019-05-27 06:59:08 |
+----+---------------------+
5 rows in set (0.00 sec)
Ignore the index, and the result is different:
mysql> select * from t2 use index() where ts='2019-05-27 06:59:08.123456';
Empty set (0.00 sec)
Use the pkey, and the optimizer doesn't use the index on the datetime column, and again, no rows:
mysql> select * from t2 where id=1 and ts='2019-05-27 06:59:08.123456';
Empty set (0.00 sec)
Give that same query a hint to use the index on the datetime column, and:
mysql> select * from t2 use index(ts) where id=1 and ts='2019-05-27 06:59:08.123456';
+----+---------------------+
| id | ts |
+----+---------------------+
| 1 | 2019-05-27 06:59:08 |
+----+---------------------+
1 row in set (0.00 sec)
Suggested fix:
Do not round constants to match index precision of temporals on SELECT.
Description: When doing a SELECT on a table with a datetime column, constants provided in the WHERE are not rounded *unless* the column is defined as an index *and* the optimizer actually decides to use the index. This leads to inconsistent query results. How to repeat: Example: mysql> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `id` int(11) NOT NULL, `ts` datetime NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql> select * from t2; +----+---------------------+ | id | ts | +----+---------------------+ | 1 | 2019-05-27 06:59:08 | | 2 | 2019-05-27 06:59:08 | | 3 | 2019-05-27 06:59:08 | | 4 | 2019-05-27 07:09:26 | | 5 | 2019-05-27 06:59:08 | | 6 | 2019-05-27 06:59:08 | | 7 | 2019-06-27 06:59:08 | | 8 | 2019-06-27 06:59:09 | | 9 | 2019-06-27 06:59:09 | | 10 | 2019-06-27 06:59:09 | | 11 | 2016-01-01 09:00:00 | +----+---------------------+ 11 rows in set (0.00 sec) mysql> select * from t2 where ts='2019-05-27 06:59:08.123456'; Empty set (0.00 sec) mysql> create index tsindex on t2(ts); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from t2 where ts='2019-05-27 06:59:08.123456'; +----+---------------------+ | id | ts | +----+---------------------+ | 1 | 2019-05-27 06:59:08 | | 2 | 2019-05-27 06:59:08 | | 3 | 2019-05-27 06:59:08 | | 5 | 2019-05-27 06:59:08 | | 6 | 2019-05-27 06:59:08 | +----+---------------------+ 5 rows in set (0.00 sec) Ignore the index, and the result is different: mysql> select * from t2 use index() where ts='2019-05-27 06:59:08.123456'; Empty set (0.00 sec) Use the pkey, and the optimizer doesn't use the index on the datetime column, and again, no rows: mysql> select * from t2 where id=1 and ts='2019-05-27 06:59:08.123456'; Empty set (0.00 sec) Give that same query a hint to use the index on the datetime column, and: mysql> select * from t2 use index(ts) where id=1 and ts='2019-05-27 06:59:08.123456'; +----+---------------------+ | id | ts | +----+---------------------+ | 1 | 2019-05-27 06:59:08 | +----+---------------------+ 1 row in set (0.00 sec) Suggested fix: Do not round constants to match index precision of temporals on SELECT.