MySQL Bugs: #99640: add constraint return Out of range

Bug #99640	add constraint return Out of range
Submitted:	20 May 2020 6:03	Modified:	21 May 2020 12:01
Reporter:	wj huang	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.19	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Consider the following statement:

mysql> create table t(a int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values (1000);
Query OK, 1 row affected (0.00 sec)

mysql> alter table t add column b tinyint as ((a+1));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t add constraint c1 check (a>10);
ERROR 1264 (22003): Out of range value for column 'b' at row 1

expected result: execute succeeds.

How to repeat:
create table t(a int);
insert into t values (1000);
alter table t add column b tinyint as ((a+1));
alter table t add constraint c1 check (a>10);

Hi Mr. huang,

Thank you for your bug report.

However, this is not a bug.

Value of 1000 is far beyond the range of the TINYINT SQL type.

Not a bug.

Hi, Sinisa Milivojevic.

I add a check for column a, not for b.  I don't think it should return an error about b.

For comparison: consider the following statement:

mysql> create table t(a int check(a > 100));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values (1000);
Query OK, 1 row affected (0.00 sec)

mysql> alter table t add column b tinyint as ((a+1));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t;
+------+------+
| a    | b    |
+------+------+
| 1000 |  127 |
+------+------+
1 row in set (0.00 sec)

Hi,

Second example is OK because check condition is before ALTER.

Hi, Sinisa Milivojevic

BTW, I have a question about the error handling of the delete statement.

create table t(a int);
insert into t values (1000);
alter table t add column b tinyint as ((a+1));

Then, does MySQL takes the row as an invalid row? 
The document says "Strict SQL mode applies to the following statements under conditions for which some value might be out of range or an invalid row is inserted into or deleted from a table:"

And `delete from t` will succeed without error.

The confusing thing is how MySQL handles these errors when reading the row.

For DDLs like `add constraint` and `create index`, MySQL doesn't ignore these errors.
For DMLs `select` and `delete`, MySQL ignores these errors without warnings.
For DML `update`, MySQL doesn't ignore these error and your guys think it's a bug(https://bugs.mysql.com/bug.php?id=99568).

I agree with you.

This bug is a duplicate of the following one:

https://bugs.mysql.com/bug.php?id=99568

Because it is the same cause.