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

Out-of-range errors when CAST(1-2 AS UNSIGNED)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1, 10.2, 10.3
    • Fix Version/s: 10.3.1
    • Component/s: OTHER
    • Labels:

      Description

      This problem is repeatable in 10.2 with the default sql_mode, as well as in 10.1 with sql_mode=STRICT_ALL_TABLES.

      It's very similar to MDEV-12849.

      Cast -1 as UNSIGNED returns 18446744073709551615. This script proves that:

      SELECT CAST(1-2 AS UNSIGNED);
      

      +-----------------------+
      | CAST(1-2 AS UNSIGNED) |
      +-----------------------+
      |  18446744073709551615 |
      +-----------------------+
      

      Now I want to create a table from the result of the same SELECT query. It unexpectedly returns an error:

      CREATE OR REPLACE TABLE t1 AS SELECT CAST(1-2 AS UNSIGNED);
      

      ERROR 1264 (22003): Out of range value for column 'CAST(1-2 AS UNSIGNED)' at row 1
      

      Adding LIMIT 0 helps to figure out what's going on:

      CREATE OR REPLACE TABLE t1 AS SELECT CAST(1-2 AS UNSIGNED), CAST(-1 AS UNSIGNED) LIMIT 0;
      DESCRIBE t1;
      

      +-----------------------+-----------------+------+-----+---------+-------+
      | Field                 | Type            | Null | Key | Default | Extra |
      +-----------------------+-----------------+------+-----+---------+-------+
      | CAST(1-2 AS UNSIGNED) | int(3) unsigned | NO   |     | NULL    |       |
      | CAST(-1 AS UNSIGNED)  | int(2) unsigned | NO   |     | NULL    |       |
      +-----------------------+-----------------+------+-----+---------+-------+
      

      Notice, the column type is wrong. The expected column type should be BIGINT for both columns, to be able to store 18446744073709551615.

        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: