bzr commit into mysql-6.0-codebase-bugfixing branch (tor.didriksen:3722) Bug#46744

[Tor Didriksen]

#At file:///export/home/didrik/mysqldev-6.0-codebase/6.0-codebase-bf-valgrind/ based on revid:kostja@stripped

 3722 Tor Didriksen	2009-11-23
      Bug #46744 Crash in optimize_semijoin_nests on empty view with limit and procedure.
      
      Skip optimize_semijoin_nests() if optimizer_switch = 'semijoin=off';
     @ mysql-test/r/subselect_sj.result
        Add test case.
     @ mysql-test/r/subselect_sj_jcl6.result
        Add test case.
     @ mysql-test/t/subselect_sj.test
        Add test case.
     @ sql/sql_select.cc
        Skip optimize_semijoin_nests() if OPTIMIZER_SWITCH_SEMIJOIN == false.

    modified:
      mysql-test/r/subselect_sj.result
      mysql-test/r/subselect_sj_jcl6.result
      mysql-test/t/subselect_sj.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2009-11-20 11:48:50 +0000
+++ b/mysql-test/r/subselect_sj.result	2009-11-23 10:27:34 +0000
@@ -463,6 +463,34 @@ int_key
 7
 DROP TABLE t0, t1, t2;
 # End of bug#46550
+#
+# Bug #46744 Crash in optimize_semijoin_nests on empty view
+# with limit and procedure.
+#
+DROP TABLE IF EXISTS t1, t2;
+DROP VIEW IF EXISTS v1;
+DROP PROCEDURE IF EXISTS p1;
+CREATE TABLE t1 ( f1 int );
+CREATE TABLE t2 ( f1 int );
+insert into t2 values (5), (7);
+CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 LIMIT 2;
+create procedure p1() 
+select COUNT(*) 
+FROM v1 WHERE f1 IN 
+(SELECT f1 FROM t2 WHERE f1 = ANY (SELECT f1 FROM v1));
+SET SESSION optimizer_switch = 'semijoin=on';
+CALL p1();
+COUNT(*)
+0
+SET SESSION optimizer_switch = 'semijoin=off';
+CALL p1();
+COUNT(*)
+0
+drop table t1, t2;
+drop view v1;
+drop procedure p1;
+set SESSION optimizer_switch='default';
+# End of bug#46744
 
 Bug#46797 "Crash in fix_semijoin_strategies_for_picked_join_order 
 with semijoin=on"

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2009-11-20 11:48:50 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2009-11-23 10:27:34 +0000
@@ -467,6 +467,34 @@ int_key
 7
 DROP TABLE t0, t1, t2;
 # End of bug#46550
+#
+# Bug #46744 Crash in optimize_semijoin_nests on empty view
+# with limit and procedure.
+#
+DROP TABLE IF EXISTS t1, t2;
+DROP VIEW IF EXISTS v1;
+DROP PROCEDURE IF EXISTS p1;
+CREATE TABLE t1 ( f1 int );
+CREATE TABLE t2 ( f1 int );
+insert into t2 values (5), (7);
+CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 LIMIT 2;
+create procedure p1() 
+select COUNT(*) 
+FROM v1 WHERE f1 IN 
+(SELECT f1 FROM t2 WHERE f1 = ANY (SELECT f1 FROM v1));
+SET SESSION optimizer_switch = 'semijoin=on';
+CALL p1();
+COUNT(*)
+0
+SET SESSION optimizer_switch = 'semijoin=off';
+CALL p1();
+COUNT(*)
+0
+drop table t1, t2;
+drop view v1;
+drop procedure p1;
+set SESSION optimizer_switch='default';
+# End of bug#46744
 
 Bug#46797 "Crash in fix_semijoin_strategies_for_picked_join_order 
 with semijoin=on"

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2009-11-20 11:48:50 +0000
+++ b/mysql-test/t/subselect_sj.test	2009-11-23 10:27:34 +0000
@@ -358,6 +358,42 @@ DROP TABLE t0, t1, t2;
 
 --echo # End of bug#46550
 
+--echo #
+--echo # Bug #46744 Crash in optimize_semijoin_nests on empty view
+--echo # with limit and procedure.
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+DROP VIEW IF EXISTS v1;
+DROP PROCEDURE IF EXISTS p1;
+--enable_warnings
+
+CREATE TABLE t1 ( f1 int );
+CREATE TABLE t2 ( f1 int );
+
+insert into t2 values (5), (7);
+
+CREATE OR REPLACE VIEW v1 AS SELECT * FROM t1 LIMIT 2;
+
+create procedure p1() 
+select COUNT(*) 
+FROM v1 WHERE f1 IN 
+(SELECT f1 FROM t2 WHERE f1 = ANY (SELECT f1 FROM v1));
+
+SET SESSION optimizer_switch = 'semijoin=on';
+CALL p1();
+SET SESSION optimizer_switch = 'semijoin=off';
+CALL p1();
+
+drop table t1, t2;
+drop view v1;
+drop procedure p1;
+
+set SESSION optimizer_switch='default';
+
+--echo # End of bug#46744
+
 --echo
 --echo Bug#46797 "Crash in fix_semijoin_strategies_for_picked_join_order 
 --echo with semijoin=on"

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2009-11-20 12:49:47 +0000
+++ b/sql/sql_select.cc	2009-11-23 10:27:34 +0000
@@ -4573,7 +4573,12 @@ static bool optimize_semijoin_nests(JOIN
   DBUG_ENTER("optimize_semijoin_nests");
   List_iterator<TABLE_LIST> sj_list_it(join->select_lex->sj_nests);
   TABLE_LIST *sj_nest;
-  if (optimizer_flag(join->thd, OPTIMIZER_SWITCH_MATERIALIZATION))
+  /*
+    The statement may have been executed with 'semijoin=on' earlier.
+    We need to verify that 'semijoin=on' still holds.
+   */
+  if (optimizer_flag(join->thd, OPTIMIZER_SWITCH_SEMIJOIN) &&
+      optimizer_flag(join->thd, OPTIMIZER_SWITCH_MATERIALIZATION))
   {
     while ((sj_nest= sj_list_it++))
     {


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