#At file:///export/home/didrik/mysqldev-6.0-codebase/6.0-codebase-bf-valgrind/ based on revid:guilhem@stripped
3718 Tor Didriksen 2009-11-19
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-17 10:12:07 +0000
+++ b/mysql-test/r/subselect_sj.result 2009-11-19 13:10:16 +0000
@@ -463,3 +463,31 @@ 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
=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result 2009-11-17 10:12:07 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result 2009-11-19 13:10:16 +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
set join_cache_level=default;
show variables like 'join_cache_level';
Variable_name Value
=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test 2009-11-17 10:12:07 +0000
+++ b/mysql-test/t/subselect_sj.test 2009-11-19 13:10:16 +0000
@@ -357,3 +357,39 @@ WHERE t0.varchar_nokey IN (
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
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2009-11-17 10:12:07 +0000
+++ b/sql/sql_select.cc 2009-11-19 13:10:16 +0000
@@ -4573,7 +4573,8 @@ 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))
+ 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/tor.didriksen@sun.com-20091119131016-0wmyektwvllcn3r0.bundle
