Bug #92540 Comparison between DATE and DECIMAL does not round nanoseconds

• Submitted: 24 Sep 2018 8:01
• Modified: 24 Sep 2018 8:57
• Reporter: Alexander Barkov
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 5.7, 8.0.12, 5.7.23, 5.6.41
• OS: Any
• CPU Architecture: Any

[24 Sep 2018 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 2018 8:57] MySQL Verification Team

Hello Alexander,

Thank you for the report!

regards,
Umesh