[MDEV-12852] Out-of-range errors when CAST(1-2 AS UNSIGNED) Created: 2017-05-19  Updated: 2017-05-23  Resolved: 2017-05-23

Status: Closed
Project: MariaDB Server
Component/s: OTHER
Affects Version/s: 10.1, 10.2, 10.3
Fix Version/s: 10.3.1

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: datatype

Issue Links:
Relates
relates to MDEV-12849 Out-of-range errors when casting hex-... Closed
relates to MDEV-12853 Out-of-range errors when CAST('-1' AS... Closed
relates to MDEV-12849 Out-of-range errors when casting hex-... Closed

 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.

 Comments   
Comment by Alexander Barkov [ 2017-05-23 ]

Pushed into bb-10.2-ext

Generated at Thu Feb 08 08:00:59 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.