Bug #92473 DATE_ADD rounds nanoseconds inconsistently
Submitted: 18 Sep 2018 9:33 Modified: 18 Sep 2018 10:15
Reporter: Alexander Barkov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.7, 5.7.23, 8.0.12 OS:Any
Assigned to: CPU Architecture:Any

[18 Sep 2018 9:33] Alexander Barkov
Description:
DATE_ADD rounds nanoseconds, but only for the first argument.

SELECT
  DATE_ADD('2017-12-31 23:59:59.9999999', INTERVAL 0 SECOND) AS c70,
  DATE_ADD('2017-12-31 23:59:59', INTERVAL 0.9999999 SECOND) AS c07;
+---------------------+----------------------------+
| c70                 | c07                        |
+---------------------+----------------------------+
| 2018-01-01 00:00:00 | 2017-12-31 23:59:59.999999 |
+---------------------+----------------------------+

Looks inconsistent. 

Note, the dyadic function TIMESTAMP rounds nanoseconds for both arguments:

SELECT
  TIMESTAMP('2017-12-31 23:59:59.9999999', '00:00:00') AS c70,
  TIMESTAMP('2017-12-31 23:59:59', '00:00:00.9999999') AS c07;
+----------------------------+----------------------------+
| c70                        | c07                        |
+----------------------------+----------------------------+
| 2018-01-01 00:00:00.000000 | 2018-01-01 00:00:00.000000 |
+----------------------------+----------------------------+

How to repeat:
SELECT
  DATE_ADD('2017-12-31 23:59:59.9999999', INTERVAL 0 SECOND) AS c70,
  DATE_ADD('2017-12-31 23:59:59', INTERVAL 0.9999999 SECOND) AS c07;

Suggested fix:
Please fix DATE_ADD() to round nanoseconds for both arguments.
[18 Sep 2018 10:15] Umesh Shastry
Hello Alexander,

Thank you for the report and test case.

regards,
Umesh