MySQL Bugs: #77478: Generated columns: STORED column rejected, but VIRTUAL is accepted

Bug #77478	Generated columns: STORED column rejected, but VIRTUAL is accepted
Submitted:	25 Jun 2015 7:22	Modified:	5 Oct 2015 15:02
Reporter:	Roy Lyseng	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.8	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When creating a generated column with an expression that is outside the range of the data type, a STORED column may be rejected but a VIRTUAL column is accepted.

On SELECT of such virtual column, some value is returned, accompanied with a warning.

Also, if one of the base columns is updated so that the generated value is still invalid, the update will be rejected.

How to repeat:
create table t1(a integer, b integer) engine=innodb;
insert into t1 values(0,2147483647);
alter table t1 add column c smallint as (b) stored;
ERROR 1264 (22003): Out of range value for column 'c' at row 1
alter table t1 add column c smallint as (b) virtual;
select * from t1;
+------+------------+-------+
| a    | b          | c     |
+------+------------+-------+
|    0 | 2147483647 | 32767 |
+------+------------+-------+
1 row in set, 1 warning (0.00 sec)
show warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1264 | Out of range value for column 'c' at row 1 |
+---------+------+--------------------------------------------+
1 row in set (0.00 sec)
update t1 set b=b-1;
ERROR 1264 (22003): Out of range value for column 'c' at row 1

Suggested fix:
When creating a generated column using an in-place algorithm, make sure to run a table scan to validate
the GC expression against the specified data type.

Posted by developer:
 
Strange facts:

drop table t1;
create table t1(a integer, b integer) engine=innodb;
insert into t1 values(0,2147483647);
#alter table t1 add column c smallint as (b) stored;
alter table t1 add column c smallint as (b) virtual;
select * from t1;

drop table t1;
create table t1(a integer, b integer) engine=innodb;
insert into t1 values(0,2147483647);
alter table t1 add column c smallint as (b) stored;
alter table t1 add column c smallint as (b) virtual;
select * from t1;

first SELECT shows no warning, second SELECT does. There should always be a warning.

Noted in 5.7.9, 5.8.0 changelogs.

When a VIRTUAL generated column was added to a table, it was not
ensured that data being calculated by the generated column expression
would not be out of range for the column. This could lead to
inconsistent data being returned and unexpectedly failed statements.

ALTER TABLE now supports WITHOUT VALIDATION and WITH VALIDATION
clauses to control whether ALTER TABLE validates the data for a
VIRTUAL generated column:

* With WITHOUT VALIDATION (the default if neither clause is specified),
  an in-place operation is performed (if possible), data integrity is
  not checked, and the statement finishes more quickly. However, later
  reads from the table might report warnings or errors for the column
  if values are out of range.

* With WITH VALIDATION, ALTER TABLE copies the table. If an
  out-of-range or any other error occurs, the statement fails. Because
  a table copy is performed, the statement takes longer.

WITHOUT VALIDATION and WITH VALIDATION are permitted only with ADD
COLUMN, CHANGE COLUMN, and MODIFY COLUMN operations.