MySQL Bugs: #84496: CHECK TABLE should support checking virtual column expressions

Bug #84496	CHECK TABLE should support checking virtual column expressions
Submitted:	12 Jan 2017 21:47	Modified:	13 Jan 2017 7:26
Reporter:	Morgan Tocker	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	8.0.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Because the default of virtual columns is to be instant WITHOUT VALIDATION, there exists a case where a poorly thought out definition can essentially block modification of a row.

How to repeat:
CREATE TABLE t1 (a INT NOT NULL primary key auto_increment, b varchar(255), c varchar(255)) ;
INSERT INTO t1 VALUES (NULL, 'b', 'c'), (NULL, REPEAT('b', 255), REPEAT('c', 255));

# default is WITHOUT VALIDATION
ALTER TABLE t1 ADD bc VARCHAR(255) AS (CONCAT(b,c)) VIRTUAL;

# now I can't modify a row if I tried:

mysql> update t1 set b = 'bbb' where a =2;
ERROR 1406 (22001): Data too long for column 'bc' at row 1

mysql> check table t1;
+---------+-------+----------+----------+
| Table   | Op    | Msg_type | Msg_text |
+---------+-------+----------+----------+
| test.t1 | check | status   | OK       |
+---------+-------+----------+----------+
1 row in set (0.00 sec)

mysql> OPTIMIZE TABLE t1;
+---------+----------+----------+-------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                          |
+---------+----------+----------+-------------------------------------------------------------------+
| test.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.t1 | optimize | status   | OK                                                                |
+---------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.74 sec)

There are no complaints the virtual column definition is wrong unless I do something like this (too much to expect from a user):

mysql> SELECT * FROM t1 WHERE CONVERT( CONCAT(b, c),char(255)) != concat(b,c) \G
*************************** 1. row ***************************
 a: 2
 b: bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
 c: ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
bc: bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
1 row in set, 1 warning (0.00 sec)

Suggested fix:
I think the default of WITHOUT VALIDATION is acceptable given the benefit (it is instant) and that it is not actually modifying data/corrupting it (it is virtual).  But there should be a way to check without locking that a tables virtual column definitions are correct.

Example:

mysql> check table t1;
+---------+-------+----------+------------------------------------+
| Table   | Op    | Msg_type | Msg_text                           |
+---------+-------+----------+------------------------------------+
| test.t1 | check | failed   | Virtual column bc failed on 1 rows |
+---------+-------+----------+------------------------------------+

# demonstrating that testcase does not produce warnings on reads:

mysql> select * from t1\G
*************************** 1. row ***************************
 a: 1
 b: b
 c: c
bc: bc
*************************** 2. row ***************************
 a: 2
 b: bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
 c: ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
bc: bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
2 rows in set (0.00 sec)

I wonder if the discussion shouldn't go beyond and make it check on ALTER TABLES (where VIRTUAL columns are done WITHOUT VALIDATION by default).

Make it WITH VALIDATION for virtual if STRICT mode is enabled. The documentation itself says it can cause weird behavior for overflow cases, but it feels inconsistent allowing this to run in a STRICT environment.

Hello Morgan,

Thank you for the report and feature request!

Thanks,
Umesh