#At file:///home/rl136806/mysql/repo/mysql-work5/ based on revid:jorgen.loland@stripped
3244 Roy Lyseng 2010-09-16
Bug#55955: crash in MEMORY engine with IN(LEFT JOIN (JOIN))
As indicated in the "Suggested fix", the problem is that not all
tables in a semijoin nest has the emb_sj_nest pointer set.
The pointer is only set if the table is contained immediately within
the semijoin nest, but according to the documentation in sql_select.h,
it should be set for all tables within a semijoin nest.
This is also consistent, as a table can never be included in more than
one semijoin nest (semijoin nests cannot be nested).
I have also manually inspected all uses of emb_sj_nest, and it seems that
all uses are still consistent (previously there was an implicit assert
that emb_sj_nest == embedding, which no longer holds).
mysql-test/include/subquery_sj.inc
Added test case for bug#55955.
mysql-test/r/subquery_sj_all.result
Semijoin plan changed from DuplicateWeedout to Materialize.
Added test case result for bug#55955.
mysql-test/r/subquery_sj_all_jcl6.result
Semijoin plan changed from DuplicateWeedout to Materialize.
Added test case result for bug#55955.
mysql-test/r/subquery_sj_all_jcl7.result
Semijoin plan changed from DuplicateWeedout to Materialize.
Added test case result for bug#55955.
mysql-test/r/subquery_sj_dupsweed.result
Added test case result for bug#55955.
mysql-test/r/subquery_sj_dupsweed_jcl6.result
Added test case result for bug#55955.
mysql-test/r/subquery_sj_dupsweed_jcl7.result
Added test case result for bug#55955.
mysql-test/r/subquery_sj_firstmatch.result
Semijoin plan changed from DuplicateWeedout to FirstMatch.
Added test case result for bug#55955.
mysql-test/r/subquery_sj_firstmatch_jcl6.result
Semijoin plan changed from DuplicateWeedout to FirstMatch.
Added test case result for bug#55955.
mysql-test/r/subquery_sj_firstmatch_jcl7.result
Semijoin plan changed from DuplicateWeedout to FirstMatch.
Added test case result for bug#55955.
mysql-test/r/subquery_sj_loosescan.result
Added test case result for bug#55955.
mysql-test/r/subquery_sj_loosescan_jcl6.result
Added test case result for bug#55955.
mysql-test/r/subquery_sj_loosescan_jcl7.result
Added test case result for bug#55955.
mysql-test/r/subquery_sj_mat.result
Semijoin plan changed from DuplicateWeedout to Materialize.
Added test case result for bug#55955.
mysql-test/r/subquery_sj_mat_jcl6.result
Semijoin plan changed from DuplicateWeedout to Materialize.
Added test case result for bug#55955.
mysql-test/r/subquery_sj_mat_jcl7.result
Semijoin plan changed from DuplicateWeedout to Materialize.
Added test case result for bug#55955.
mysql-test/r/subquery_sj_mat_nosj.result
Added test case result for bug#55955.
mysql-test/r/subquery_sj_none.result
Added test case result for bug#55955.
mysql-test/r/subquery_sj_none_jcl6.result
Added test case result for bug#55955.
mysql-test/r/subquery_sj_none_jcl7.result
Added test case result for bug#55955.
sql/sql_select.cc
Moved setting of emb_sj_nest from pullout_semijoin_tables() to
make_join_statistics(). pullout_semijoin_tables() only went through
tables immediately contained in the semijoin nests, and the code
strictly did not belong here. It is easier to loop over all join
tabs and check whether they belong to a semijoin nest through
some nesting.
Also did a few minor cleanups, by using explicit initializers
instead of assignment to a variable inside an expression,
adding a set of parentheses to make syntax clearer, fixing some
argument documentation mistakes, and removing a redundant reference
to 'sj_corr_tables'.
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-09-07 19:39:01 +0000
+++ b/mysql-test/include/subquery_sj.inc 2010-09-16 11:16:22 +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-16 11:16:22 +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-16 11:16:22 +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-16 11:16:22 +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-16 11:16:22 +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-16 11:16:22 +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-16 11:16:22 +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-16 11:16:22 +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-14 17:21:35 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-09-16 11:16:22 +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,
@@ -5128,6 +5128,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_jcl7.result'
--- a/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-09-14 17:21:35 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-09-16 11:16:22 +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,
@@ -5128,6 +5128,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_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-16 11:16:22 +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-16 11:16:22 +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-16 11:16:22 +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-16 11:16:22 +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-16 11:16:22 +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-16 11:16:22 +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-16 11:16:22 +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-16 11:16:22 +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-16 11:16:22 +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-16 11:16:22 +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/sql_select.cc'
--- a/sql/sql_select.cc 2010-09-15 08:53:08 +0000
+++ b/sql/sql_select.cc 2010-09-16 11:16:22 +0000
@@ -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;
@@ -4325,15 +4321,8 @@ int pull_out_semijoin_tables(JOIN *join)
{
if (tbl->table)
{
- if (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]
@@ -4920,6 +4909,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++)
+ {
+ TABLE_LIST *tables= s->table->pos_in_table_list;
+ while (tables->embedding)
+ {
+ if (tables->embedding->sj_on_expr)
+ {
+ s->emb_sj_nest= tables->embedding;
+ break;
+ }
+ tables= tables->embedding;
+ }
+ }
+
join->join_tab=stat;
join->map2table=stat_ref;
join->all_tables= table_vector;
@@ -5289,9 +5301,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 +6571,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 +13596,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 +13645,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 +13884,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 +14183,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/roy.lyseng@oracle.com-20100916111622-ugv2s9bwqpk0q7b3.bundle
