Description:
Inserting values to a view with check option raises an unexpected error, when the values are visible through the view.
How to repeat:
CREATE TABLE t1 (id int);
CREATE TABLE t2 (id int, c int);
INSERT INTO t1 (id) VALUES (1);
INSERT INTO t1 (id) VALUES (2);
INSERT INTO t2 (id, c) VALUES (1, 2), (2, 2);
CREATE VIEW v2 (a,b) AS
SELECT t2.id, t2.c AS c FROM t1, t2
WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
mysql-8.0.27 > SELECT * FROM v2;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
| 2 | 2 |
+------+------+
2 rows in set (0.00 sec)
mysql-8.0.27 > INSERT INTO v2(a,b) VALUES (1,2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v2'
mysql-8.0.27 > SELECT * FROM v2 LIMIT 1;
+------+------+
| a | b |
+------+------+
| 1 | 2 |
+------+------+
1 row in set (0.01 sec)
mysql-8.0.27 > INSERT INTO v2(a,b) VALUES (1,2);
Query OK, 1 row affected (0.00 sec)
mysql-8.0.27 > INSERT INTO v2(a,b) VALUES (2,2);
ERROR 1369 (HY000): CHECK OPTION failed 'test.v2'
Actually, we should be able to insert these two values by the view. But some unexpected behaviors occur.
Suggested fix:
I think the server doesn't properly fill the actual value of `t1.id` to the field, but keeps the value of TABLE cache since the last execution when TABLE_LIST::view_check_option().
