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

MICROSECOND() returns confusing results with an out-of-range TIME-alike argument

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
    • 10.4
    • Data types, Temporal Types
    • None

    Description

      Notice a similar bug MDEV-17351 (which is about hybrid functions).

      MICROSECOND(arg) can return 999999 in some contexts where TIME(arg) returns a result without fractional digits.
      Internally, the argument gets converted to the maximum possible TIME value of '838:59:59.999999'.

      Confusing examples when a number gets converted to '838:59:59.999999'

      SELECT
        TIME(8395959) AS c1,
        MICROSECOND(8395959) AS c2;
      

      +-----------+--------+
      | c1        | c2     |
      +-----------+--------+
      | 838:59:59 | 999999 |
      +-----------+--------+
      

      SELECT
        TIME(8395959.0) AS c1,
        MICROSECOND(8395959.0) AS c2;
      

      +-------------+--------+
      | c1          | c2     |
      +-------------+--------+
      | 838:59:59.9 | 999999 |
      +-------------+--------+
      

      SELECT
        TIME(CAST(8395959 AS SIGNED)) AS c1,
        MICROSECOND(CAST(8395959 AS SIGNED)) AS c2;
      

      +-----------+--------+
      | c1        | c2     |
      +-----------+--------+
      | 838:59:59 | 999999 |
      +-----------+--------+
      

      SELECT
        TIME(CAST(8395959 AS DECIMAL(30,1))) AS c1,
        MICROSECOND(CAST(8395959 AS DECIMAL(10,1))) AS c2;
      

      +-------------+--------+
      | c1          | c2     |
      +-------------+--------+
      | 838:59:59.9 | 999999 |
      +-------------+--------+
      

      SELECT
        TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS SIGNED)) AS c1,
        MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS SIGNED)) AS c2;
      

      +-----------+--------+
      | c1        | c2     |
      +-----------+--------+
      | 838:59:59 | 999999 |
      +-----------+--------+
      

      More confusing examples when a string gets converted to '838:59:59.999999'

      Intuitively, an explicit conversion from string to time using the TIME() function, and an implicit conversion of a function argument (from a non-TIME actual parameter to TIME formal parameter) should return the same result. But they do not, because of different ways of precision detection for string arguments (mentioned in the previous paragraph):

      SELECT
        TIME('839:59:59') AS c0,
        MICROSECOND(TIME('839:59:59')) AS explicit,
        MICROSECOND('839:59:59') AS implicit;
      

      +-----------+----------+----------+
      | c0        | explicit | implicit |
      +-----------+----------+----------+
      | 838:59:59 |        0 |   999999 |
      +-----------+----------+----------+
      

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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