List:Commits« Previous MessageNext Message »
From:Øystein Grøvlen Date:January 21 2011 9:33am
Subject:Re: bzr commit into mysql-trunk branch (roy.lyseng:3323) Bug#57623
View as plain text  
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
Thread
bzr commit into mysql-trunk branch (roy.lyseng:3323) Bug#57623Roy Lyseng13 Jan
  • Re: bzr commit into mysql-trunk branch (roy.lyseng:3323) Bug#57623Øystein Grøvlen21 Jan
    • Re: bzr commit into mysql-trunk branch (roy.lyseng:3323) Bug#57623Roy Lyseng24 Jan
      • Re: bzr commit into mysql-trunk branch (roy.lyseng:3323) Bug#57623Øystein Grøvlen24 Jan