bzr commit into mysql-5.0 branch (gluh:2647) Bug#37460

[Sergey Glukhov]

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

 2647 Sergey Glukhov	2008-08-28
      The Bug#37460 Assertion failed: !table->file ||
table->file->inited==handler::NONE
      The problem:
      subselect_single_select_engine on optimization stage is executed and
      sets the 'executed' variable value to 'true'.
      Because of that we call cleanup_all_joins() with 'true' argument in JOIN::join_free
      and upper join sets lower join field 'table' to 0.
      Next subquery calls do not execute 'file->ha_index_or_rnd_end()
      (see  JOIN::cleanup func). It leads to assertion failure.
      The fix:
      Call cleanup_all_joins() with 'false' argument.
modified:
  mysql-test/r/subselect.result
  mysql-test/t/subselect.test
  sql/sql_select.cc

per-file messages:
  mysql-test/r/subselect.result
    test result
  mysql-test/t/subselect.test
    test case
  sql/sql_select.cc
    The problem:
    subselect_single_select_engine on optimization stage is executed and
    sets the 'executed' variable value to 'true'.
    Because of that we call cleanup_all_joins() with 'true' argument in JOIN::join_free
    and upper join sets lower join field 'table' to 0.
    Next subquery calls do not execute 'file->ha_index_or_rnd_end()
    (see  JOIN::cleanup func). It leads to assertion failure.
    The fix:
    Call cleanup_all_joins() with 'false' argument.
=== modified file 'mysql-test/r/subselect.result'
--- a/mysql-test/r/subselect.result	2008-05-16 14:05:55 +0000
+++ b/mysql-test/r/subselect.result	2008-08-28 12:17:32 +0000
@@ -4396,4 +4396,22 @@ id	select_type	table	type	possible_keys	
 Warnings:
 Note	1003	select 1 AS `1` from `test`.`t1` where
<in_optimizer>(1,<exists>(select 1 AS `1` from `test`.`t1` where
(`test`.`t1`.`a` > 3) group by `test`.`t1`.`a` having (<cache>(1) =
<ref_null_helper>(1))))
 DROP TABLE t1;
+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;
+SELECT * FROM v1;
+c
+1
+1
+1
+1
+DROP VIEW v1;
+DROP TABLE t1,t2;
 End of 5.0 tests.

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2008-06-25 14:59:38 +0000
+++ b/mysql-test/t/subselect.test	2008-08-28 12:17:32 +0000
@@ -3295,5 +3295,25 @@ EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 
 EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE 1 IN (SELECT 1 FROM t1 WHERE a > 3 GROUP BY
a);
 DROP TABLE t1;
 
+#
+# 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;
+SELECT * FROM v1;
+DROP VIEW v1;
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests.
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2008-07-15 14:13:21 +0000
+++ b/sql/sql_select.cc	2008-08-28 12:17:32 +0000
@@ -6378,20 +6378,8 @@ void JOIN::join_free()
        tmp_unit= tmp_unit->next_unit())
     for (sl= tmp_unit->first_select(); sl; sl= sl->next_select())
     {
-      Item_subselect *subselect= sl->master_unit()->item;
-      bool full_local= full && (!subselect || subselect->is_evaluated());
-      /*
-        If this join is evaluated, we can fully clean it up and clean up all
-        its underlying joins even if they are correlated -- they will not be
-        used any more anyway.
-        If this join is not yet evaluated, we still must clean it up to
-        close its table cursors -- it may never get evaluated, as in case of
-        ... HAVING FALSE OR a IN (SELECT ...))
-        but all table cursors must be closed before the unlock.
-      */
-      sl->cleanup_all_joins(full_local);
-      /* Can't unlock if at least one JOIN is still needed */
-      can_unlock= can_unlock && full_local;
+      sl->cleanup_all_joins(false);
+      can_unlock= false;
     }
 
   /*