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

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

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
    • Fix Version/s: 10.4
    • Component/s: Data types
    • Labels:
      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

          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: