From: Sergey Glukhov
Date: October 13 2008 9:30am
Subject: bzr commit into mysql-5.0-bugteam branch (Sergey.Glukhov:2702)
 Bug#37460
List-Archive: http://lists.mysql.com/commits/56098
X-Bug: 37460
Message-Id: <0K8O0000H7QTD950@fe-emea-09.sun.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7BIT

#At file:///home/gluh/MySQL/mysql-5.0-bug-37460/

 2702 Sergey Glukhov	2008-10-13
      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())
modified:
  mysql-test/r/subselect.result
  mysql-test/t/subselect.test
  sql/mysql_priv.h
  sql/sql_update.cc

per-file messages:
  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())
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2008-07-26 20:44:07 +0000
+++ b/mysql-test/r/subselect.result	2008-10-13 09:29:59 +0000
@@ -4407,4 +4407,34 @@ pk	a
 3	30
 2	20
 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.

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2008-07-26 20:44:07 +0000
+++ b/mysql-test/t/subselect.test	2008-10-13 09:29:59 +0000
@@ -3307,5 +3307,39 @@ SELECT * FROM t1
    WHERE EXISTS (SELECT DISTINCT a FROM t2 WHERE t1.a < t2.a ORDER BY b);
 DROP TABLE t1,t2;
 
---echo End of 5.0 tests.
+#
+# Bug#37460 Assertion failed:
+# !table->file || table->file->inited == handler::NONE
+#
+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;
+
+--error 1369
+INSERT INTO v2(a,b) VALUES (2,2);
+INSERT INTO v2(a,b) VALUES (1,2);
+SELECT * FROM v1;
 
+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;
+
+--echo End of 5.0 tests.

=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h	2008-10-02 11:57:52 +0000
+++ b/sql/mysql_priv.h	2008-10-13 09:29:59 +0000
@@ -435,6 +435,7 @@ MY_LOCALE *my_locale_by_number(uint numb
 #define UNCACHEABLE_PREPARE    16
 /* For uncorrelated SELECT in an UNION with some correlated SELECTs */
 #define UNCACHEABLE_UNITED     32
+#define UNCACHEABLE_CHECKOPTION   64
 
 /* Used to check GROUP BY list in the MODE_ONLY_FULL_GROUP_BY mode */
 #define UNDEF_POS (-1)

=== modified file 'sql/sql_update.cc'
--- a/sql/sql_update.cc	2008-07-15 14:13:21 +0000
+++ b/sql/sql_update.cc	2008-10-13 09:29:59 +0000
@@ -1218,6 +1218,26 @@ multi_update::initialize_tables(JOIN *jo
       }
     }
 
+    /*
+      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()).
+    */
+    if (table_ref->check_option && !join->select_lex->uncacheable)
+    {
+      SELECT_LEX_UNIT *tmp_unit;
+      SELECT_LEX *sl;
+      for (tmp_unit= join->select_lex->first_inner_unit();
+           tmp_unit;
+           tmp_unit= tmp_unit->next_unit())
+        for (sl= tmp_unit->first_select(); sl; sl= sl->next_select())
+        {
+          if (sl->master_unit()->item)
+            join->select_lex->uncacheable|= UNCACHEABLE_CHECKOPTION;
+        }
+    }
+
     if (table == first_table_for_update && table_ref->check_option)
     {
       table_map unupdated_tables= table_ref->check_option->used_tables() &