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

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

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
    • Fix Version/s: 10.4
    • Component/s: Data types, Temporal Types
    • Labels:
      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

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

                Dates

                • Created:
                  Updated: