Description:
I have a table with BOOLEAN and TIMESTAMP column and a BTREE index on those columns. Select statements using this index return incorrect results when using >=,<= operators with the TIMESTAMP column.
How to repeat:
CREATE TABLE `DB_TEST_TABLE` (
`TTB_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`TTB_FLAG` tinyint(1) NOT NULL DEFAULT '0',
`TTB_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`TTB_ID`),
KEY `IDX_FLAG_DATE` (`TTB_FLAG`,`TTB_DATE`)
) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
INSERT INTO DB_TEST_TABLE (`TTB_FLAG`, `TTB_DATE`) VALUES
(1,'2010-03-22 15:34:44'),
(1,'2010-03-22 15:36:50'),
(1,'2010-03-22 15:36:53'),
(0,'2010-03-22 15:36:57'),
(0,'2010-03-22 15:36:59'),
(1,'2010-03-22 15:37:02'),
(1,'2010-03-22 15:37:04');
My query is:
select TTB_FLAG, TTB_DATE from DB_TEST_TABLE force index (IDX_FLAG_DATE) where TTB_FLAG = TRUE AND TTB_DATE >= '2010-03-22 15:34:44';
mysql> select TTB_FLAG, TTB_DATE from DB_TEST_TABLE where TTB_FLAG = 1 AND TTB_DATE >= '2010-03-22 15:34:44' ORDER BY TTB_DATE DESC;
+----------+---------------------+
| TTB_FLAG | TTB_DATE |
+----------+---------------------+
| 1 | 2010-03-22 15:37:04 |
| 1 | 2010-03-22 15:37:02 |
| 1 | 2010-03-22 15:36:53 |
| 1 | 2010-03-22 15:36:50 |
| 1 | 2010-03-22 15:34:44 |
+----------+---------------------+
5 rows in set (0.00 sec)
this query works fine, however when executed with JDBC the actual query is:
mysql> select TTB_FLAG, TTB_DATE from DB_TEST_TABLE where TTB_FLAG = 1 AND TTB_DATE >= '2010-03-22 15:34:44.0' ORDER BY TTB_DATE DESC;
+----------+---------------------+
| TTB_FLAG | TTB_DATE |
+----------+---------------------+
| 1 | 2010-03-22 15:37:04 |
| 1 | 2010-03-22 15:37:02 |
| 1 | 2010-03-22 15:36:53 |
| 1 | 2010-03-22 15:36:50 |
+----------+---------------------+
4 rows in set (0.00 sec)
(timestamp value with .0) and this query does not return the row with the equal timestmp value.
mysql> explain select TTB_FLAG, TTB_DATE from DB_TEST_TABLE where TTB_FLAG = 1 AND TTB_DATE >= '2010-03-22 15:34:44' ORDER BY TTB_DATE DESC;
+----+-------------+---------------+-------+---------------+---------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+---------------+---------+------+------+--------------------------+
| 1 | SIMPLE | DB_TEST_TABLE | range | IDX_FLAG_DATE | IDX_FLAG_DATE | 5 | NULL | 4 | Using where; Using index |
+----+-------------+---------------+-------+---------------+---------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select TTB_FLAG, TTB_DATE from DB_TEST_TABLE where TTB_FLAG = 1 AND TTB_DATE >= '2010-03-22 15:34:44.0' ORDER BY TTB_DATE DESC;
+----+-------------+---------------+-------+---------------+---------------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+-------+---------------+---------------+---------+------+------+--------------------------+
| 1 | SIMPLE | DB_TEST_TABLE | range | IDX_FLAG_DATE | IDX_FLAG_DATE | 5 | NULL | 3 | Using where; Using index |
+----+-------------+---------------+-------+---------------+---------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
Suggested fix:
The problem is with timestamp representation with milliseconds. And it only occurs when an index is used in the where clause.
