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

Hex hybrid constants 0xHHHH work badly in rounding functions

    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

      SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES';
      CREATE OR REPLACE TABLE t1 AS SELECT
        0xFFFFFFFFFFFFFFFF+0 AS c1,
        FLOOR(0xFFFFFFFFFFFFFFFF) AS c2,
        CEILING(0xFFFFFFFFFFFFFFFF) AS c3,
        ROUND(0xFFFFFFFFFFFFFFFF) AS c4,
        TRUNCATE(0xFFFFFFFFFFFFFFFF,0) AS c5;
      

      ERROR 1264 (22003): Out of range value for column 'c4' at row
      

      Looks bad. No error expected.

      SET sql_mode='';
      CREATE OR REPLACE TABLE t1 AS SELECT
        0xFFFFFFFFFFFFFFFF+0 AS c1,
        FLOOR(0xFFFFFFFFFFFFFFFF) AS c2,
        CEILING(0xFFFFFFFFFFFFFFFF) AS c3,
        ROUND(0xFFFFFFFFFFFFFFFF) AS c4,
        TRUNCATE(0xFFFFFFFFFFFFFFFF,0) AS c5;
      

      Query OK, 1 row affected, 2 warnings (0.022 sec)
      Records: 1  Duplicates: 0  Warnings: 2
      

      Ok. With a loose mode it worked, but with warnings. Warnings are not expected.

      SELECT * FROM t1\G
      

      *************************** 1. row ***************************
      c1: 18446744073709551615
      c2: 9223372036854775808
      c3: 9223372036854775808
      c4: 100000000000000000
      c5: 100000000000000000
      1 row in set (0.001 sec)
      

      The result for all columns is wrong. Expect 18446744073709551615 for all columns.

      DESCRIBE t1;
      

      +-------+-----------------------+------+-----+---------+-------+
      | Field | Type                  | Null | Key | Default | Extra |
      +-------+-----------------------+------+-----+---------+-------+
      | c1    | bigint(21) unsigned   | NO   |     | NULL    |       |
      | c2    | bigint(10) unsigned   | NO   |     | NULL    |       |
      | c3    | bigint(10) unsigned   | NO   |     | NULL    |       |
      | c4    | double(17,0) unsigned | NO   |     | NULL    |       |
      | c5    | double(17,0) unsigned | NO   |     | NULL    |       |
      +-------+-----------------------+------+-----+---------+-------+
      

      The data types for all columns except c1 are wrong. Expect BIGINT(21) UNSIGNED for all columns.

        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: