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

EXTRACT(DAY FROM negative_time) returns wrong result

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.3, 10.4
    • Fix Version/s: 10.4.0
    • Component/s: Temporal Types
    • Labels:
      None

      Description

      CREATE OR REPLACE TABLE t1 (a TIME(6));
      INSERT INTO t1 VALUES ('-24:10:10.10');
      SELECT
        EXTRACT(MINUTE FROM a),
        EXTRACT(SECOND FROM a),
        EXTRACT(MICROSECOND FROM a),
        EXTRACT(DAY FROM a),
        EXTRACT(DAY_HOUR FROM a),
        EXTRACT(DAY_MINUTE FROM a),
        EXTRACT(DAY_SECOND FROM a),
        EXTRACT(DAY_MICROSECOND FROM a)
      FROM t1
      \G
      

      *************************** 1. row ***************************
               EXTRACT(MINUTE FROM a): -10
               EXTRACT(SECOND FROM a): -10
          EXTRACT(MICROSECOND FROM a): -100000
                  EXTRACT(DAY FROM a): 1
             EXTRACT(DAY_HOUR FROM a): -100
           EXTRACT(DAY_MINUTE FROM a): -10010
           EXTRACT(DAY_SECOND FROM a): -1001010
      EXTRACT(DAY_MICROSECOND FROM a): -1001010100000
      

      The result for EXTRACT(DAY..) looks wrong. It should be -1.

      The SQL standard says:

      If <extract field> is a <primary datetime field>, then the result is the value of the datetime field identified by that <primary datetime field> and has the same sign as the <extract source>. NOTE 186 — If the value of the identified <primary datetime field> is zero or if <extract source> is not an <interval value expression>, then the sign is irrelevant.

      The negative time value '-24:10:10.10' plays role of INTERVAL DAY TO SECOND in this example. The extract source is an <interval value expression>. So the sign is relevant and should be derived from the value.

        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:
                  Resolved: