Description:
Generated columns inconsistently permit integer overflow. Consider the attached test case.
How to repeat:
DROP TABLE IF EXISTS t1, t2, t3;
CREATE TABLE t1 (a BIGINT UNSIGNED NOT NULL, b BIGINT AS (a) NOT NULL);
INSERT INTO t1 (a) VALUES (9223372036854775808); # out of range for b, succeeds
SELECT * FROM t1; # b wraps
CREATE TABLE t2 (a INT UNSIGNED NOT NULL, b INT AS (a) NOT NULL);
INSERT INTO t2 (a) VALUES (4294967295); # out of range for b, succeeds
INSERT INTO t2 (a) VALUES (4294967296); # out of range for a, fails
INSERT INTO t2 (a) VALUES (-1); # out of range for a, fails
SELECT * FROM t2;
CREATE TABLE t3 (a INT NOT NULL, b INT UNSIGNED AS (a) NOT NULL);
INSERT INTO t3(a) VALUES (-1); # out of range for b, but fails! (inconsistent)
SELECT * FROM t3;
...
mysql [localhost:8022] {msandbox} (test) > DROP TABLE IF EXISTS t1, t2, t3;
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:8022] {msandbox} (test) >
mysql [localhost:8022] {msandbox} (test) > CREATE TABLE t1 (a BIGINT UNSIGNED NOT NULL, b BIGINT AS (a) NOT NULL);
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:8022] {msandbox} (test) > INSERT INTO t1 (a) VALUES (9223372036854775808); # out of range for b, succeeds
Query OK, 1 row affected (0.00 sec)
mysql [localhost:8022] {msandbox} (test) > SELECT * FROM t1; # b wraps
+---------------------+----------------------+
| a | b |
+---------------------+----------------------+
| 9223372036854775808 | -9223372036854775808 |
+---------------------+----------------------+
1 row in set (0.00 sec)
mysql [localhost:8022] {msandbox} (test) >
mysql [localhost:8022] {msandbox} (test) > CREATE TABLE t2 (a INT UNSIGNED NOT NULL, b INT AS (a) NOT NULL);
Query OK, 0 rows affected (0.00 sec)
mysql [localhost:8022] {msandbox} (test) > INSERT INTO t2 (a) VALUES (4294967295); # out of range for b, succeeds
Query OK, 1 row affected (0.01 sec)
mysql [localhost:8022] {msandbox} (test) > INSERT INTO t2 (a) VALUES (4294967296); # out of range for a, fails
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql [localhost:8022] {msandbox} (test) > INSERT INTO t2 (a) VALUES (-1); # out of range for a, fails
ERROR 1264 (22003): Out of range value for column 'a' at row 1
mysql [localhost:8022] {msandbox} (test) > SELECT * FROM t2;
+------------+----+
| a | b |
+------------+----+
| 4294967295 | -1 |
+------------+----+
1 row in set (0.00 sec)
mysql [localhost:8022] {msandbox} (test) >
mysql [localhost:8022] {msandbox} (test) > CREATE TABLE t3 (a INT NOT NULL, b INT UNSIGNED AS (a) NOT NULL);
Query OK, 0 rows affected (0.01 sec)
mysql [localhost:8022] {msandbox} (test) > INSERT INTO t3(a) VALUES (-1); # out of range for b
ERROR 1264 (22003): Out of range value for column 'b' at row 1
mysql [localhost:8022] {msandbox} (test) > SELECT * FROM t3;
Empty set (0.00 sec)
Suggested fix:
Ideally they would not permit any overflow (as shown with regular columns).
Description: Generated columns inconsistently permit integer overflow. Consider the attached test case. How to repeat: DROP TABLE IF EXISTS t1, t2, t3; CREATE TABLE t1 (a BIGINT UNSIGNED NOT NULL, b BIGINT AS (a) NOT NULL); INSERT INTO t1 (a) VALUES (9223372036854775808); # out of range for b, succeeds SELECT * FROM t1; # b wraps CREATE TABLE t2 (a INT UNSIGNED NOT NULL, b INT AS (a) NOT NULL); INSERT INTO t2 (a) VALUES (4294967295); # out of range for b, succeeds INSERT INTO t2 (a) VALUES (4294967296); # out of range for a, fails INSERT INTO t2 (a) VALUES (-1); # out of range for a, fails SELECT * FROM t2; CREATE TABLE t3 (a INT NOT NULL, b INT UNSIGNED AS (a) NOT NULL); INSERT INTO t3(a) VALUES (-1); # out of range for b, but fails! (inconsistent) SELECT * FROM t3; ... mysql [localhost:8022] {msandbox} (test) > DROP TABLE IF EXISTS t1, t2, t3; Query OK, 0 rows affected (0.01 sec) mysql [localhost:8022] {msandbox} (test) > mysql [localhost:8022] {msandbox} (test) > CREATE TABLE t1 (a BIGINT UNSIGNED NOT NULL, b BIGINT AS (a) NOT NULL); Query OK, 0 rows affected (0.01 sec) mysql [localhost:8022] {msandbox} (test) > INSERT INTO t1 (a) VALUES (9223372036854775808); # out of range for b, succeeds Query OK, 1 row affected (0.00 sec) mysql [localhost:8022] {msandbox} (test) > SELECT * FROM t1; # b wraps +---------------------+----------------------+ | a | b | +---------------------+----------------------+ | 9223372036854775808 | -9223372036854775808 | +---------------------+----------------------+ 1 row in set (0.00 sec) mysql [localhost:8022] {msandbox} (test) > mysql [localhost:8022] {msandbox} (test) > CREATE TABLE t2 (a INT UNSIGNED NOT NULL, b INT AS (a) NOT NULL); Query OK, 0 rows affected (0.00 sec) mysql [localhost:8022] {msandbox} (test) > INSERT INTO t2 (a) VALUES (4294967295); # out of range for b, succeeds Query OK, 1 row affected (0.01 sec) mysql [localhost:8022] {msandbox} (test) > INSERT INTO t2 (a) VALUES (4294967296); # out of range for a, fails ERROR 1264 (22003): Out of range value for column 'a' at row 1 mysql [localhost:8022] {msandbox} (test) > INSERT INTO t2 (a) VALUES (-1); # out of range for a, fails ERROR 1264 (22003): Out of range value for column 'a' at row 1 mysql [localhost:8022] {msandbox} (test) > SELECT * FROM t2; +------------+----+ | a | b | +------------+----+ | 4294967295 | -1 | +------------+----+ 1 row in set (0.00 sec) mysql [localhost:8022] {msandbox} (test) > mysql [localhost:8022] {msandbox} (test) > CREATE TABLE t3 (a INT NOT NULL, b INT UNSIGNED AS (a) NOT NULL); Query OK, 0 rows affected (0.01 sec) mysql [localhost:8022] {msandbox} (test) > INSERT INTO t3(a) VALUES (-1); # out of range for b ERROR 1264 (22003): Out of range value for column 'b' at row 1 mysql [localhost:8022] {msandbox} (test) > SELECT * FROM t3; Empty set (0.00 sec) Suggested fix: Ideally they would not permit any overflow (as shown with regular columns).