Bug #92540 Comparison between DATE and DECIMAL does not round nanoseconds
Submitted: 24 Sep 8:01 Modified: 24 Sep 8:57
Reporter: Alexander Barkov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.7, 8.0.12, 5.7.23, 5.6.41 OS:Any
Assigned to: CPU Architecture:Any

[24 Sep 8:01] Alexander Barkov
Description:
I run this script:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DATETIME);
INSERT INTO t1 VALUES (20010101);
# DATE is compared to DECIMAL and VARCHAR as DATETIME
# In the below queries nanoseconds should not round to microseconds
SELECT * FROM t1 WHERE a='2001-01-01 00:00:00.000000';
SELECT * FROM t1 WHERE a= 20010101000000.000000;

Both SELECT queries return one row as expected.

This query:
SELECT * FROM t1 WHERE a='2001-01-01 00:00:00.0000009';
returns empty set. This also looks fine, presumably because the 7th fractional
digit rounded the literal to '2001-01-01 00:00:00.000001'.

This query:
SELECT * FROM t1 WHERE a= 20010101000000.0000009;
returns one row. This looks wrong. Shouldn't the value have been
rounded to '2001-01-01 00:00:00.000001', like in the previous example?

How to repeat:
Run the above scripts.

Suggested fix:
Please fix the last SELECT to return empty set.
[24 Sep 8:57] Umesh Shastry
Hello Alexander,

Thank you for the report!

regards,
Umesh