| Bug #88573 | Nanosecond precision in INTERVAL xxx DAY_MICROSECOND parsed wrong? | ||
|---|---|---|---|
| Submitted: | 21 Nov 2017 3:17 | Modified: | 30 Nov 2017 18:23 |
| Reporter: | Daniel Adamski | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
| Version: | 5.7 | OS: | Debian |
| Assigned to: | CPU Architecture: | Any | |
[21 Nov 2017 3:27]
Daniel Adamski
Detected using JOOQ, which generates SQL with nanosecond precision for subsecond part. Reported here: https://github.com/jOOQ/jOOQ/issues/6820
[30 Nov 2017 14:30]
MySQL Verification Team
Hi!
I have managed to repeat your results:
+----------------------------------------------------------------------------------+
| date_add('1000-01-01 00:00:00', interval '0 00:00:01.500000000' day_microsecond) |
+----------------------------------------------------------------------------------+
| 1000-01-01 00:08:21 |
+----------------------------------------------------------------------------------+
Verified as reported.
[30 Nov 2017 18:23]
Daniel Adamski
FYI I also reported it in MariaDB: https://jira.mariadb.org/browse/MDEV-14452
Description: When up to 6 digits are used in subsecond part of INTERVAL xxx DAY_MICROSECOND it behaves as normal fraction, and 0.5 == 0.500000: mysql> select date_add('1000-01-01 00:00:00', interval '0 00:00:01.5' day_microsecond); +--------------------------------------------------------------------------+ | date_add('1000-01-01 00:00:00', interval '0 00:00:01.5' day_microsecond) | +--------------------------------------------------------------------------+ | 1000-01-01 00:00:01.500000 | +--------------------------------------------------------------------------+ mysql> select date_add('1000-01-01 00:00:00', interval '0 00:00:01.500000' day_microsecond); +-------------------------------------------------------------------------------+ | date_add('1000-01-01 00:00:00', interval '0 00:00:01.500000' day_microsecond) | +-------------------------------------------------------------------------------+ | 1000-01-01 00:00:01.500000 | +-------------------------------------------------------------------------------+ Using more digits there, e.g., nanosecond precision leads to surprising result: mysql> select date_add('1000-01-01 00:00:00', interval '0 00:00:01.500000000' day_microsecond); +----------------------------------------------------------------------------------+ | date_add('1000-01-01 00:00:00', interval '0 00:00:01.500000000' day_microsecond) | +----------------------------------------------------------------------------------+ | 1000-01-01 00:08:21 | +----------------------------------------------------------------------------------+ It is parsed as 1 second and 500'000'000 microseconds and gives 501 seconds interval. How to repeat: select date_add('1000-01-01 00:00:00', interval '0 00:00:01.500000000' day_microsecond);