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-06
      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: Save join->table value for subquery and restore it on each execution.
modified:
  mysql-test/r/subselect.result
  mysql-test/t/subselect.test
  sql/item_subselect.cc
  sql/item_subselect.h

per-file messages:
  mysql-test/r/subselect.result
    test result
  mysql-test/t/subselect.test
    test case
  sql/item_subselect.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: Save join->table value for subquery and restore it on each execution.
  sql/item_subselect.h
    adedd new field 'save_table'
=== 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-06 16:37:34 +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-06 16:37:34 +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/item_subselect.cc'
--- a/sql/item_subselect.cc	2008-03-28 11:31:52 +0000
+++ b/sql/item_subselect.cc	2008-08-06 16:37:34 +0000
@@ -1582,7 +1582,7 @@ subselect_single_select_engine(st_select
 			       Item_subselect *item_arg)
   :subselect_engine(item_arg, result_arg),
    prepared(0), optimized(0), executed(0),
-   select_lex(select), join(0)
+   select_lex(select), join(0), save_table(0)
 {
   select_lex->master_unit()->item= item_arg;
 }
@@ -1593,6 +1593,7 @@ void subselect_single_select_engine::cle
   DBUG_ENTER("subselect_single_select_engine::cleanup");
   prepared= optimized= executed= 0;
   join= 0;
+  save_table= 0;
   result->cleanup();
   DBUG_VOID_RETURN;
 }
@@ -1802,6 +1803,12 @@ int subselect_single_select_engine::exec
       thd->lex->current_select= save_select;
       DBUG_RETURN(join->error ? join->error : 1);
     }
+    /* 
+       Subselect join->table field may be cleared by upper JOIN::join_free()
+       So we need to save this value on optimization stage and restore it
+       on each execution.
+    */
+    save_table= join->table;
     if (!select_lex->uncacheable && thd->lex->describe && 
         !(join->select_options & SELECT_DESCRIBE) && 
         join->need_tmp && item->const_item())
@@ -1822,6 +1829,8 @@ int subselect_single_select_engine::exec
       DBUG_RETURN(1);
     }
   }
+  else
+    join->table= save_table;
   if (select_lex->uncacheable &&
       select_lex->uncacheable != UNCACHEABLE_EXPLAIN
       && executed)

=== modified file 'sql/item_subselect.h'
--- a/sql/item_subselect.h	2007-10-30 12:27:21 +0000
+++ b/sql/item_subselect.h	2008-08-06 16:37:34 +0000
@@ -417,6 +417,7 @@ class subselect_single_select_engine: pu
   my_bool executed; /* simple subselect is executed */
   st_select_lex *select_lex; /* corresponding select_lex */
   JOIN * join; /* corresponding JOIN structure */
+  TABLE **save_table; /* Save join->table value after optimization */
 public:
   subselect_single_select_engine(st_select_lex *select,
 				 select_subselect *result,