MySQL Bugs: #50710: timestamp add .0 behind second

Bug #50710	timestamp add .0 behind second
Submitted:	29 Jan 2010 5:08	Modified:	1 Feb 2010 8:58
Reporter:	davy hou	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
 when i query data using prepare statement by timestamp type,i found that connector/j in 5.1 will add .0 behind second,but in 5.0 it dosent.usually,it is not a problem,but when i add index on the timestamp,it cause a problem,i can not find the result i expected.so could you please tell me why dose the connector/j in 5.1 do this and why dose it cause the problem?

How to repeat:
Create Table: CREATE TABLE `customer` (
  `CUSTOMER_ID` varchar(20) NOT NULL DEFAULT '',
  `CURRENCY_PAIR` varchar(7) DEFAULT NULL,
  `update_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_T
IMESTAMP,
  PRIMARY KEY (`CUSTOMER_ID`),
  KEY `IDX_testNoPK_Name` (`update_date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> select * from customer;
+-------------+---------------+---------------------+
| CUSTOMER_ID | CURRENCY_PAIR | update_date         |
+-------------+---------------+---------------------+
| bb          | nnn           | 2010-01-27 10:51:53 |
| bbb         | nnn           | 2010-01-27 10:54:03 |
| bbbb        | nnn           | 2010-01-27 10:56:10 |
| bbbbb       | nnn           | 2010-01-27 11:01:50 |
| bbbbbb      | nnn           | 2010-01-27 11:09:06 |
| bbbbbba     | nnn           | 2010-01-27 11:09:06 |
| bbbbbbb     | nnn           | 2010-01-27 11:18:45 |
| bbbbbbbbb   | nnn           | 2010-01-27 11:40:03 |
| bbbbbbda    | nnn           | 2010-01-27 11:09:06 |
| bbbbgbbbbb  | nnn           | 2010-01-27 12:52:06 |
| ggggg       | nnn           | 2010-01-27 10:49:45 |
+-------------+---------------+---------------------+

mysql> select * from customer where update_date>='2010-01-27 11:01:50.0' and upd
ate_date<'2010-01-27 11:02:00.0';
Empty set (0.00 sec)

Hi Davy and thanks for your report.

This is not a bug but expected behavior. Java resolution is higher and Java parsers excepts timestamp in such format. Please recheck Java specs.

hi,thanks for your reply
but i dont think it related to java spec.because when i add index on timestamp,i can not get the result.but when i deleted index on timestamp,i can get the result.it looks like mysql's problem. by the way,mysql's version is 5.1.42.
waiting for your reply,thanks.

Davy, MySQL server 5.1 & 5.0 manual says 'A microseconds part is allowable in temporal values in some contexts, such as in literal values' which is what the c/J driver is doing (see http://dev.mysql.com/doc/refman/5.1/en/datetime.html).

Also, can you please attach complete test case to the report so I can check? You can consult any bug report in bugs DB with attached test case by Mark, Todd or me to see how to do it (ie. http://bugs.mysql.com/file.php?id=13927).

More on fractions of seconds in Bug#8523.

Actually, this is a duplicate of Bug#47963, and so more info can be found in Bug#50774.

to workargound, is it OK to comment the following lines in PreparedStatement.java?
buf.append('.');
buf.append(formatNanos(x.getNanos()));