#At file:///home/rl136806/mysql/repo/mysql-work5/ based on revid:roy.lyseng@stripped
3270 Roy Lyseng 2010-10-29
Bug#52068: Optimizer generates invalid semijoin materialization plan
When MaterializeScan semijoin strategy was used and there were one
or more outer dependent tables before the semijoin tables, the scan
over the materialized table was not properly reset for each row of
the prefix outer tables.
Example: suppose we have a join order:
ot1 SJ-Mat-Scan(it2 it3) ot4
Notice that this is called a MaterializeScan, even though there is an
outer table ahead of the materialized tables. Usually a MaterializeScan
has the outer tables after the materialized table, but this is
a special (but legal) case with outer dependent tables both before and
after the materialized table.
For each qualifying row from ot1, a new scan over the materialized
table must be set up. The code failed to do that, so all scans after
the first one returned zero rows from the materialized table.
mysql-test/include/subquery_sj.inc
Bug#52068: Optimizer generates invalid semijoin materialization plan
Added new test.
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
Bug#52068: Optimizer generates invalid semijoin materialization plan
Earlier wrong semijoin materialization test results are corrected.
sql/sql_select.cc
Bug#52068: Optimizer generates invalid semijoin materialization plan
In sub_select_sjm(), added code that resets the cursor over the
materialized table when more than one scan is needed.
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-10-26 10:43:50 +0000
+++ b/mysql-test/include/subquery_sj.inc 2010-10-29 14:56:12 +0000
@@ -3433,3 +3433,28 @@ eval explain $query;
DROP TABLE t1,t2,t3;
--echo # End of the test for bug#50019.
+
+--echo #
+--echo # Bug#52068: Optimizer generates invalid semijoin materialization plan
+--echo #
+
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+
+let $query=
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+ FROM it2,it3);
+
+eval $query;
+eval explain $query;
+
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+
+--echo # End of the test for bug#52068.
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2010-10-29 14:56:12 +0000
@@ -4926,6 +4926,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4934,6 +4935,7 @@ Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
pk
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4953,6 +4955,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4962,6 +4965,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4971,6 +4975,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4990,6 +4995,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4999,6 +5005,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
pk
1
+2
DROP TABLE t1, t2;
# End of Bug#48213
#
@@ -5265,4 +5272,39 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End materialize
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Start materialize; Scan
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 End materialize
+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.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_jcl6.result'
--- a/mysql-test/r/subquery_sj_all_jcl6.result 2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl6.result 2010-10-29 14:56:12 +0000
@@ -4930,6 +4930,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4938,6 +4939,7 @@ Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
pk
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4957,6 +4959,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4966,6 +4969,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4975,6 +4979,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4994,6 +4999,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -5003,6 +5009,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
pk
1
+2
DROP TABLE t1, t2;
# End of Bug#48213
#
@@ -5269,6 +5276,41 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End materialize
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Start materialize; Scan
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 End materialize
+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.
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-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_all_jcl7.result 2010-10-29 14:56:12 +0000
@@ -4930,6 +4930,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4938,6 +4939,7 @@ Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
pk
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4957,6 +4959,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4966,6 +4969,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4975,6 +4979,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4994,6 +4999,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -5003,6 +5009,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
pk
1
+2
DROP TABLE t1, t2;
# End of Bug#48213
#
@@ -5269,6 +5276,41 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End materialize
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Start materialize; Scan
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 End materialize
+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.
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-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2010-10-29 14:56:12 +0000
@@ -5271,4 +5271,39 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (BNL, regular buffers)
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, regular buffers)
+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.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_jcl6.result'
--- a/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl6.result 2010-10-29 14:56:12 +0000
@@ -5275,6 +5275,41 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (BNL, incremental buffers)
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, incremental buffers)
+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.
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-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_jcl7.result 2010-10-29 14:56:12 +0000
@@ -5275,6 +5275,41 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (BNL, regular buffers)
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, regular buffers)
+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.
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-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2010-10-29 14:56:12 +0000
@@ -5273,6 +5273,41 @@ id select_type table type possible_keys
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, regular buffers)
+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#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-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl6.result 2010-10-29 14:56:12 +0000
@@ -5277,6 +5277,41 @@ id select_type table type possible_keys
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, incremental buffers)
+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#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-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_jcl7.result 2010-10-29 14:56:12 +0000
@@ -5277,6 +5277,41 @@ id select_type table type possible_keys
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, regular buffers)
+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#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-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2010-10-29 14:56:12 +0000
@@ -5276,4 +5276,39 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (BNL, regular buffers)
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, regular buffers)
+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.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_jcl6.result'
--- a/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl6.result 2010-10-29 14:56:12 +0000
@@ -5280,6 +5280,41 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (BNL, incremental buffers)
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, incremental buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, incremental buffers)
+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.
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-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_jcl7.result 2010-10-29 14:56:12 +0000
@@ -5280,6 +5280,41 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End temporary; Using join buffer (BNL, regular buffers)
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2 Start temporary
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Using where; Using join buffer (BNL, regular buffers)
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, regular buffers)
+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.
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-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2010-10-29 14:56:12 +0000
@@ -2651,6 +2651,8 @@ select left(a1,7), left(a2,7)
from t1_16
where a1 in (select b1 from t2_16 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_16
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
@@ -2663,6 +2665,8 @@ select left(a1,7), left(a2,7)
from t1_16
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_16
where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
@@ -2775,6 +2779,8 @@ select left(a1,7), left(a2,7)
from t1_512
where a1 in (select b1 from t2_512 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_512
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
@@ -2787,6 +2793,8 @@ select left(a1,7), left(a2,7)
from t1_512
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_512
where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
@@ -2864,6 +2872,8 @@ select left(a1,7), left(a2,7)
from t1_513
where a1 in (select b1 from t2_513 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_513
where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
@@ -2876,6 +2886,8 @@ select left(a1,7), left(a2,7)
from t1_513
where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_513
where a1 in (select substring(b1,1,513) from t2_513 where b1 > '0');
@@ -2940,6 +2952,8 @@ select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select b1 from t2_1024 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1024
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
@@ -2952,6 +2966,8 @@ select left(a1,7), left(a2,7)
from t1_1024
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
@@ -3029,6 +3045,8 @@ select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select b1 from t2_1025 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1025
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
@@ -3041,6 +3059,8 @@ select left(a1,7), left(a2,7)
from t1_1025
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
@@ -4906,6 +4926,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4914,6 +4935,7 @@ Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
pk
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Start temporary
@@ -4933,6 +4955,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4942,6 +4965,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4951,6 +4975,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Start temporary
@@ -4970,6 +4995,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4979,6 +5005,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
pk
1
+2
DROP TABLE t1, t2;
# End of Bug#48213
#
@@ -5245,4 +5272,39 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End materialize
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Start materialize; Scan
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 End materialize
+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.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_jcl6.result'
--- a/mysql-test/r/subquery_sj_mat_jcl6.result 2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl6.result 2010-10-29 14:56:12 +0000
@@ -2655,6 +2655,8 @@ select left(a1,7), left(a2,7)
from t1_16
where a1 in (select b1 from t2_16 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_16
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
@@ -2667,6 +2669,8 @@ select left(a1,7), left(a2,7)
from t1_16
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_16
where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
@@ -2779,6 +2783,8 @@ select left(a1,7), left(a2,7)
from t1_512
where a1 in (select b1 from t2_512 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_512
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
@@ -2791,6 +2797,8 @@ select left(a1,7), left(a2,7)
from t1_512
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_512
where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
@@ -2868,6 +2876,8 @@ select left(a1,7), left(a2,7)
from t1_513
where a1 in (select b1 from t2_513 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_513
where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
@@ -2880,6 +2890,8 @@ select left(a1,7), left(a2,7)
from t1_513
where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_513
where a1 in (select substring(b1,1,513) from t2_513 where b1 > '0');
@@ -2944,6 +2956,8 @@ select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select b1 from t2_1024 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1024
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
@@ -2956,6 +2970,8 @@ select left(a1,7), left(a2,7)
from t1_1024
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
@@ -3033,6 +3049,8 @@ select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select b1 from t2_1025 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1025
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
@@ -3045,6 +3063,8 @@ select left(a1,7), left(a2,7)
from t1_1025
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
@@ -4910,6 +4930,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4918,6 +4939,7 @@ Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
pk
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Start temporary
@@ -4937,6 +4959,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4946,6 +4969,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4955,6 +4979,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Start temporary
@@ -4974,6 +4999,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4983,6 +5009,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
pk
1
+2
DROP TABLE t1, t2;
# End of Bug#48213
#
@@ -5249,6 +5276,41 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End materialize
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Start materialize; Scan
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 End materialize
+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.
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-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_mat_jcl7.result 2010-10-29 14:56:12 +0000
@@ -2655,6 +2655,8 @@ select left(a1,7), left(a2,7)
from t1_16
where a1 in (select b1 from t2_16 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_16
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
@@ -2667,6 +2669,8 @@ select left(a1,7), left(a2,7)
from t1_16
where (a1,a2) in (select b1, b2 from t2_16 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_16
where a1 in (select substring(b1,1,16) from t2_16 where b1 > '0');
@@ -2779,6 +2783,8 @@ select left(a1,7), left(a2,7)
from t1_512
where a1 in (select b1 from t2_512 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_512
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
@@ -2791,6 +2797,8 @@ select left(a1,7), left(a2,7)
from t1_512
where (a1,a2) in (select b1, b2 from t2_512 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_512
where a1 in (select substring(b1,1,512) from t2_512 where b1 > '0');
@@ -2868,6 +2876,8 @@ select left(a1,7), left(a2,7)
from t1_513
where a1 in (select b1 from t2_513 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_513
where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
@@ -2880,6 +2890,8 @@ select left(a1,7), left(a2,7)
from t1_513
where (a1,a2) in (select b1, b2 from t2_513 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_513
where a1 in (select substring(b1,1,513) from t2_513 where b1 > '0');
@@ -2944,6 +2956,8 @@ select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select b1 from t2_1024 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1024
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
@@ -2956,6 +2970,8 @@ select left(a1,7), left(a2,7)
from t1_1024
where (a1,a2) in (select b1, b2 from t2_1024 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1024
where a1 in (select substring(b1,1,1024) from t2_1024 where b1 > '0');
@@ -3033,6 +3049,8 @@ select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select b1 from t2_1025 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1025
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
@@ -3045,6 +3063,8 @@ select left(a1,7), left(a2,7)
from t1_1025
where (a1,a2) in (select b1, b2 from t2_1025 where b1 > '0');
left(a1,7) left(a2,7)
+1 - 01x 2 - 01x
+1 - 02x 2 - 02x
explain extended select left(a1,7), left(a2,7)
from t1_1025
where a1 in (select substring(b1,1,1025) from t2_1025 where b1 > '0');
@@ -4910,6 +4930,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, c) IN (SELECT b, c FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4918,6 +4939,7 @@ Warnings:
Note 1003 select `test`.`t1`.`pk` AS `pk` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`d` = `test`.`t1`.`d`) and (`test`.`t2`.`b` = `test`.`t1`.`b`) and (`test`.`t2`.`pk` > 0))
SELECT pk FROM t1 WHERE (b, d) IN (SELECT b, d FROM t2 WHERE pk > 0);
pk
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, e) IN (SELECT b, e FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Start temporary
@@ -4937,6 +4959,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, f) IN (SELECT b, f FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4946,6 +4969,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, g) IN (SELECT b, g FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4955,6 +4979,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, h) IN (SELECT b, h FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, i) IN (SELECT b, i FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Start temporary
@@ -4974,6 +4999,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, j) IN (SELECT b, j FROM t2 WHERE pk > 0);
pk
1
+2
EXPLAIN EXTENDED SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00
@@ -4983,6 +5009,7 @@ Note 1003 select `test`.`t1`.`pk` AS `pk
SELECT pk FROM t1 WHERE (b, k) IN (SELECT b, k FROM t2 WHERE pk > 0);
pk
1
+2
DROP TABLE t1, t2;
# End of Bug#48213
#
@@ -5249,6 +5276,41 @@ id select_type table type possible_keys
1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; End materialize
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY it2 ALL NULL NULL NULL NULL 4 Start materialize; Scan
+1 PRIMARY it3 ALL NULL NULL NULL NULL 6 End materialize
+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.
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-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2010-10-29 14:56:12 +0000
@@ -5494,4 +5494,39 @@ id select_type table type possible_keys
2 SUBQUERY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
+2 SUBQUERY it2 ALL NULL NULL NULL NULL 4
+2 SUBQUERY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, regular buffers)
+DROP TABLE IF EXISTS ot1, ot4, it2, it3;
+# End of the test for bug#52068.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2010-10-29 14:56:12 +0000
@@ -5420,4 +5420,39 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 4 Using where
+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.
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_jcl6.result'
--- a/mysql-test/r/subquery_sj_none_jcl6.result 2010-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl6.result 2010-10-29 14:56:12 +0000
@@ -5424,6 +5424,41 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, incremental buffers)
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, incremental buffers)
+2 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 4 Using where
+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.
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-10-26 10:43:50 +0000
+++ b/mysql-test/r/subquery_sj_none_jcl7.result 2010-10-29 14:56:12 +0000
@@ -5424,6 +5424,41 @@ id select_type table type possible_keys
2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
DROP TABLE t1,t2,t3;
# End of the test for bug#50019.
+#
+# Bug#52068: Optimizer generates invalid semijoin materialization plan
+#
+CREATE TABLE ot1(a INTEGER);
+INSERT INTO ot1 VALUES(5), (8);
+CREATE TABLE it2(a INTEGER);
+INSERT INTO it2 VALUES(9), (5), (1), (8);
+CREATE TABLE it3(a INTEGER);
+INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
+CREATE TABLE ot4(a INTEGER);
+INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
+SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+a a
+5 1
+8 1
+5 5
+8 5
+5 7
+8 7
+5 7
+8 7
+5 1
+8 1
+explain SELECT * FROM ot1,ot4
+WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
+FROM it2,it3);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
+1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
+2 DEPENDENT SUBQUERY it2 ALL NULL NULL NULL NULL 4 Using where
+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.
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-10-26 10:43:50 +0000
+++ b/sql/sql_select.cc 2010-10-29 14:56:12 +0000
@@ -16970,6 +16970,15 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
last_tab->read_record.read_record= rr_sequential_and_unpack;
}
}
+ else
+ {
+ if (sjm->is_scan)
+ {
+ /* Reset the cursor for a new scan over the table */
+ if (sjm->table->file->ha_rnd_init(TRUE))
+ DBUG_RETURN(NESTED_LOOP_ERROR);
+ }
+ }
if (sjm->is_scan)
{
Attachment: [text/bzr-bundle] bzr/roy.lyseng@oracle.com-20101029145612-1qid9j82e2qtqkqg.bundle
