| Bug #50774 | Wrong resultset when timestamp values are appended with .0 | ||
|---|---|---|---|
| Submitted: | 1 Feb 2010 2:23 | Modified: | 13 May 2011 14:21 |
| Reporter: | Jingqi Xu | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S1 (Critical) |
| Version: | 5.1.44, 5.1.49, 5.5.99, 5.5.6rc | OS: | Any |
| Assigned to: | Martin Hansson | CPU Architecture: | Any |
| Tags: | regression | ||
[1 Feb 2010 7:52]
Sveta Smirnova
Thank you for the report. Verified almost as described: I get wrong results with TIMESTAMP_OK table as well. Similar to bug #47963, but still repeatable with current sources and TIMESTAMP column. Workaround: remove KEY `idxtk01` (`TRADE_TIME`)
[1 Feb 2010 7:53]
Sveta Smirnova
Slightly modified test which I used: CREATE TABLE `TIMESTAMP_OK` ( `TICK_ID` bigint(20) NOT NULL AUTO_INCREMENT, `TRADE_TIME` timestamp NULL DEFAULT NULL, PRIMARY KEY (`TICK_ID`) , KEY `idxtk01` (`TRADE_TIME`) ) ; insert into TIMESTAMP_OK values(null, '2010-02-01 09:31:01'); insert into TIMESTAMP_OK values(null, '2010-02-01 09:31:02'); insert into TIMESTAMP_OK values(null, '2010-02-01 09:31:03'); insert into TIMESTAMP_OK values(null, '2010-02-01 09:31:04'); select * from TIMESTAMP_OK where TRADE_TIME >= '2010-02-01 09:31:02.0'; CREATE TABLE `TIMESTAMP_NG` ( `TICK_ID` bigint(20) NOT NULL AUTO_INCREMENT, `CURRENCY_PAIR` varchar(7) DEFAULT NULL, `TRADE_TIME` timestamp NULL DEFAULT NULL, PRIMARY KEY (`TICK_ID`) , KEY `idxtk01` (`TRADE_TIME`) ) ; insert into TIMESTAMP_NG values(null, 'usd', '2010-02-01 09:31:01'); insert into TIMESTAMP_NG values(null, 'usd', '2010-02-01 09:31:02'); insert into TIMESTAMP_NG values(null, 'usd', '2010-02-01 09:31:03'); insert into TIMESTAMP_NG values(null, 'usd', '2010-02-01 09:31:04'); select * from TIMESTAMP_NG where TRADE_TIME >= '2010-02-01 09:31:02.0'; select * from TIMESTAMP_NG where TRADE_TIME = '2010-02-01 09:31:02.0';
[1 Feb 2010 8:50]
Jingqi Xu
Thank you for your reply, Does `Version: 5.1.44, 5.5.99` mean this issue will be fixed in MySQL 5.1.44?
[1 Feb 2010 8:59]
Tonci Grgin
Just a note: Bug#50710
[1 Feb 2010 9:00]
Sveta Smirnova
Not, this means bug was confirmed in these versions. Version where bug would be fixed has to be decided.
[22 Apr 2010 4:22]
Trent Lloyd
Please re-triage this bug.. both Hibernate (Java) and Connector (Python) format queries in this way (.X nanoseconds)
[22 Apr 2010 6:07]
Jingqi Xu
I modified the Connector/J to work around this issue
[28 Apr 2010 8:32]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/106763 3360 Martin Hansson 2010-04-28 Bug#50774: failed to get the correct resultset when timestamp values are appended with .0 The range optimizer's comparison function, used for creating search keys for incomplete DATE/DATETIME/TIMESTAMP values failed to parse string values if they were compared in a TIMESTAMP context. Fixed by adding an or-branch.
[26 Oct 2010 2:02]
Roel Van de Paar
Additional testcase
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
`added` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
KEY `IDX_UPDATED` (`updated`)
) ENGINE=InnoDB;
INSERT INTO `a` VALUES ('0000-00-00 00:00:00','2010-04-20 12:00:00');
SELECT * FROM `a` WHERE updated >= '2010-04-20 12:00:00' AND updated < '2010-04-20 12:00:04'; /* Works */
SELECT * FROM `a` WHERE updated >= '2010-04-20 12:00:00' AND updated <= '2010-04-20 12:00:04.0'; /* Works */
SELECT * FROM `a` WHERE updated >= '2010-04-20 12:00:00.0' AND updated <= '2010-04-20 12:00:04'; /* Fails */
SELECT * FROM `a` WHERE updated >= ADDDATE('2010-04-20 12:00:00.0',0) AND updated <= '2010-04-20 12:00:04'; /* Workaround */
mysql> SELECT * FROM `a` WHERE updated >= '2010-04-20 12:00:00.0' AND updated <=
'2010-04-20 12:00:04'; /* Fails */
Empty set (0.00 sec)
[26 Oct 2010 2:03]
Roel Van de Paar
Workaround: use ADDDATE(timestamp_col,0) as per the above.
[31 Oct 2010 19:02]
Roel Van de Paar
If we don't support microseconds, we should not allow .0 entries, or possibly/likely better (given all the softwares which pass .x entries automatically, as per the above) silently discard the .x bit [giving a warning], just like we do for other data types: create table t11 (id int) engine=myisam; insert into t11 values (1.1); mysql> select * from t11; +------+ | id | +------+ | 1 | +------+ 1 row in set (0.00 sec)
[7 Feb 2011 15:54]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/130601 3580 Martin Hansson 2011-02-07 Bug#50774: failed to get the correct resultset when timestamp values are appended with .0 Bug is already fixed. This patch contains only the test case.
[7 Apr 2011 23:02]
Simon Mudd
If the bug is fixed please confirm in which versions it is fixed. Without that the comment is meaningless. I don't see in this bug report any mention of a specific version in which the bug has been resolved and do see others asking the same question.
[11 Apr 2011 9:40]
Martin Hansson
This bug was fixed by this patch: http://lists.mysql.com/commits/117428, the fix for Bug#55779.
Description: 1 MySQL connector/j append nanos at the end of timestamp value, such as StringBuffer buf = new StringBuffer(); buf.append(timestampString); buf.append('.'); buf.append(formatNanos(x.getNanos())); buf.append('\''); 2 MySQL server failed to get the correct resultset when using condition like where TRADE_TIME >= '2010-02-01 09:31:02.0' 3 Environment mysql> status; -------------- mysql Ver 14.14 Distrib 5.1.41, for pc-linux-gnu (i686) using readline 5.1 Connection id: 2028664 SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.1.41-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Insert id: 4 Client characterset: cp932 Server characterset: cp932 UNIX socket: /tmp/mysql.sock Uptime: 13 days 17 hours 47 min 5 sec Threads: 104 Questions: 28064076 Slow queries: 15726 Opens: 54370 Flush tables: 1 Open tables: 64 Queries per second avg: 23.638 How to repeat: CREATE TABLE `TIMESTAMP_OK` ( `TICK_ID` bigint(20) NOT NULL AUTO_INCREMENT, `TRADE_TIME` timestamp NULL DEFAULT NULL, PRIMARY KEY (`TICK_ID`), KEY `idxtk01` (`TRADE_TIME`) ) ENGINE=InnoDB DEFAULT CHARSET=cp932; insert into TIMESTAMP_OK values(null, '2010-02-01 09:31:01'); insert into TIMESTAMP_OK values(null, '2010-02-01 09:31:02'); insert into TIMESTAMP_OK values(null, '2010-02-01 09:31:03'); insert into TIMESTAMP_OK values(null, '2010-02-01 09:31:04'); mysql> select * from TIMESTAMP_OK where TRADE_TIME >= '2010-02-01 09:31:02.0'; +---------+---------------------+ | TICK_ID | TRADE_TIME | +---------+---------------------+ | 2 | 2010-02-01 09:31:02 | | 3 | 2010-02-01 09:31:03 | | 4 | 2010-02-01 09:31:04 | +---------+---------------------+ 3 rows in set (0.00 sec) mysql> explain select * from TIMESTAMP_OK where TRADE_TIME >= '2010-02-01 09:31:02.0'; +----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+ | 1 | SIMPLE | TIMESTAMP_OK | index | idxtk01 | idxtk01 | 5 | NULL | 4 | Using where; Using index | +----+-------------+--------------+-------+---------------+---------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) CREATE TABLE `TIMESTAMP_NG` ( `TICK_ID` bigint(20) NOT NULL AUTO_INCREMENT, `CURRENCY_PAIR` varchar(7) DEFAULT NULL, `TRADE_TIME` timestamp NULL DEFAULT NULL, PRIMARY KEY (`TICK_ID`), KEY `idxtk01` (`TRADE_TIME`) ) ENGINE=InnoDB DEFAULT CHARSET=cp932; insert into TIMESTAMP_NG values(null, 'usd', '2010-02-01 09:31:01'); insert into TIMESTAMP_NG values(null, 'usd', '2010-02-01 09:31:02'); insert into TIMESTAMP_NG values(null, 'usd', '2010-02-01 09:31:03'); insert into TIMESTAMP_NG values(null, 'usd', '2010-02-01 09:31:04'); mysql> select * from TIMESTAMP_NG where TRADE_TIME >= '2010-02-01 09:31:02.0'; +---------+---------------+---------------------+ | TICK_ID | CURRENCY_PAIR | TRADE_TIME | +---------+---------------+---------------------+ | 3 | usd | 2010-02-01 09:31:03 | | 4 | usd | 2010-02-01 09:31:04 | +---------+---------------+---------------------+ 2 rows in set (0.00 sec) ######################################## # one row is missing! ######################################## mysql> select * from TIMESTAMP_NG where TRADE_TIME = '2010-02-01 09:31:02.0'; +---------+---------------+---------------------+ | TICK_ID | CURRENCY_PAIR | TRADE_TIME | +---------+---------------+---------------------+ | 2 | usd | 2010-02-01 09:31:02 | +---------+---------------+---------------------+ mysql> explain select * from TIMESTAMP_NG where TRADE_TIME >= '2010-02-01 09:31:02.0'; +----+-------------+--------------+-------+---------------+---------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+-------+---------------+---------+---------+------+------+-------------+ | 1 | SIMPLE | TIMESTAMP_NG | range | idxtk01 | idxtk01 | 5 | NULL | 2 | Using where | +----+-------------+--------------+-------+---------------+---------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from TIMESTAMP_NG where TRADE_TIME = '2010-02-01 09:31:02.0'; +----+-------------+--------------+------+---------------+---------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------------+------+---------------+---------+---------+-------+------+-------------+ | 1 | SIMPLE | TIMESTAMP_NG | ref | idxtk01 | idxtk01 | 5 | const | 1 | Using where | +----+-------------+--------------+------+---------------+---------+---------+-------+------+-------------+ 1 row in set (0.00 sec)