Bug #47089 DATETIME comparison in CASE statement with fractional seconds fails

• Submitted: 3 Sep 2009 9:44
• Modified: 5 Sep 2009 16:58
• Reporter: Vladimir Strugatsky
• Status: Can't repeat
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 5.0.45
• OS: Any
• CPU Architecture: Any
• Tags: case, Connector/J, date, datetime

[3 Sep 2009 9:44] Vladimir Strugatsky

Description:
This is important because Connector/J 5.1.8 sends dates to MySQL Server in a 'YYYY-MM-DD HH:MM:SS.0' format.

How to repeat:
drop table if exists datetime_test;
CREATE TABLE datetime_test(
test_date DATETIME);

insert into datetime_test values('2009-09-03 01:00:00');

// Returns 1 record
select * from datetime_test where test_date = '2009-09-03 01:00:00.0';

// Returns 0 records => problem!
select * from datetime_test 
where case when test_date is null then test_date 
else test_date 
end = '2009-09-03 01:00:00.0';

[3 Sep 2009 11:30] MySQL Verification Team

Thank you for the bug report. Could you please try the latest release?. Thanks in advance.

c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.85-Win X64 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.0 > use test
Database changed
mysql 5.0 > drop table if exists datetime_test;
Query OK, 0 rows affected (0.00 sec)

mysql 5.0 > CREATE TABLE datetime_test(
    -> test_date DATETIME);
Query OK, 0 rows affected (0.14 sec)

mysql 5.0 >
mysql 5.0 > insert into datetime_test values('2009-09-03 01:00:00');
Query OK, 1 row affected (0.00 sec)

mysql 5.0 >
mysql 5.0 > select * from datetime_test where test_date = '2009-09-03 01:00:00.0';
+---------------------+
| test_date           |
+---------------------+
| 2009-09-03 01:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql 5.0 >
mysql 5.0 > select * from datetime_test
    -> where case when test_date is null then test_date
    -> else test_date
    -> end = '2009-09-03 01:00:00.0';
+---------------------+
| test_date           |
+---------------------+
| 2009-09-03 01:00:00 |
+---------------------+
1 row in set (0.00 sec)

mysql 5.0 >

[4 Sep 2009 19:34] Vladimir Strugatsky

Yes, it works with the latest release. Do you know the reference to the bug that solved this problem?

[5 Sep 2009 16:58] Sveta Smirnova

Thank you for the feedback.

Closed as "Can't repeat" because last comment.