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

ROUND(bigint_22_or_longer) returns a wrong data type

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.5
    • Fix Version/s: 10.4.14, 10.5.5
    • Component/s: Data types
    • Labels:
      None

      Description

      After fixes made in MDEV-23337, ROUND() returns good results for integer input:

      CREATE OR REPLACE TABLE t1 (a bigint(21) unsigned);
      CREATE OR REPLACE TABLE t2 AS SELECT ROUND(a),TRUNCATE(a,0),FLOOR(a),CEILING(a) FROM t1;
      DESC t2;
      

      +---------------+---------------------+------+-----+---------+-------+
      | Field         | Type                | Null | Key | Default | Extra |
      +---------------+---------------------+------+-----+---------+-------+
      | ROUND(a,0)    | bigint(21) unsigned | YES  |     | NULL    |       |
      | TRUNCATE(a,0) | bigint(21) unsigned | YES  |     | NULL    |       |
      | FLOOR(a)      | bigint(21) unsigned | YES  |     | NULL    |       |
      | CEILING(a)    | bigint(21) unsigned | YES  |     | NULL    |       |
      +---------------+---------------------+------+-----+---------+-------+
      

      However, if the length get longer than 21, the result is still not nice:

      CREATE OR REPLACE TABLE t1 (a bigint(22) unsigned);
      CREATE OR REPLACE TABLE t2 AS SELECT ROUND(a),TRUNCATE(a,0),FLOOR(a),CEILING(a) FROM t1;
      DESC t2;
      

      +---------------+------------------------+------+-----+---------+-------+
      | Field         | Type                   | Null | Key | Default | Extra |
      +---------------+------------------------+------+-----+---------+-------+
      | ROUND(a,0)    | decimal(22,0) unsigned | YES  |     | NULL    |       |
      | TRUNCATE(a,0) | bigint(22) unsigned    | YES  |     | NULL    |       |
      | FLOOR(a)      | bigint(22) unsigned    | YES  |     | NULL    |       |
      | CEILING(a)    | bigint(22) unsigned    | YES  |     | NULL    |       |
      +---------------+------------------------+------+-----+---------+-------+
      

      Notice, decimal(22,0) instead of bigint(22), which is the data type of the argument.

      ROUND(bigint,0) cannot go outside of the BIGINT limit. No needs to create the decimal data type.

        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: