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:
None 
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
Triage: Triaged: D2 (Serious) / R3 (Medium) / E3 (Medium)

[1 Feb 2010 2:23] Jingqi Xu
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)
[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.