[MDEV-12852] Out-of-range errors when CAST(1-2 AS UNSIGNED) - Jira

XML

Word

Printable

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.

relates to

MDEV-12849 Out-of-range errors when casting hex-hybrid to SIGNED and UNSIGNED

MDEV-12853 Out-of-range errors when CAST('-1' AS UNSIGNED)

MDEV-12849 Out-of-range errors when casting hex-hybrid to SIGNED and UNSIGNED

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