Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-14452

Precision in INTERVAL xxx DAY_MICROSECOND parsed wrong?

    XMLWordPrintable

    Details

    • 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

            People

            • Assignee:
              bar Alexander Barkov
              Reporter:
              danadam Daniel Adamski
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: