Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.1.28, 10.1(EOL), 10.2(EOL)
-
10.2.14, 10.1.32
Description
Also reported in mysql.
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.