Bug#37460 Assertion failed: !table->file || table->file->inited == handler::NONE

enable uncacheable flag if we update a view with check option
and check option has a subselect, otherwise, the check option
can be evaluated after the subselect was freed as independent
(See full_local in JOIN::join_free())


mysql-test/r/subselect.result:
  test result
mysql-test/t/subselect.test:
  test case
sql/mysql_priv.h:
  added UNCACHEABLE_CHECKOPTION flag
sql/sql_update.cc:
  enable uncacheable flag if we update a view with check option
  and check option has a subselect, otherwise, the check option
  can be evaluated after the subselect was freed as independent
  (See full_local in JOIN::join_free())

1 files changed, 30 insertions, 0 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index 6eeb652e3c1..8830ea11f97 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -4422,4 +4422,34 @@ id
 2
 3
 DROP TABLE t1, t2;
+CREATE TABLE t1 (id int);
+CREATE TABLE t2 (id int, c int);
+INSERT INTO t1 (id) VALUES (1);
+INSERT INTO t2 (id) VALUES (1);
+INSERT INTO t1 (id) VALUES (1);
+INSERT INTO t2 (id) VALUES (1);
+CREATE VIEW v1 AS
+SELECT t2.c AS c FROM t1, t2
+WHERE t1.id=t2.id AND 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
+UPDATE v1 SET c=1;
+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;
+INSERT INTO v2(a,b) VALUES (2,2);
+ERROR HY000: CHECK OPTION failed 'test.v2'
+INSERT INTO v2(a,b) VALUES (1,2);
+SELECT * FROM v1;
+c
+1
+1
+1
+1
+2
+2
+CREATE VIEW v3 AS
+SELECT t2.c AS c FROM t2
+WHERE 1 IN (SELECT id FROM t1) WITH CHECK OPTION;
+DELETE FROM v3;
+DROP VIEW v1,v2,v3;
+DROP TABLE t1,t2;
 End of 5.0 tests.