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.
Bug
Major
