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

Hex hybrid constants 0xHHHH work badly in rounding functions

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.1, 10.2, 10.3, 10.4, 10.5
    • 10.4.14, 10.5.5
    • Data types
    • 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

              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.