[MDEV-14452] Precision in INTERVAL xxx DAY_MICROSECOND parsed wrong? - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1.28, 10.1(EOL), 10.2(EOL)
Fix Version/s: 10.1.32, 10.2.14, 10.3.6
Component/s: Temporal Types
Labels:
- upstream

Sprint:
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.

Attachments

Activity

Ascending order - Click to sort in descending order

Daniel Adamski added a comment - 2017-11-21 03:27

Detected using JOOQ, which generates SQL with nanosecond precision for subsecond part. Reported here: https://github.com/jOOQ/jOOQ/issues/6820

Daniel Adamski added a comment - 2017-11-21 03:27 Detected using JOOQ, which generates SQL with nanosecond precision for subsecond part. Reported here: https://github.com/jOOQ/jOOQ/issues/6820

Elena Stepanova added a comment - 2017-11-30 00:41

I agree there is inconsistency here, even though maybe it's not an intuitive one.

Let's take for example MINUTE_SECOND vs MINUTE_MICROSECOND (it's the same for DAY_MICROSECOND, but shorter).

The format of the interval values described both for MariaDB and MySQL as

MINUTE_MICROSECOND	'MINUTES:SECONDS.MICROSECONDS'

MINUTE_SECOND	'MINUTES:SECONDS'

The punctuation character between integers can be anything.

So, it's not a floating point between seconds and microseconds, it's just a separator between an integer number of seconds and integer number of microseconds. Same for a separator between minutes and seconds.

And it does indeed work like that for minutes and seconds:

1.5 is not one minute and a half, it's 1 minute and 5 seconds
MariaDB [test]> select date_add('1000-01-01 00:00:00', interval '01.5' minute_second);
+----------------------------------------------------------------+
\| date_add('1000-01-01 00:00:00', interval '01.5' minute_second) \|
+----------------------------------------------------------------+
\| 1000-01-01 00:01:05 \|
+----------------------------------------------------------------+
1 row in set (0.00 sec)

1.50 is not the same as 1.5, it's 1 minute and 50 seconds
MariaDB [test]> select date_add('1000-01-01 00:00:00', interval '01.50' minute_second);
+-----------------------------------------------------------------+
\| date_add('1000-01-01 00:00:00', interval '01.50' minute_second) \|
+-----------------------------------------------------------------+
\| 1000-01-01 00:01:50 \|
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

1.5000000 is not the same as 1.5, it's 1 minute and 5000000 seconds
MariaDB [test]> select date_add('1000-01-01 00:00:00', interval '01.5000000' minute_second);
+----------------------------------------------------------------------+
\| date_add('1000-01-01 00:00:00', interval '01.5000000' minute_second) \|
+----------------------------------------------------------------------+
\| 1000-02-27 20:54:20 \|
+----------------------------------------------------------------------+
1 row in set (0.00 sec)

Same exact thing happens with microseconds when they grow above 6 digits

01.01.5000000 is 1 minute, 1 second and 5000000 microseconds == 1 minute and 6 seconds
MariaDB [test]> select date_add('1000-01-01 00:00:00', interval '01.01.5000000' minute_microsecond);
+------------------------------------------------------------------------------+
\| date_add('1000-01-01 00:00:00', interval '01.01.5000000' minute_microsecond) \|
+------------------------------------------------------------------------------+
\| 1000-01-01 00:01:06.000000 \|
+------------------------------------------------------------------------------+
1 row in set (0.00 sec)

PROBLEM

Thus, it's reasonable to expect – from the definition and by analogy – that 01.01.5 MINUTE_MICROSECOND would be 1 minute, 1 second and 5 microseconds; but it's not so:

MariaDB [test]> select date_add('1000-01-01 00:00:00', interval '01.01.5' minute_microsecond);

+------------------------------------------------------------------------+

| date_add('1000-01-01 00:00:00', interval '01.01.5' minute_microsecond) |

+------------------------------------------------------------------------+

| 1000-01-01 00:01:01.500000                                             |

+------------------------------------------------------------------------+

1 row in set (0.00 sec)

I don't know whether it needs to be fixed, or the specification needs to be refined, leaving it to serg to decide.

Elena Stepanova added a comment - 2017-11-30 00:41 I agree there is inconsistency here, even though maybe it's not an intuitive one. Let's take for example MINUTE_SECOND vs MINUTE_MICROSECOND (it's the same for DAY_MICROSECOND , but shorter). The format of the interval values described both for MariaDB and MySQL as MINUTE_MICROSECOND 'MINUTES:SECONDS.MICROSECONDS' MINUTE_SECOND 'MINUTES:SECONDS' The punctuation character between integers can be anything. So, it's not a floating point between seconds and microseconds, it's just a separator between an integer number of seconds and integer number of microseconds. Same for a separator between minutes and seconds. And it does indeed work like that for minutes and seconds: 1.5 is not one minute and a half, it's 1 minute and 5 seconds MariaDB [test]> select date_add( '1000-01-01 00:00:00' , interval '01.5' minute_second); + ----------------------------------------------------------------+ | date_add( '1000-01-01 00:00:00' , interval '01.5' minute_second) | + ----------------------------------------------------------------+ | 1000-01-01 00:01:05 | + ----------------------------------------------------------------+ 1 row in set (0.00 sec) 1.50 is not the same as 1.5, it's 1 minute and 50 seconds MariaDB [test]> select date_add( '1000-01-01 00:00:00' , interval '01.50' minute_second); + -----------------------------------------------------------------+ | date_add( '1000-01-01 00:00:00' , interval '01.50' minute_second) | + -----------------------------------------------------------------+ | 1000-01-01 00:01:50 | + -----------------------------------------------------------------+ 1 row in set (0.00 sec) 1.5000000 is not the same as 1.5, it's 1 minute and 5000000 seconds MariaDB [test]> select date_add( '1000-01-01 00:00:00' , interval '01.5000000' minute_second); + ----------------------------------------------------------------------+ | date_add( '1000-01-01 00:00:00' , interval '01.5000000' minute_second) | + ----------------------------------------------------------------------+ | 1000-02-27 20:54:20 | + ----------------------------------------------------------------------+ 1 row in set (0.00 sec) Same exact thing happens with microseconds when they grow above 6 digits 01.01.5000000 is 1 minute, 1 second and 5000000 microseconds == 1 minute and 6 seconds MariaDB [test]> select date_add( '1000-01-01 00:00:00' , interval '01.01.5000000' minute_microsecond); + ------------------------------------------------------------------------------+ | date_add( '1000-01-01 00:00:00' , interval '01.01.5000000' minute_microsecond) | + ------------------------------------------------------------------------------+ | 1000-01-01 00:01:06.000000 | + ------------------------------------------------------------------------------+ 1 row in set (0.00 sec) PROBLEM Thus, it's reasonable to expect – from the definition and by analogy – that 01.01.5 MINUTE_MICROSECOND would be 1 minute, 1 second and 5 microseconds; but it's not so: MariaDB [test]> select date_add( '1000-01-01 00:00:00' , interval '01.01.5' minute_microsecond); + ------------------------------------------------------------------------+ | date_add( '1000-01-01 00:00:00' , interval '01.01.5' minute_microsecond) | + ------------------------------------------------------------------------+ | 1000-01-01 00:01:01.500000 | + ------------------------------------------------------------------------+ 1 row in set (0.00 sec) I don't know whether it needs to be fixed, or the specification needs to be refined, leaving it to serg to decide.

Daniel Adamski added a comment - 2017-11-30 01:35

Thus, it's reasonable to expect – from the definition and by analogy – that 01.01.5 MINUTE_MICROSECOND would be 1 minute, 1 second and 5 microseconds

Apparently it was like that in some cases (specifically, when day was missing) and it was changed to always "expand" microseconds part to 6 digits. See (old) mysql bug#36466.

Daniel Adamski added a comment - 2017-11-30 01:35 Thus, it's reasonable to expect – from the definition and by analogy – that 01.01.5 MINUTE_MICROSECOND would be 1 minute, 1 second and 5 microseconds Apparently it was like that in some cases (specifically, when day was missing) and it was changed to always "expand" microseconds part to 6 digits. See (old) mysql bug#36466 .

Lukas Eder added a comment - 2017-11-30 09:49

Observe the behaviour of the following:

select

  date_add('1000-01-01 00:00:00', interval '0.5' minute_microsecond),

  date_add('1000-01-01 00:00:00', interval '0.50' minute_microsecond),

  date_add('1000-01-01 00:00:00', interval '0.500' minute_microsecond),

  date_add('1000-01-01 00:00:00', interval '0.5000' minute_microsecond),

  date_add('1000-01-01 00:00:00', interval '0.50000' minute_microsecond),

  date_add('1000-01-01 00:00:00', interval '0.500000' minute_microsecond),

  date_add('1000-01-01 00:00:00', interval '0.5000000' minute_microsecond)

Quite unexpected...

Lukas Eder added a comment - 2017-11-30 09:49 Observe the behaviour of the following: select date_add('1000-01-01 00:00:00', interval '0.5' minute_microsecond), date_add('1000-01-01 00:00:00', interval '0.50' minute_microsecond), date_add('1000-01-01 00:00:00', interval '0.500' minute_microsecond), date_add('1000-01-01 00:00:00', interval '0.5000' minute_microsecond), date_add('1000-01-01 00:00:00', interval '0.50000' minute_microsecond), date_add('1000-01-01 00:00:00', interval '0.500000' minute_microsecond), date_add('1000-01-01 00:00:00', interval '0.5000000' minute_microsecond) ; Quite unexpected...

Alexander Barkov added a comment - 2018-02-07 12:09 - edited

https://mariadb.com/kb/en/library/date-and-time-units/ says:

The time units containing an underscore are composite; that is, they consist of multiple base time units. For base time units, time_quantity is an integer number. For composite units, the quantity must be expressed as a string with multiple integer numbers separated by any punctuation character.

Therefore, interval '+0 00:00:01.300000000' day_microsecond reported in https://github.com/jOOQ/jOOQ/issues/6820 is treated as:

1 second plus 300000000 microseconds, which gives 301 seconds total (i.e. 5 minutes and one second)

This query gives a correct result:

select date_add('0001-01-01 00:00:00', interval '+0 00:00:01.300000000' day_microsecond);

+-----------------------------------------------------------------------------------+

| date_add('0001-01-01 00:00:00', interval '+0 00:00:01.300000000' day_microsecond) |

+-----------------------------------------------------------------------------------+

| 0001-01-01 00:05:01.000000                                                        |

+-----------------------------------------------------------------------------------+

Note, this query:

MariaDB [test]> SELECT DATE_ADD('0001-01-01 00:00:00', INTERVAL '1 00:00:01.30000000' DAY_SECOND);

+----------------------------------------------------------------------------+

| DATE_ADD('0001-01-01 00:00:00', INTERVAL '1 00:00:01.30000000' DAY_SECOND) |

+----------------------------------------------------------------------------+

| NULL                                                                       |

+----------------------------------------------------------------------------+

returns NULL. It could parse 3000000 as fractional second digits (notice DAY_SECOND instead of DAY_MICROSECOND). But this is a separate problem.

Alexander Barkov added a comment - 2018-02-07 12:09 - edited https://mariadb.com/kb/en/library/date-and-time-units/ says: The time units containing an underscore are composite; that is, they consist of multiple base time units. For base time units, time_quantity is an integer number. For composite units, the quantity must be expressed as a string with multiple integer numbers separated by any punctuation character. Therefore, interval '+0 00:00:01.300000000' day_microsecond reported in https://github.com/jOOQ/jOOQ/issues/6820 is treated as: 1 second plus 300000000 microseconds, which gives 301 seconds total (i.e. 5 minutes and one second) This query gives a correct result: select date_add('0001-01-01 00:00:00', interval '+0 00:00:01.300000000' day_microsecond); +-----------------------------------------------------------------------------------+ | date_add('0001-01-01 00:00:00', interval '+0 00:00:01.300000000' day_microsecond) | +-----------------------------------------------------------------------------------+ | 0001-01-01 00:05:01.000000 | +-----------------------------------------------------------------------------------+ Note, this query: MariaDB [test]> SELECT DATE_ADD('0001-01-01 00:00:00', INTERVAL '1 00:00:01.30000000' DAY_SECOND); +----------------------------------------------------------------------------+ | DATE_ADD('0001-01-01 00:00:00', INTERVAL '1 00:00:01.30000000' DAY_SECOND) | +----------------------------------------------------------------------------+ | NULL | +----------------------------------------------------------------------------+ returns NULL. It could parse 3000000 as fractional second digits (notice DAY_SECOND instead of DAY_MICROSECOND). But this is a separate problem.

Alexander Barkov added a comment - 2018-02-08 05:42

The above query mentioned by Lukas returns this result:

      date_add('1000-01-01 00:00:00', interval '0.5' minute_microsecond): 1000-01-01 00:00:00.500000

     date_add('1000-01-01 00:00:00', interval '0.50' minute_microsecond): 1000-01-01 00:00:00.500000

    date_add('1000-01-01 00:00:00', interval '0.500' minute_microsecond): 1000-01-01 00:00:00.500000

   date_add('1000-01-01 00:00:00', interval '0.5000' minute_microsecond): 1000-01-01 00:00:00.500000

  date_add('1000-01-01 00:00:00', interval '0.50000' minute_microsecond): 1000-01-01 00:00:00.500000

 date_add('1000-01-01 00:00:00', interval '0.500000' minute_microsecond): 1000-01-01 00:00:00.500000

date_add('1000-01-01 00:00:00', interval '0.5000000' minute_microsecond): 1000-01-01 00:00:05.000000

interval '0.5000000' minute_microsecond should be fixed to treat it as '0.5' seconds, like all other examples in this query do
The manual should be updated

Alexander Barkov added a comment - 2018-02-08 05:42 The above query mentioned by Lukas returns this result: date_add('1000-01-01 00:00:00', interval '0.5' minute_microsecond): 1000-01-01 00:00:00.500000 date_add('1000-01-01 00:00:00', interval '0.50' minute_microsecond): 1000-01-01 00:00:00.500000 date_add('1000-01-01 00:00:00', interval '0.500' minute_microsecond): 1000-01-01 00:00:00.500000 date_add('1000-01-01 00:00:00', interval '0.5000' minute_microsecond): 1000-01-01 00:00:00.500000 date_add('1000-01-01 00:00:00', interval '0.50000' minute_microsecond): 1000-01-01 00:00:00.500000 date_add('1000-01-01 00:00:00', interval '0.500000' minute_microsecond): 1000-01-01 00:00:00.500000 date_add('1000-01-01 00:00:00', interval '0.5000000' minute_microsecond): 1000-01-01 00:00:05.000000 interval '0.5000000' minute_microsecond should be fixed to treat it as '0.5' seconds, like all other examples in this query do The manual should be updated

People

Assignee:: Alexander Barkov

Reporter:: Daniel Adamski

Votes:: 0 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2017-11-21 03:23

Updated:: 2018-03-15 06:26

Resolved:: 2018-03-14 10:47

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server