Description:
Under sql_mode='traditional', all invalid values must be rejected.
The corollary to this is that, if a change would result in existing
values becoming invalid, the change must fail. But ALTER TABLE
currently accepts a data type modification, silently changing
any resulting invalid values.
How to repeat:
mysql> set sql_mode='traditional';
mysql> create table t1 (col1 int);
mysql> insert into t1 values(null),(50000);
mysql> select * from t1;
+-------+
| col1 |
+-------+
| NULL |
| 50000 |
+-------+
-- This is correct; both values are valid for the table definition.
mysql> alter table t1 modify col1 smallint not null;
Query OK, 2 rows affected, 2 warnings (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 2
-- This is the incorrect response. The values already in the
table are not valid for the new definition, so ALTER TABLE
should fail with SQLSTATE 42000 invalid definition for existing values
mysql> show warnings;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------+
| Warning | 1265 | Data truncated for column 'col1' at row 1 |
| Warning | 1264 | Out of range value adjusted for column 'col1' at row 2 |
+---------+------+--------------------------------------------------------+
-- Each warning shows a result that isn't allowed
in 'traditional' mode.
mysql> select * from t1;
+-------+
| col1 |
+-------+
| 0 |
| 32767 |
+-------+
-- Values should still be {NULL,50000}, because ALTER
TABLE should have failed.
Suggested fix:
Description: Under sql_mode='traditional', all invalid values must be rejected. The corollary to this is that, if a change would result in existing values becoming invalid, the change must fail. But ALTER TABLE currently accepts a data type modification, silently changing any resulting invalid values. How to repeat: mysql> set sql_mode='traditional'; mysql> create table t1 (col1 int); mysql> insert into t1 values(null),(50000); mysql> select * from t1; +-------+ | col1 | +-------+ | NULL | | 50000 | +-------+ -- This is correct; both values are valid for the table definition. mysql> alter table t1 modify col1 smallint not null; Query OK, 2 rows affected, 2 warnings (0.01 sec) Records: 2 Duplicates: 0 Warnings: 2 -- This is the incorrect response. The values already in the table are not valid for the new definition, so ALTER TABLE should fail with SQLSTATE 42000 invalid definition for existing values mysql> show warnings; +---------+------+--------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------+ | Warning | 1265 | Data truncated for column 'col1' at row 1 | | Warning | 1264 | Out of range value adjusted for column 'col1' at row 2 | +---------+------+--------------------------------------------------------+ -- Each warning shows a result that isn't allowed in 'traditional' mode. mysql> select * from t1; +-------+ | col1 | +-------+ | 0 | | 32767 | +-------+ -- Values should still be {NULL,50000}, because ALTER TABLE should have failed. Suggested fix: