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

Precision in INTERVAL xxx DAY_MICROSECOND parsed wrong?

Details

    • 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

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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