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

FLOOR()/CEIL() incorrectly calculate the precision of a DECIMAL(M,D) column.

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5
    • 10.4.16, 10.5.7
    • Data types
    • None

    Description

      MariaDB [test]> create table i1 (a decimal(38,10));
      		 
      Query OK, 0 rows affected (0.057 sec)
      		 
       
      		 
      MariaDB [test]> create table i2 as select floor(a), ceil(a) from i1;
      		 
      Query OK, 0 rows affected (0.019 sec)
      		 
      Records: 0  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [test]> show create table i2;
      		 
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Table | Create Table                                                                                                                               |
      		 
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | i2    | CREATE TABLE `i2` (
      		 
        `floor(a)` decimal(28,0) DEFAULT NULL,
      		 
        `ceil(a)` decimal(28,0) DEFAULT NULL
      		 
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      		 
      +-------+--------------------------------------------------------------------------------------------------------------------------------------------+
      		 
      1 row in set (0.001 sec)
      		 
       
      		 
      MariaDB [test]> insert into i1 values (9999999999999999999999999999.9999999999), (-9999999999999999999999999999.9999999999);
      		 
      Query OK, 2 rows affected (0.002 sec)
      		 
      Records: 2  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [test]> create table i3 as select floor(a), ceil(a) from i1;
      		 
      ERROR 1264 (22003): Out of range value for column 'ceil(a)' at row 1
      		 
      MariaDB [test]> create table i3 as select floor(a) from i1;
      		 
      ERROR 1264 (22003): Out of range value for column 'floor(a)' at row 2

      For DECIMAL(M,0), i2 should have M=29, instead of M=28 as in the above. This is because M can exceed by 1 for FLOOR/CEIL functions, for example, when we insert the 2 rows into i1, the result of the functions would contain 29 digits, instead of 28.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-20397 Support TIMESTAMP, DATETIME, TIME in ROUND() and TRUNCATE()

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23320 Hex hybrid constants 0xHHHH work badly in rounding functions

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23323 Rounding functions return a wrong data type for a BIT, ENUM, SET argument

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23337 Rounding functions create a wrong data type for integer input

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23350 ROUND(bigint_22_or_longer) returns a wrong data type

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. MCOL-641 Full DECIMAL support in ColumnStore

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23311 CEILING() and FLOOR() convert temporal input to numbers, unlike ROUND() and TRUNCATE()

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23351 Rounding functions return wrong data types for DATE input

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23366 ROUND(18446744073709551615,rand()*0) returns a wrong result

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23367 ROUND(18446744073709551615,-1) returns a wrong result

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            tntnatbry Gagan Goel (Inactive) added a comment -

            MDEV-23032.patch

            Attached is a quick patch I wrote to get this case working.

            tntnatbry Gagan Goel (Inactive) added a comment - MDEV-23032.patch Attached is a quick patch I wrote to get this case working.
            bar Alexander Barkov added a comment - - edited

            Update: this problem was fixed by MDEV-23320

            A related problem:

            CREATE OR REPLACE TABLE t1 AS SELECT floor(0xffffffffff);


            ERROR 1264 (22003): Out of range value for column 'floor(0xffffffffff)' at row 1

            bar Alexander Barkov added a comment - - edited Update: this problem was fixed by MDEV-23320 A related problem: CREATE OR REPLACE TABLE t1 AS SELECT floor(0xffffffffff); ERROR 1264 (22003): Out of range value for column 'floor(0xffffffffff)' at row 1
            bar Alexander Barkov added a comment - - edited

            A related problem (now reported in MDEV-23323):

            CREATE OR REPLACE TABLE t1 (a BIT(64));
            		 
            CREATE OR REPLACE TABLE t2 AS SELECT FLOOR(a) FROM t1;
            		 
            DESC t2;


            +----------+---------------+------+-----+---------+-------+
            		 
            | Field    | Type          | Null | Key | Default | Extra |
            		 
            +----------+---------------+------+-----+---------+-------+
            		 
            | FLOOR(a) | decimal(64,0) | YES  |     | NULL    |       |
            		 
            +----------+---------------+------+-----+---------+-------+

            Looks redundant. A DECIMAL(19,0) or even a BIGINT() column would be enough.

            bar Alexander Barkov added a comment - - edited A related problem (now reported in MDEV-23323 ): CREATE OR REPLACE TABLE t1 (a BIT (64)); CREATE OR REPLACE TABLE t2 AS SELECT FLOOR(a) FROM t1; DESC t2; +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | FLOOR(a) | decimal(64,0) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ Looks redundant. A DECIMAL(19,0) or even a BIGINT() column would be enough.
            bar Alexander Barkov added a comment - - edited

            Update: This problem was fixed by MDEV-23323

            A related problem:

            CREATE OR REPLACE TABLE t1 (a SET('1000000000000000000000000000000000000000000000000000000000000000000000000000000000000'));
            		 
            INSERT INTO t1 VALUES (1);
            		 
            CREATE OR REPLACE TABLE t2 AS SELECT FLOOR(a) FROM t1;
            		 
            SELECT * FROM t2;
            		 
            DESC t2;


            +----------+
            		 
            | FLOOR(a) |
            		 
            +----------+
            		 
            |        1 |
            		 
            +----------+


            +----------+---------------+------+-----+---------+-------+
            		 
            | Field    | Type          | Null | Key | Default | Extra |
            		 
            +----------+---------------+------+-----+---------+-------+
            		 
            | FLOOR(a) | decimal(65,0) | YES  |     | NULL    |       |
            		 
            +----------+---------------+------+-----+---------+-------+

            Looks redundant. In numeric context SET can return a value beween 0 and 0xFF or between and 0xFFFF, depending on the amount of values in SET. INT should be enough (or even TINYINT/SMALLINT).

            bar Alexander Barkov added a comment - - edited Update: This problem was fixed by MDEV-23323 A related problem: CREATE OR REPLACE TABLE t1 (a SET ( '1000000000000000000000000000000000000000000000000000000000000000000000000000000000000' )); INSERT INTO t1 VALUES (1); CREATE OR REPLACE TABLE t2 AS SELECT FLOOR(a) FROM t1; SELECT * FROM t2; DESC t2; +----------+ | FLOOR(a) | +----------+ | 1 | +----------+ +----------+---------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+---------------+------+-----+---------+-------+ | FLOOR(a) | decimal(65,0) | YES | | NULL | | +----------+---------------+------+-----+---------+-------+ Looks redundant. In numeric context SET can return a value beween 0 and 0xFF or between and 0xFFFF, depending on the amount of values in SET. INT should be enough (or even TINYINT/SMALLINT).
            bar Alexander Barkov added a comment - - edited

            Update 2020-06-29: The problem described below was fixed under terms of MDEV-23311. FLOOR(TIME) and CEILING(TIME) now return TIME rather than BIGINT.

            A related problem:

            CREATE OR REPLACE TABLE t1 (a TIME(6));
            		 
            INSERT INTO t1 VALUES ('10:20:30');
            		 
            CREATE OR REPLACE TABLE t2 AS SELECT FLOOR(a) FROM t1;
            		 
            SELECT * FROM t2;
            		 
            DESC t2;


            +----------+
            		 
            | FLOOR(a) |
            		 
            +----------+
            		 
            |   102030 |
            		 
            +----------+


            +----------+------------+------+-----+---------+-------+
            		 
            | Field    | Type       | Null | Key | Default | Extra |
            		 
            +----------+------------+------+-----+---------+-------+
            		 
            | FLOOR(a) | bigint(12) | YES  |     | NULL    |       |
            		 
            +----------+------------+------+-----+---------+-------+

            Looks redundant. FLOOR(time) should never return more than 9 digits (hhhmmss). So INT(9) should be enough.

            Or even better TIME(0), like in this script:

            CREATE OR REPLACE TABLE t1 (a TIME(6));
            		 
            CREATE OR REPLACE TABLE t2 AS SELECT ROUND(a) FROM t1;
            		 
            DESC t2;


            +----------+------+------+-----+---------+-------+
            		 
            | Field    | Type | Null | Key | Default | Extra |
            		 
            +----------+------+------+-----+---------+-------+
            		 
            | ROUND(a) | time | YES  |     | NULL    |       |
            		 
            +----------+------+------+-----+---------+-------+

            bar Alexander Barkov added a comment - - edited Update 2020-06-29: The problem described below was fixed under terms of MDEV-23311 . FLOOR(TIME) and CEILING(TIME) now return TIME rather than BIGINT. A related problem: CREATE OR REPLACE TABLE t1 (a TIME (6)); INSERT INTO t1 VALUES ( '10:20:30' ); CREATE OR REPLACE TABLE t2 AS SELECT FLOOR(a) FROM t1; SELECT * FROM t2; DESC t2; +----------+ | FLOOR(a) | +----------+ | 102030 | +----------+ +----------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------+------+-----+---------+-------+ | FLOOR(a) | bigint(12) | YES | | NULL | | +----------+------------+------+-----+---------+-------+ Looks redundant. FLOOR(time) should never return more than 9 digits (hhhmmss). So INT(9) should be enough. Or even better TIME(0), like in this script: CREATE OR REPLACE TABLE t1 (a TIME (6)); CREATE OR REPLACE TABLE t2 AS SELECT ROUND(a) FROM t1; DESC t2; +----------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------+------+-----+---------+-------+ | ROUND(a) | time | YES | | NULL | | +----------+------+------+-----+---------+-------+

            People

              bar Alexander Barkov
              tntnatbry Gagan Goel (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.