#At file:///home/rl136806/mysql/repo/mysql-work5/ based on revid:jorgen.loland@stripped
3334 Roy Lyseng 2011-01-27
Bug#57623: subquery within before insert trigger causes crash (semijoin=on)
Crash when attempting to transform a subquery using IN_TO_EXISTS inside
JOIN::flatten_subqueries(), when semijoin transformation is impossible
for the subquery, and in prepared (non-conventional) mode.
The problem is that replace_subcondition() is attempted with prep_on_expr as
"tree" argument, but prep_on_expr has not yet been set. prep_on_expr
is set for prepared statements in fix_prepare_info_in_table_list(), but only
for table objects, not for join nest objects. In this case, prep_on_expr
is not set before the subsequently called simplify_joins() function, which
will propagate the desired information.
mysql-test/include/subquery_sj.inc
Added test case for bug#57623.
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_jcl6.result
mysql-test/r/subquery_sj_all_jcl7.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_jcl6.result
mysql-test/r/subquery_sj_dupsweed_jcl7.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_jcl6.result
mysql-test/r/subquery_sj_firstmatch_jcl7.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_jcl6.result
mysql-test/r/subquery_sj_loosescan_jcl7.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_jcl6.result
mysql-test/r/subquery_sj_mat_jcl7.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_jcl6.result
mysql-test/r/subquery_sj_none_jcl7.result
Added test results for bug#57623.
sql/sql_select.cc
Added test on *tree being non-NULL before calling replace_subcondition()
in JOIN::flatten_subqueries().
Added DBUG_ASSERT to validate that the assumption for this fix is true.
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_jcl6.result
mysql-test/r/subquery_sj_all_jcl7.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_jcl6.result
mysql-test/r/subquery_sj_dupsweed_jcl7.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_jcl6.result
mysql-test/r/subquery_sj_firstmatch_jcl7.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_jcl6.result
mysql-test/r/subquery_sj_loosescan_jcl7.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_jcl6.result
mysql-test/r/subquery_sj_mat_jcl7.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_jcl6.result
mysql-test/r/subquery_sj_none_jcl7.result
sql/sql_select.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2010-11-24 14:06:22 +0000
+++ b/mysql-test/include/subquery_sj.inc 2011-01-27 11:38:22 +0000
@@ -3472,3 +3472,35 @@ eval explain $query;
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
--echo # End of the test for bug#52068.
+
+--echo #
+--echo # Bug#57623: subquery within before insert trigger causes crash (sj=on)
+--echo #
+
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+
+let $query=
+SELECT *
+FROM ot1
+ LEFT JOIN
+ (ot2 JOIN ot3 on ot2.a=ot3.a)
+ ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+
+eval explain $query;
+eval $query;
+eval prepare s from '$query';
+execute s;
+execute s;
+deallocate prepare s;
+
+DROP TABLE ot1, ot2, ot3, it1;
+
+--echo # End of the test for bug#57623.
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2010-11-30 13:55:22 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2011-01-27 11:38:22 +0000
@@ -5330,4 +5330,67 @@ id select_type table type possible_keys
1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
+#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result 2010-11-30 13:55:22 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result 2011-01-27 11:38:22 +0000
@@ -5334,5 +5334,68 @@ id select_type table type possible_keys
1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
+#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result 2010-11-30 13:55:22 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result 2011-01-27 11:38:22 +0000
@@ -5334,5 +5334,68 @@ id select_type table type possible_keys
1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
+#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2011-01-27 11:38:22 +0000
@@ -5329,4 +5329,67 @@ id select_type table type possible_keys
1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (BNL, incremental buffers)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
+#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-11-29 13:04:34 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2011-01-27 11:38:22 +0000
@@ -5333,5 +5333,68 @@ id select_type table type possible_keys
1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (BNL, incremental buffers)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
+#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2010-11-29 13:04:34 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2011-01-27 11:38:22 +0000
@@ -5333,5 +5333,68 @@ id select_type table type possible_keys
1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (BNL, regular buffers)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
+#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2011-01-27 11:38:22 +0000
@@ -5331,6 +5331,69 @@ id select_type table type possible_keys
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
+#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
# certain query plans
#
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl6.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-11-29 13:04:34 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2011-01-27 11:38:22 +0000
@@ -5335,6 +5335,69 @@ id select_type table type possible_keys
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
+#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
# certain query plans
#
=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-11-29 13:04:34 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2011-01-27 11:38:22 +0000
@@ -5335,6 +5335,69 @@ id select_type table type possible_keys
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
+#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
# certain query plans
#
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2011-01-27 11:38:22 +0000
@@ -5330,4 +5330,67 @@ id select_type table type possible_keys
1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (BNL, incremental buffers)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
+#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-11-29 13:04:34 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result 2011-01-27 11:38:22 +0000
@@ -5334,5 +5334,68 @@ id select_type table type possible_keys
1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (BNL, incremental buffers)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
+#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-11-29 13:04:34 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result 2011-01-27 11:38:22 +0000
@@ -5334,5 +5334,68 @@ id select_type table type possible_keys
1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; End temporary; Using join buffer (BNL, regular buffers)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
+#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2011-01-27 11:38:22 +0000
@@ -5330,4 +5330,67 @@ id select_type table type possible_keys
1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
+#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result 2010-11-29 13:04:34 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result 2011-01-27 11:38:22 +0000
@@ -5334,5 +5334,68 @@ id select_type table type possible_keys
1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
+#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result 2010-11-29 13:04:34 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result 2011-01-27 11:38:22 +0000
@@ -5334,5 +5334,68 @@ id select_type table type possible_keys
1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
+#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2011-01-13 15:37:54 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2011-01-27 11:38:22 +0000
@@ -5552,4 +5552,67 @@ id select_type table type possible_keys
2 SUBQUERY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, incremental buffers)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
+#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+2 SUBQUERY it1 ALL NULL NULL NULL NULL 8
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2011-01-13 10:48:28 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2011-01-27 11:38:22 +0000
@@ -5475,4 +5475,67 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY it3 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (BNL, incremental buffers)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
+#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result 2011-01-13 15:37:54 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result 2011-01-27 11:38:22 +0000
@@ -5479,5 +5479,68 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY it3 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (BNL, incremental buffers)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
+#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+6 6 6
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result 2011-01-13 15:37:54 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result 2011-01-27 11:38:22 +0000
@@ -5479,5 +5479,68 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY it3 ALL NULL NULL NULL NULL 6 Using where; Using join buffer (BNL, regular buffers)
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
# End of the test for bug#52068.
+#
+# Bug#57623: subquery within before insert trigger causes crash (sj=on)
+#
+CREATE TABLE ot1(a INT);
+CREATE TABLE ot2(a INT);
+CREATE TABLE ot3(a INT);
+CREATE TABLE it1(a INT);
+INSERT INTO ot1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+INSERT INTO ot2 VALUES(0),(2),(4),(6);
+INSERT INTO ot3 VALUES(0),(3),(6);
+INSERT INTO it1 VALUES(0),(1),(2),(3),(4),(5),(6),(7);
+explain SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 8
+1 PRIMARY ot3 ALL NULL NULL NULL NULL 3 Using where
+1 PRIMARY ot2 ALL NULL NULL NULL NULL 4 Using where
+2 DEPENDENT SUBQUERY it1 ALL NULL NULL NULL NULL 8 Using where
+SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1);
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+prepare s from 'SELECT *
+FROM ot1
+LEFT JOIN
+(ot2 JOIN ot3 on ot2.a=ot3.a)
+ON ot1.a=ot2.a AND ot1.a IN (SELECT a from it1)';
+execute s;
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+execute s;
+a a a
+0 0 0
+1 NULL NULL
+2 NULL NULL
+3 NULL NULL
+4 NULL NULL
+5 NULL NULL
+6 6 6
+7 NULL NULL
+deallocate prepare s;
+DROP TABLE ot1, ot2, ot3, it1;
+# End of the test for bug#57623.
set optimizer_switch=default;
set optimizer_join_cache_level=default;
=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc 2011-01-25 09:55:55 +0000
+++ b/sql/sql_select.cc 2011-01-27 11:38:22 +0000
@@ -4134,22 +4134,38 @@ skip_conversion:
(*subq)->fixed= 1;
Item *substitute= (*subq)->substitution;
- bool do_fix_fields= !(*subq)->substitution->fixed;
- Item **tree= ((*subq)->embedding_join_nest == (TABLE_LIST*)1)?
- &conds : &((*subq)->embedding_join_nest->on_expr);
+ const bool do_fix_fields= !(*subq)->substitution->fixed;
+ const bool subquery_in_join_clause=
+ ((*subq)->embedding_join_nest != (TABLE_LIST*)1);
+
+ Item **tree= subquery_in_join_clause ?
+ &((*subq)->embedding_join_nest->on_expr) :
+ &conds;
if (replace_subcondition(this, tree, *subq, substitute, do_fix_fields))
DBUG_RETURN(TRUE);
(*subq)->substitution= NULL;
if (!thd->stmt_arena->is_conventional())
{
- tree= ((*subq)->embedding_join_nest == (TABLE_LIST*)1)?
- &select_lex->prep_where :
- &((*subq)->embedding_join_nest->prep_on_expr);
+ if (subquery_in_join_clause)
+ {
+ tree= &((*subq)->embedding_join_nest->prep_on_expr);
+ /*
+ Some precaution is needed when dealing with PS/SP:
+ fix_prepare_info_in_table_list() sets prep_on_expr, but only for
+ tables, not for join nest objects. This is instead populated in
+ simplify_joins(), which is called after this function. Hence, we need
+ to check that *tree is non-NULL before calling replace_subcondition.
+ */
+ DBUG_ASSERT((*subq)->embedding_join_nest->nested_join ?
+ *tree == NULL :
+ *tree != NULL);
+ }
+ else
+ tree= &select_lex->prep_where;
- if (replace_subcondition(this, tree, *subq, substitute,
- FALSE))
- DBUG_RETURN(TRUE);
+ if (*tree && replace_subcondition(this, tree, *subq, substitute, false))
+ DBUG_RETURN(true);
}
}
Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20110127113822-to5kl0xi16hbz7c9.bundle
| Thread |
|---|
| • bzr commit into mysql-trunk branch (roy.lyseng:3334) Bug#57623 | Roy Lyseng | 27 Jan |
