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

Rounding functions return wrong data types for DATE input

Details

    Description

      CREATE OR REPLACE TABLE t1 (a date);
      		 
      CREATE OR REPLACE TABLE t2 AS SELECT FLOOR(a), CEIL(a),ROUND(a),TRUNCATE(a,0) FROM t1;


      +---------------+--------------+------+-----+---------+-------+
      		 
      | Field         | Type         | Null | Key | Default | Extra |
      		 
      +---------------+--------------+------+-----+---------+-------+
      		 
      | FLOOR(a)      | bigint(12)   | YES  |     | NULL    |       |
      		 
      | CEIL(a)       | bigint(12)   | YES  |     | NULL    |       |
      		 
      | ROUND(a)      | double(17,0) | YES  |     | NULL    |       |
      		 
      | TRUNCATE(a,0) | double(17,0) | YES  |     | NULL    |       |
      		 
      +---------------+--------------+------+-----+---------+-------+

      A date value has 8 digits in numeric format: YYYYMMDD.

      • INT(9) should be enough for ROUND() with a negative argument (to be checked in a separate MDEV)
      • INT(8) should be enough for ROUND() with a non-negative argument
      • INT(8) should be enough for all other functions

      Attachments

        Issue Links

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23032 FLOOR()/CEIL() incorrectly calculate the precision of a DECIMAL(M,D) column.

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23388 Assertion `args[0]->decimals == 0' failed in Item_func_round::fix_arg_int

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            Description {code:sql}
            CREATE OR REPLACE TABLE t1 (a date);
            CREATE OR REPLACE TABLE t2 AS SELECT FLOOR(a), CEIL(a),ROUND(a),TRUNCATE(a,0) FROM t1;
            {code}
            {noformat}
            +---------------+--------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +---------------+--------------+------+-----+---------+-------+
            | FLOOR(a) | bigint(12) | YES | | NULL | |
            | CEIL(a) | bigint(12) | YES | | NULL | |
            | ROUND(a) | double(17,0) | YES | | NULL | |
            | TRUNCATE(a,0) | double(17,0) | YES | | NULL | |
            +---------------+--------------+------+-----+---------+-------+
            {noformat}

            A date value has 8 digits in numeric format: YYYYMMDD.

            - INT(9) should be enough for ROUND() with a negative argument
            - INT(8) should be enough for ROUND() with a non-negative argument
            - INT(8) should be enough for all other functions
            		{code:sql}
            CREATE OR REPLACE TABLE t1 (a date);
            CREATE OR REPLACE TABLE t2 AS SELECT FLOOR(a), CEIL(a),ROUND(a),TRUNCATE(a,0) FROM t1;
            {code}
            {noformat}
            +---------------+--------------+------+-----+---------+-------+
            | Field | Type | Null | Key | Default | Extra |
            +---------------+--------------+------+-----+---------+-------+
            | FLOOR(a) | bigint(12) | YES | | NULL | |
            | CEIL(a) | bigint(12) | YES | | NULL | |
            | ROUND(a) | double(17,0) | YES | | NULL | |
            | TRUNCATE(a,0) | double(17,0) | YES | | NULL | |
            +---------------+--------------+------+-----+---------+-------+
            {noformat}

            A date value has 8 digits in numeric format: YYYYMMDD.

            - INT(9) should be enough for ROUND() with a negative argument (to be checked in a separate MDEV)
            - INT(8) should be enough for ROUND() with a non-negative argument
            - INT(8) should be enough for all other functions
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]
            bar Alexander Barkov made changes -
            issue.field.resolutiondate 2020-07-31 14:08:20.0 2020-07-31 14:08:20.057
            bar Alexander Barkov made changes -
            Fix Version/s 10.4.14 [ 24305 ]
            Fix Version/s 10.5.5 [ 24423 ]
            Fix Version/s 10.4 [ 22408 ]
            Resolution Fixed [ 1 ]
            Status In Progress [ 3 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 111848 ] MariaDB v4 [ 158175 ]

            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.