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

EXTRACT(DAY FROM negative_time) returns wrong result

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3(EOL), 10.4(EOL)
    • 10.4.0
    • Temporal Types
    • 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

          blocks

          Task - A task that needs to be done. MDEV-16991 Rounding vs truncation for TIME, DATETIME, TIMESTAMP

          • Major - Major loss of function.
          • Closed

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.