[MDEV-12858] Out-of-range error for CREATE..SELECT unsigned_int_column+1 - Jira

XML

Word

Printable

SET sql_mode=STRICT_ALL_TABLES;

CREATE OR REPLACE  TABLE t1 (a INT UNSIGNED NOT NULL);

INSERT INTO t1 VALUES (0xFFFFFFFF);

CREATE OR REPLACE TABLE t2 AS SELECT a+1 FROM t1;

ERROR 1264 (22003): Out of range value for column 'a+1' at row 1

Adding LIMIT 0 shows that an INT(11) instead of a BITINT(11) column is created:

CREATE OR REPLACE TABLE t2 AS SELECT a+1 FROM t1 LIMIT 0;

DESCRIBE t2;

+-------+------------------+------+-----+---------+-------+

| Field | Type             | Null | Key | Default | Extra |

+-------+------------------+------+-----+---------+-------+

| a+1   | int(11) unsigned | NO   |     | 0       |       |

+-------+------------------+------+-----+---------+-------+

blocks

MDEV-4912 Data type plugin API version 1

relates to

MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions

MDEV-12857 Out-of-range errors on CREATE..SELECT 2222222222 DIV 1

MDEV-12859 Out-of-range error for CREATE..SELECT @a:=EXTRACT(MINUTE_MICROSECOND FROM..)

MDEV-12860 Out-of-range error on CREATE..SELECT with a view using MAX and EXTRACT(MINUTE_MICROSECOND..)

MDEV-14910 Unexpected "BIGINT UNSIGNED value is out of range" for minus

(1 relates to)

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