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

Details

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

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

relates to

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

Closed

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

Closed

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

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2017-05-19 15:34

Alexander Barkov made changes - 2017-05-19 15:34

Field	Original Value	New Value
Link		This issue relates to ~~MDEV-12849~~ [ ~~MDEV-12849~~ ]

Alexander Barkov made changes - 2017-05-19 17:52

Summary

Out-of-range errors when CREATE..SELECT CAST(0xFFFFFFFF AS SIGNED)

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

Alexander Barkov made changes - 2017-05-19 17:52

Summary

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

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

Alexander Barkov made changes - 2017-05-19 17:53

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~~.

This query erroneously returns an error:
{code:sql}
CREATE OR REPLACE TABLE t1 AS SELECT CAST(0xFFFFFFFF AS SIGNED);
{code}
{noformat}
ERROR 1264 (22003): Out of range value for column 'CAST(0xFFFFFFFF AS SIGNED)' at row 1
{noformat}

This query with {{LIMIT 0}} shows what's going on:
{code:sql}
CREATE OR REPLACE TABLE t1 AS SELECT CAST(0xFFFFFFFF AS SIGNED) LIMIT 0;
SHOW CREATE TABLE t1;
{code}
{noformat}
+-------+-----------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`CAST(0xFFFFFFFF AS SIGNED)` int(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------+
{noformat}

Notice, a column of a wrong type is created. It should create a {{BIGINT}} column, to be able to store the value {{4294967295}}.

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:
{code:sql}
SELECT CAST(1-2 AS UNSIGNED);
{code}
{noformat}
+-----------------------+
| CAST(1-2 AS UNSIGNED) |
+-----------------------+
| 18446744073709551615 |
+-----------------------+
{noformat}

Now I want to create a table from the result of the same {{SELECT}} query. It unexpectedly returns an error:
{code:sql}
CREATE OR REPLACE TABLE t1 AS SELECT CAST(1-2 AS UNSIGNED);
{code}
{noformat}
ERROR 1264 (22003): Out of range value for column 'CAST(1-2 AS UNSIGNED)' at row 1
{noformat}

Adding {{LIMIT 0}} helps to figure out what's going on:
{code:sql}
CREATE OR REPLACE TABLE t1 AS SELECT CAST(1-2 AS UNSIGNED) LIMIT 0;
DESCRIBE t1;
{code}
{noformat}
+-----------------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+-----------------+------+-----+---------+-------+
| CAST(1-2 AS UNSIGNED) | int(3) unsigned | NO | | NULL | |
+-----------------------+-----------------+------+-----+---------+-------+
{noformat}
Notice, the column type is wrong. The expected column type should be {{BIGINT}} to be able to store {{18446744073709551615}}.

Alexander Barkov made changes - 2017-05-19 17:56

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:
{code:sql}
SELECT CAST(1-2 AS UNSIGNED);
{code}
{noformat}
+-----------------------+
| CAST(1-2 AS UNSIGNED) |
+-----------------------+
| 18446744073709551615 |
+-----------------------+
{noformat}

Now I want to create a table from the result of the same {{SELECT}} query. It unexpectedly returns an error:
{code:sql}
CREATE OR REPLACE TABLE t1 AS SELECT CAST(1-2 AS UNSIGNED);
{code}
{noformat}
ERROR 1264 (22003): Out of range value for column 'CAST(1-2 AS UNSIGNED)' at row 1
{noformat}

Adding {{LIMIT 0}} helps to figure out what's going on:
{code:sql}
CREATE OR REPLACE TABLE t1 AS SELECT CAST(1-2 AS UNSIGNED), CAST(-1 AS UNSIGNED) LIMIT 0;
DESCRIBE t1;
{code}
{noformat}
+-----------------------+-----------------+------+-----+---------+-------+
| 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 | |
+-----------------------+-----------------+------+-----+---------+-------+
{noformat}
Notice, the column type is wrong. The expected column type should be {{BIGINT}} to be able to store {{18446744073709551615}}.

Alexander Barkov made changes - 2017-05-19 17:57

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:
{code:sql}
SELECT CAST(1-2 AS UNSIGNED);
{code}
{noformat}
+-----------------------+
| CAST(1-2 AS UNSIGNED) |
+-----------------------+
| 18446744073709551615 |
+-----------------------+
{noformat}

Now I want to create a table from the result of the same {{SELECT}} query. It unexpectedly returns an error:
{code:sql}
CREATE OR REPLACE TABLE t1 AS SELECT CAST(1-2 AS UNSIGNED);
{code}
{noformat}
ERROR 1264 (22003): Out of range value for column 'CAST(1-2 AS UNSIGNED)' at row 1
{noformat}

Adding {{LIMIT 0}} helps to figure out what's going on:
{code:sql}
CREATE OR REPLACE TABLE t1 AS SELECT CAST(1-2 AS UNSIGNED), CAST(-1 AS UNSIGNED) LIMIT 0;
DESCRIBE t1;
{code}
{noformat}
+-----------------------+-----------------+------+-----+---------+-------+
| 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 | |
+-----------------------+-----------------+------+-----+---------+-------+
{noformat}
Notice, the column type is wrong. The expected column type should be {{BIGINT}} to be able to store {{18446744073709551615}}.

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:
{code:sql}
SELECT CAST(1-2 AS UNSIGNED);
{code}
{noformat}
+-----------------------+
| CAST(1-2 AS UNSIGNED) |
+-----------------------+
| 18446744073709551615 |
+-----------------------+
{noformat}

Now I want to create a table from the result of the same {{SELECT}} query. It unexpectedly returns an error:
{code:sql}
CREATE OR REPLACE TABLE t1 AS SELECT CAST(1-2 AS UNSIGNED);
{code}
{noformat}
ERROR 1264 (22003): Out of range value for column 'CAST(1-2 AS UNSIGNED)' at row 1
{noformat}

Adding {{LIMIT 0}} helps to figure out what's going on:
{code:sql}
CREATE OR REPLACE TABLE t1 AS SELECT CAST(1-2 AS UNSIGNED), CAST(-1 AS UNSIGNED) LIMIT 0;
DESCRIBE t1;
{code}
{noformat}
+-----------------------+-----------------+------+-----+---------+-------+
| 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 | |
+-----------------------+-----------------+------+-----+---------+-------+
{noformat}
Notice, the column type is wrong. The expected column type should be {{BIGINT}} for both columns, to be able to store {{18446744073709551615}}.

Alexander Barkov made changes - 2017-05-19 18:16

Link

This issue relates to ~~MDEV-12849~~ [ ~~MDEV-12849~~ ]

Alexander Barkov made changes - 2017-05-19 18:16

Link

This issue relates to ~~MDEV-12853~~ [ ~~MDEV-12853~~ ]

Alexander Barkov made changes - 2017-05-19 18:17

Comment

[ The same problem is repeatable with {{CAST('-1' AS UNSIGNED)}}:

{code:sql}
CREATE TABLE t1 AS SELECT CAST('-1' AS UNSIGNED);
{code}
{noformat}
ERROR 1264 (22003): Out of range value for column 'CAST('-1' AS UNSIGNED)' at row 1
{noformat}

{code:sql}
CREATE TABLE t1 AS SELECT CAST('-1' AS UNSIGNED) LIMIT 0;
SHOW CREATE TABLE t1;
{code}
{noformat}
+-------+----------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`CAST('-1' AS UNSIGNED)` int(2) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------+
{noformat}
Notice, {{int(2)}} is wrong. The expected type is {{BIGINT(20)}}.
]

Alexander Barkov added a comment - 2017-05-23 08:50

Pushed into bb-10.2-ext

Alexander Barkov added a comment - 2017-05-23 08:50 Pushed into bb-10.2-ext

Alexander Barkov made changes - 2017-05-23 08:50

issue.field.resolutiondate

2017-05-23 08:50:27.0

2017-05-23 08:50:27.787

Alexander Barkov made changes - 2017-05-23 08:50

Fix Version/s		10.3.1 [ 22532 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Alexander Barkov made changes - 2017-05-23 08:51

Labels

datatype

Sergei Golubchik made changes - 2021-12-06 21:44

Workflow

MariaDB v3 [ 80861 ]

MariaDB v4 [ 152183 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2017-05-19 15:34

Updated:: 2017-05-23 19:11

Resolved:: 2017-05-23 08:50

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration