bzr commit into mysql-5.0-bugteam branch (joro:2804) Bug#46791

[Georgi Kodinov]

#At file:///home/kgeorge/mysql/work/B46791-5.0-bugteam/ based on revid:joro@stripped

 2804 Georgi Kodinov	2009-09-03
      Bug #46791: Assertion failed:(table->key_read==0),function unknown
        function,file sql_base.cc
      
      When uncacheable queries are written to a temp table the optimizer must 
      preserve the original JOIN structure, because it is re-using the JOIN 
      structure to read from the resulting temporary table.
      This was done only for uncacheable sub-queries. 
      But top level queries can also benefit from this mechanism, specially if 
      they're using index access and need a reset.
      Fixed by not limiting the saving of JOIN structure to subqueries
      exclusively.
      Added a new test file to extend the existing (large) subquery.test.

    added:
      mysql-test/r/subselect4.result
      mysql-test/t/subselect4.test
    modified:
      sql/sql_select.cc
=== added file 'mysql-test/r/subselect4.result'
--- a/mysql-test/r/subselect4.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/r/subselect4.result	2009-09-03 15:03:46 +0000
@@ -0,0 +1,30 @@
+#
+# Bug #46791: Assertion failed:(table->key_read==0),function unknown
+#    function,file sql_base.cc
+#
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+INSERT INTO t1 VALUES (1,1),(2,2);
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES (1,1),(2,2);
+CREATE TABLE t3 LIKE t1;
+# should have 1 impossible where and 2 dependent subqueries
+EXPLAIN
+SELECT 1 FROM t1
+WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3))
+ORDER BY count(*);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	index	NULL	a	5	NULL	2	Using index; Using temporary
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+3	DEPENDENT SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	no matching row in const table
+# should not crash the next statement
+SELECT 1 FROM t1
+WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3))
+ORDER BY count(*);
+1
+1
+# should not crash: the crash is caused by the previous statement
+SELECT 1;
+1
+1
+DROP TABLE t1,t2,t3;
+End of 5.0 tests.

=== added file 'mysql-test/t/subselect4.test'
--- a/mysql-test/t/subselect4.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/t/subselect4.test	2009-09-03 15:03:46 +0000
@@ -0,0 +1,32 @@
+# General purpose bug fix tests go here : subselect.test too large
+
+
+--echo #
+--echo # Bug #46791: Assertion failed:(table->key_read==0),function unknown
+--echo #    function,file sql_base.cc
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, KEY(a));
+INSERT INTO t1 VALUES (1,1),(2,2);
+CREATE TABLE t2 LIKE t1;
+INSERT INTO t2 VALUES (1,1),(2,2);
+CREATE TABLE t3 LIKE t1;
+
+--echo # should have 1 impossible where and 2 dependent subqueries
+EXPLAIN
+SELECT 1 FROM t1
+WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3))
+ORDER BY count(*);
+
+--echo # should not crash the next statement
+SELECT 1 FROM t1
+WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE 1 = (SELECT MIN(t2.b) FROM t3))
+ORDER BY count(*);
+
+--echo # should not crash: the crash is caused by the previous statement
+SELECT 1;
+
+DROP TABLE t1,t2,t3;
+
+
+--echo End of 5.0 tests.

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-08-27 11:40:42 +0000
+++ b/sql/sql_select.cc	2009-09-03 15:03:46 +0000
@@ -1474,12 +1474,8 @@ JOIN::optimize()
       }
     }
 
-    /* 
-      If this join belongs to an uncacheable subquery save 
-      the original join 
-    */
-    if (select_lex->uncacheable && !is_top_level_join() &&
-        init_save_join_tab())
+    /* If this join belongs to an uncacheable query save the original join */
+    if (select_lex->uncacheable && init_save_join_tab())
       DBUG_RETURN(-1);                         /* purecov: inspected */
   }
 


Attachment: [text/bzr-bundle] bzr/[e-mail]