#At file:///home/rl136806/mysql/repo/mysql-review/ based on revid:roy.lyseng@stripped
3323 Roy Lyseng 2011-01-13
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
Added test results for bug#58561.
mysql-test/r/subquery_sj_all_jcl6.result
Added test results for bug#58561.
mysql-test/r/subquery_sj_all_jcl7.result
I think you are starting to get the picture by now...
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
Added test on *tree being non-NULL before calling replace_subcondition()
in JOIN::flatten_subqueries().
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-13 14:21:49 +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-13 14:21:49 +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-13 14:21:49 +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-13 14:21:49 +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-13 14:21:49 +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-13 14:21:49 +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-13 14:21:49 +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-13 14:21:49 +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-13 14:21:49 +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-13 14:21:49 +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-13 14:21:49 +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-13 14:21:49 +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-13 14:21:49 +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-13 14:21:49 +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-13 14:21:49 +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-13 14:21:49 +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 2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2011-01-13 14:21:49 +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 2010-11-29 13:30:18 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2011-01-13 14:21:49 +0000
@@ -5478,4 +5478,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 2010-11-29 13:04:34 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result 2011-01-13 14:21:49 +0000
@@ -5482,5 +5482,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 2010-11-29 13:04:34 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result 2011-01-13 14:21:49 +0000
@@ -5482,5 +5482,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-13 08:47:33 +0000
+++ b/sql/sql_select.cc 2011-01-13 14:21:49 +0000
@@ -4143,8 +4143,17 @@ skip_conversion:
&select_lex->prep_where :
&((*subq)->embedding_join_nest->prep_on_expr);
- if (replace_subcondition(this, tree, *subq, substitute,
- FALSE))
+ /*
+ 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 == (TABLE_LIST*)1 ||
+ (*subq)->embedding_join_nest->nested_join == NULL) ==
+ (*tree != 0));
+ if (*tree && replace_subcondition(this, tree, *subq, substitute, FALSE))
DBUG_RETURN(TRUE);
}
}
Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20110113142149-f4nomc3bqan9zchu.bundle
