Hi Roy,
Thanks for the patch. I have verified that it fixes the issue at hand.
My only question is that when prep_on_expr is later set, will not
there be a need to do replace_subcondition on it?
--
Øystein
On 01/13/11 03:42 PM, Roy Lyseng wrote:
> #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);
> }
> }
>
>
>
>
>
--
Øystein Grøvlen, Principal Software Engineer
MySQL Group, Oracle
Trondheim, Norway
