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

CAST("18446744073709552001" AS INT) returns a wrong result

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
    • 10.4
    • Data types
    • None

    Description

      CAST("string-with-a-huge-number" AS INT) erroneously performs a binary complement:

      SELECT CAST("18446744073709552001" AS INT) AS c1;
      SHOW WARNINGS;
      

      +----+
      | c1 |
      +----+
      | -1 |
      +----+
      1 row in set, 1 warning (0.000 sec)
      

      +---------+------+-----------------------------------------------------------+
      | Level   | Code | Message                                                   |
      +---------+------+-----------------------------------------------------------+
      | Warning | 1292 | Truncated incorrect INTEGER value: '18446744073709552001' |
      +---------+------+-----------------------------------------------------------+
      

      The same problem is repeatable with CAST in dynamic columns:

      SELECT COLUMN_GET(COLUMN_CREATE(1, "18446744073709552001" AS CHAR), 1 AS INT) AS c1;
      SHOW WARNINGS;
      

      +---------+------+------------------------------------------------------------------------------------+
      | Level   | Code | Message                                                                            |
      +---------+------+------------------------------------------------------------------------------------+
      | Warning | 1918 | Encountered illegal value '18446744073709552001' when converting to INT            |
      | Note    | 1105 | Cast to signed converted positive out-of-range integer to it's negative complement |
      +---------+------+------------------------------------------------------------------------------------+
      

      Applying binary complement to string->int conversion is wrong. Binary complement should only be applied when converting between integer types of different signness.

      Note, implicit conversion works fine though:

      CREATE OR REPLACE TABLE t1 (a BIGINT);
      INSERT IGNORE INTO t1 VALUES ("18446744073709552001");
      SELECT * FROM t1;
      

      +---------------------+
      | a                   |
      +---------------------+
      | 9223372036854775807 |
      +---------------------+
      

      Notice, unlike explicit CAST, implicit conversion does not apply binary complement. Instead, it correctly cuts the number to the maximum possible signed 64-bit value, which is 9223372036854775807.

      Explicit CAST should be fixed accordingly.

      Attachments

        Activity

          People

            bar Alexander Barkov
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.