MySQL Lists: commits: bzr push into mysql-next-mr-bugfixing branch (tor.didriksen:3296 to 3297)

List:	Commits	« Previous Message Next Message »
From:	Tor Didriksen	Date:	September 21 2010 10:33am
Subject:	bzr push into mysql-next-mr-bugfixing branch (tor.didriksen:3296 to 3297)
View as plain text
 3297 Tor Didriksen	2010-09-21 [merge]
      Auto-merge: next-mr-opt-team => next-mr-bugfixing

    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/item_subselect.cc
      sql/sql_select.cc
 3296 Vladislav Vaintroub	2010-09-21 [merge]
      merge

    modified:
      packaging/WiX/mysql_server.wxs.in
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2010-09-07 19:39:01 +0000
+++ b/mysql-test/include/subquery_sj.inc	2010-09-20 14:06:02 +0000
@@ -3307,3 +3307,22 @@ eval explain $query;
 eval $query;
 
 drop table t1,t2,t3;
+
+--echo #
+--echo # Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+--echo #
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+
+let $query=
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+               FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+eval explain $query;
+eval $query;
+
+DROP TABLE t1,t2,t3;

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2010-09-20 14:06:02 +0000
@@ -2577,10 +2577,10 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start materialize
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
+1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End materialize
 drop table t0, t1,t2,t3;
 
 Test that MaterializeLookup strategy for semijoin,
@@ -5124,4 +5124,28 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result	2010-09-20 14:06:02 +0000
@@ -2581,10 +2581,10 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start materialize
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
+1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End materialize
 drop table t0, t1,t2,t3;
 
 Test that MaterializeLookup strategy for semijoin,
@@ -5128,6 +5128,30 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_all_jcl7.result'
--- a/mysql-test/r/subquery_sj_all_jcl7.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result	2010-09-20 14:06:02 +0000
@@ -2581,10 +2581,10 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start materialize
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
+1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End materialize
 drop table t0, t1,t2,t3;
 
 Test that MaterializeLookup strategy for semijoin,
@@ -5128,6 +5128,30 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2010-09-20 14:06:02 +0000
@@ -5122,4 +5122,28 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result	2010-09-20 14:06:02 +0000
@@ -5126,6 +5126,30 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl7.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result	2010-09-20 14:06:02 +0000
@@ -5126,6 +5126,30 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2010-09-10 05:27:42 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2010-09-20 14:06:02 +0000
@@ -2577,10 +2577,10 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
+1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; FirstMatch(t0)
 drop table t0, t1,t2,t3;
 
 Test that MaterializeLookup strategy for semijoin,
@@ -5124,6 +5124,30 @@ a
 1
 drop table t1,t2,t3;
 #
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
+#
 # 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-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result	2010-09-20 14:06:02 +0000
@@ -2581,10 +2581,10 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
+1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; FirstMatch(t0)
 drop table t0, t1,t2,t3;
 
 Test that MaterializeLookup strategy for semijoin,
@@ -5127,6 +5127,113 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
+#
+# Bug#51457 Firstmatch semijoin strategy gives wrong results for
+#           certain query plans
+#
+SET @@default_storage_engine='innodb';
+SET @@optimizer_switch='semijoin=on,materialization=off,firstmatch=on,loosescan=off';
+SET @@optimizer_join_cache_level=0;
+CREATE TABLE t0(a INTEGER);
+CREATE TABLE t1(a INTEGER);
+INSERT INTO t1 VALUES(1);
+CREATE TABLE t2(a INTEGER);
+INSERT INTO t2 VALUES(5), (8);
+CREATE TABLE t6(a INTEGER);
+INSERT INTO t6 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE t8(a INTEGER);
+INSERT INTO t8 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+EXPLAIN
+SELECT *
+FROM t2 AS nt2
+WHERE 1 IN (SELECT it1.a
+FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch
+1	PRIMARY	nt2	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch(nt2)
+SELECT *
+FROM t2 AS nt2
+WHERE 1 IN (SELECT it1.a
+FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
+a
+5
+8
+EXPLAIN
+SELECT *
+FROM t2 AS nt2, t8 AS nt4
+WHERE 1 IN (SELECT it1.a
+FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch
+1	PRIMARY	nt2	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch(nt2)
+1	PRIMARY	nt4	ALL	NULL	NULL	NULL	NULL	8	
+SELECT *
+FROM t2 AS nt2, t8 AS nt4
+WHERE 1 IN (SELECT it1.a
+FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
+a	a
+5	1
+5	3
+5	5
+5	7
+5	9
+5	7
+5	3
+5	1
+8	1
+8	3
+8	5
+8	7
+8	9
+8	7
+8	3
+8	1
+EXPLAIN
+SELECT *
+FROM t0 AS ot1, t2 AS nt3
+WHERE ot1.a IN (SELECT it2.a
+FROM t1 AS it2 JOIN t8 AS it4 ON it2.a=it4.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(ot1)
+1	PRIMARY	nt3	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	it4	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(nt3)
+SELECT *
+FROM t0 as ot1, t2 AS nt3
+WHERE ot1.a IN (SELECT it2.a
+FROM t1 AS it2 JOIN t8 AS it4 ON it2.a=it4.a);
+a	a
+DROP TABLE t0, t1, t2, t6, t8;
+SET @@default_storage_engine=default;
+SET @@optimizer_switch=default;
+SET @@optimizer_join_cache_level=default;
+# End of bug#51457
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_firstmatch_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result	2010-09-20 14:06:02 +0000
@@ -2581,10 +2581,10 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
+1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; FirstMatch(t0)
 drop table t0, t1,t2,t3;
 
 Test that MaterializeLookup strategy for semijoin,
@@ -5127,6 +5127,113 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; FirstMatch(t1)
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
+#
+# Bug#51457 Firstmatch semijoin strategy gives wrong results for
+#           certain query plans
+#
+SET @@default_storage_engine='innodb';
+SET @@optimizer_switch='semijoin=on,materialization=off,firstmatch=on,loosescan=off';
+SET @@optimizer_join_cache_level=0;
+CREATE TABLE t0(a INTEGER);
+CREATE TABLE t1(a INTEGER);
+INSERT INTO t1 VALUES(1);
+CREATE TABLE t2(a INTEGER);
+INSERT INTO t2 VALUES(5), (8);
+CREATE TABLE t6(a INTEGER);
+INSERT INTO t6 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE t8(a INTEGER);
+INSERT INTO t8 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+EXPLAIN
+SELECT *
+FROM t2 AS nt2
+WHERE 1 IN (SELECT it1.a
+FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch
+1	PRIMARY	nt2	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch(nt2)
+SELECT *
+FROM t2 AS nt2
+WHERE 1 IN (SELECT it1.a
+FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
+a
+5
+8
+EXPLAIN
+SELECT *
+FROM t2 AS nt2, t8 AS nt4
+WHERE 1 IN (SELECT it1.a
+FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	it1	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch
+1	PRIMARY	nt2	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	it3	ALL	NULL	NULL	NULL	NULL	6	Using where; FirstMatch(nt2)
+1	PRIMARY	nt4	ALL	NULL	NULL	NULL	NULL	8	
+SELECT *
+FROM t2 AS nt2, t8 AS nt4
+WHERE 1 IN (SELECT it1.a
+FROM t1 AS it1 JOIN t6 AS it3 ON it1.a=it3.a);
+a	a
+5	1
+5	3
+5	5
+5	7
+5	9
+5	7
+5	3
+5	1
+8	1
+8	3
+8	5
+8	7
+8	9
+8	7
+8	3
+8	1
+EXPLAIN
+SELECT *
+FROM t0 AS ot1, t2 AS nt3
+WHERE ot1.a IN (SELECT it2.a
+FROM t1 AS it2 JOIN t8 AS it4 ON it2.a=it4.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	ot1	ALL	NULL	NULL	NULL	NULL	1	
+1	PRIMARY	it2	ALL	NULL	NULL	NULL	NULL	1	Using where; FirstMatch(ot1)
+1	PRIMARY	nt3	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	it4	ALL	NULL	NULL	NULL	NULL	8	Using where; FirstMatch(nt3)
+SELECT *
+FROM t0 as ot1, t2 AS nt3
+WHERE ot1.a IN (SELECT it2.a
+FROM t1 AS it2 JOIN t8 AS it4 ON it2.a=it4.a);
+a	a
+DROP TABLE t0, t1, t2, t6, t8;
+SET @@default_storage_engine=default;
+SET @@optimizer_switch=default;
+SET @@optimizer_join_cache_level=default;
+# End of bug#51457
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2010-09-20 14:06:02 +0000
@@ -5127,4 +5127,28 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result	2010-09-20 14:06:02 +0000
@@ -5131,6 +5131,30 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary; Using join buffer (BNL, incremental buffers)
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl7.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result	2010-09-20 14:06:02 +0000
@@ -5131,6 +5131,30 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Start temporary
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End temporary
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2010-09-20 14:06:02 +0000
@@ -2577,10 +2577,10 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start materialize
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
+1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End materialize
 drop table t0, t1,t2,t3;
 
 Test that MaterializeLookup strategy for semijoin,
@@ -5134,4 +5134,28 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result	2010-09-20 14:06:02 +0000
@@ -2581,10 +2581,10 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, incremental buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start materialize
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
+1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End materialize
 drop table t0, t1,t2,t3;
 
 Test that MaterializeLookup strategy for semijoin,
@@ -5138,6 +5138,30 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_mat_jcl7.result'
--- a/mysql-test/r/subquery_sj_mat_jcl7.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result	2010-09-20 14:06:02 +0000
@@ -2581,10 +2581,10 @@ explain select * 
 from t0 where a in
 (select t2.a+t3.a from t1 left join (t2 join t3) on t2.a=t1.a and t3.a=t1.a);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Start temporary
-1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Using join buffer (BNL, regular buffers)
+1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	
+1	PRIMARY	t1	index	NULL	a	5	NULL	10	Using index; Start materialize
 1	PRIMARY	t2	ref	a	a	5	test.t1.a	1	Using index
-1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End temporary
+1	PRIMARY	t3	ref	a	a	5	test.t1.a	1	Using where; Using index; End materialize
 drop table t0, t1,t2,t3;
 
 Test that MaterializeLookup strategy for semijoin,
@@ -5138,6 +5138,30 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	2	Start materialize
+1	PRIMARY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; End materialize
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2010-09-20 14:06:02 +0000
@@ -5345,4 +5345,28 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	
+2	SUBQUERY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
+2	SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2010-09-20 14:06:02 +0000
@@ -5271,4 +5271,28 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result	2010-09-20 14:06:02 +0000
@@ -5275,6 +5275,30 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2inner	ALL	NULL	NULL	NULL	NULL	2	Using join buffer (BNL, incremental buffers)
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where; Using join buffer (BNL, incremental buffers)
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'mysql-test/r/subquery_sj_none_jcl7.result'
--- a/mysql-test/r/subquery_sj_none_jcl7.result	2010-09-07 19:39:01 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result	2010-09-20 14:06:02 +0000
@@ -5275,6 +5275,30 @@ a
 1
 1
 drop table t1,t2,t3;
+#
+# Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
+#
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+CREATE TABLE t3 (a INT);
+INSERT INTO t1 VALUES(1),(1);
+INSERT INTO t2 VALUES(1),(1);
+INSERT INTO t3 VALUES(2),(2);
+explain SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2inner	ALL	NULL	NULL	NULL	NULL	2	
+2	DEPENDENT SUBQUERY	t3	ALL	NULL	NULL	NULL	NULL	2	Using where
+SELECT * FROM t1
+WHERE t1.a IN (SELECT t2.a
+FROM t2 LEFT JOIN (t2 AS t2inner, t3) ON t2.a=t3.a);
+a
+1
+1
+DROP TABLE t1,t2,t3;
 set optimizer_switch=default;
 set optimizer_join_cache_level=default;
 show variables like 'optimizer_join_cache_level';

=== modified file 'sql/item_subselect.cc'
--- a/sql/item_subselect.cc	2010-09-07 19:07:18 +0000
+++ b/sql/item_subselect.cc	2010-09-14 09:26:55 +0000
@@ -1836,12 +1836,6 @@ bool Item_in_subselect::fix_fields(THD *
     of this Item's execution. The method creates a new engine for
     materialized execution, and initializes the engine.
 
-    If this initialization fails
-    - either because it wasn't possible to create the needed temporary table
-      and its index,
-    - or because of a memory allocation error,
-    then we revert back to execution via the IN=>EXISTS tranformation.
-
     The initialization of the new engine is divided in two parts - a permanent
     one that lives across prepared statements, and one that is repeated for each
     execution.

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-09-10 05:14:00 +0000
+++ b/sql/sql_select.cc	2010-09-20 14:06:02 +0000
@@ -1278,8 +1278,8 @@ bool might_do_join_buffering(uint join_c
   */
   return (sj_tab-sj_tab->join->join_tab != sj_tab->join->const_tables && // (1)
           sj_tab->use_quick != QS_DYNAMIC_RANGE && 
-          ((join_cache_level != 0 && sj_tab->type == JT_ALL) ||
-           (join_cache_level > 4 && 
+          ((join_cache_level != 0U && sj_tab->type == JT_ALL) ||
+           (join_cache_level > 4U && 
             (sj_tab->type == JT_REF || 
              sj_tab->type == JT_EQ_REF || 
              sj_tab->type == JT_CONST))));
@@ -4219,9 +4219,6 @@ bool find_eq_ref_candidate(TABLE *table,
      - It is accessed via eq_ref(outer_tables)
 
     POSTCONDITIONS
-     * Tables that were pulled out have JOIN_TAB::emb_sj_nest == NULL
-     * Tables that were not pulled out have JOIN_TAB::emb_sj_nest pointing 
-       to semi-join nest they are in.
      * Semi-join nests' TABLE_LIST::sj_inner_tables is updated accordingly
 
     This operation is (and should be) performed at each PS execution since
@@ -4263,7 +4260,6 @@ int pull_out_semijoin_tables(JOIN *join)
     {
       if (tbl->table)
       {
-        tbl->table->reginfo.join_tab->emb_sj_nest= sj_nest;
         if (tbl->table->map & join->const_table_map)
         {
           pulled_tables |= tbl->table->map;
@@ -4323,27 +4319,18 @@ int pull_out_semijoin_tables(JOIN *join)
       arena= join->thd->activate_stmt_arena_if_needed(&backup);
       while ((tbl= child_li++))
       {
-        if (tbl->table)
+        if (tbl->table &&
+            !(inner_tables & tbl->table->map))
         {
-          if (inner_tables & tbl->table->map)
-          {
-            /* This table is not pulled out */
-            tbl->table->reginfo.join_tab->emb_sj_nest= sj_nest;
-          }
-          else
-          {
-            /* This table has been pulled out of the semi-join nest */
-            tbl->table->reginfo.join_tab->emb_sj_nest= NULL;
-            /*
-              Pull the table up in the same way as simplify_joins() does:
-              update join_list and embedding pointers but keep next[_local]
-              pointers.
-            */
-            child_li.remove();
-            upper_join_list->push_back(tbl);
-            tbl->join_list= upper_join_list;
-            tbl->embedding= sj_nest->embedding;
-          }
+          /*
+            Pull the table up in the same way as simplify_joins() does:
+            update join_list and embedding pointers but keep next[_local]
+            pointers.
+          */
+          child_li.remove();
+          upper_join_list->push_back(tbl);
+          tbl->join_list= upper_join_list;
+          tbl->embedding= sj_nest->embedding;
         }
       }
 
@@ -4920,6 +4907,29 @@ make_join_statistics(JOIN *join, TABLE_L
   if (pull_out_semijoin_tables(join))
     DBUG_RETURN(TRUE);
 
+  /*
+    Set pointer to embedding semijoin nest for all semijoined tables.
+    Note that this must be done for every table inside all semijoin nests,
+    even for tables within outer join nests embedded in semijoin nests.
+    A table can never be part of multiple semijoin nests, hence no
+    ambiguities can ever occur.
+    Note also that the pointer is not set for TABLE_LIST objects that
+    are outer join nests within semijoin nests.
+  */
+  for (s= stat; s < stat_end; s++)
+  {
+    for (TABLE_LIST *tables= s->table->pos_in_table_list;
+         tables->embedding;
+         tables= tables->embedding)
+    {
+      if (tables->embedding->sj_on_expr)
+      {
+        s->emb_sj_nest= tables->embedding;
+        break;
+      }
+    }
+  }
+
   join->join_tab=stat;
   join->map2table=stat_ref;
   join->all_tables= table_vector;
@@ -5289,9 +5299,8 @@ merge_key_fields(KEY_FIELD *start,KEY_FI
 
 static uint get_semi_join_select_list_index(Field *field)
 {
-  TABLE_LIST *emb_sj_nest;
-  if ((emb_sj_nest= field->table->pos_in_table_list->embedding) &&
-      emb_sj_nest->sj_on_expr)
+  TABLE_LIST *emb_sj_nest= field->table->pos_in_table_list->embedding;
+  if (emb_sj_nest && emb_sj_nest->sj_on_expr)
   {
     List<Item> &items= emb_sj_nest->nested_join->sj_inner_exprs;
     List_iterator<Item> it(items);
@@ -6560,7 +6569,7 @@ public:
         join->cur_sj_inner_tables == 0 &&                               // (3)
         !(remaining_tables & 
           s->emb_sj_nest->nested_join->sj_corr_tables) &&               // (4)
-        remaining_tables & s->emb_sj_nest->nested_join->sj_depends_on &&// (5)
+        (remaining_tables & s->emb_sj_nest->nested_join->sj_depends_on) &&// (5)
         join->thd->optimizer_switch_flag(OPTIMIZER_SWITCH_LOOSE_SCAN))
     {
       /* This table is an LooseScan scan candidate */
@@ -13585,9 +13594,9 @@ void optimize_wo_join_buffering(JOIN *jo
     advance_sj_state()
       join                        The join we're optimizing
       remaining_tables            Tables not in the join prefix
-      new_join_tab                Join tab we've just added to the join prefix
+      new_join_tab                Join tab that we are adding to the join prefix
       idx                         Index of this join tab (i.e. number of tables
-                                  in the prefix minus one)
+                                  in the prefix)
       current_record_count INOUT  Estimate of #records in join prefix's output
       current_read_time    INOUT  Cost to execute the join prefix
       loose_scan_pos       IN     A POSITION with LooseScan plan to access 
@@ -13634,6 +13643,8 @@ void advance_sj_state(JOIN *join, table_
 {
   TABLE_LIST *emb_sj_nest= new_join_tab->emb_sj_nest;
   POSITION *pos= join->positions + idx;
+
+  /* Add this table to the join prefix */
   remaining_tables &= ~new_join_tab->table->map;
 
   DBUG_ENTER("advance_sj_state");
@@ -13871,8 +13882,7 @@ void advance_sj_state(JOIN *join, table_
     */
     pos->sjm_scan_need_tables=
       emb_sj_nest->sj_inner_tables | 
-      emb_sj_nest->nested_join->sj_depends_on |
-      emb_sj_nest->nested_join->sj_corr_tables;
+      emb_sj_nest->nested_join->sj_depends_on;
     pos->sjm_scan_last_inner= idx;
   }
   else if (sjm_strategy == SJ_OPT_MATERIALIZE_LOOKUP)
@@ -14171,8 +14181,8 @@ static void backout_nj_sj_state(const ta
   }
 
   /* Restore the semijoin state */
-  TABLE_LIST *emb_sj_nest;
-  if ((emb_sj_nest= tab->emb_sj_nest))
+  TABLE_LIST *emb_sj_nest= tab->emb_sj_nest;
+  if (emb_sj_nest)
   {
     /* If we're removing the last SJ-inner table, remove the sj-nest */
     if ((remaining_tables & emb_sj_nest->sj_inner_tables) ==


Attachment: [text/bzr-bundle] bzr/tor.didriksen@oracle.com-20100921103249-qifwysg2k3ev1mck.bundle
Thread
• bzr push into mysql-next-mr-bugfixing branch (tor.didriksen:3296 to 3297)	Tor Didriksen	21 Sep