3511 Roy Lyseng 2012-01-10
Bug#13545215: Missing rows on nested in-subquery with materialization
This problem occurs with a semi-join materialization that is executed
multiple times. In this case, the problem is that some outer-join data
item (the last_inner field of the join_tab) is not reset after the
first materialization. Effectively, this means that the first
materialization is performed as an outer join, and all subsequent
materializations are performed as inner joins.
The problem occurs only with the MaterializeScan semi-join strategy.
Another demonstration of why reusing data structures for another purpose
is a really stupid idea.
mysql-test/include/subquery_sj.inc
Added test case for bug#13545215.
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
Added test case results for bug#13545215.
Notice also the correction in some previous test runs for
the test case for bug#12603183.
sql/sql_executor.cc
In sub_select_sjm(), save the value of last_inner before
performing scan over materialized table, and restore the value
after the scan.
modified:
mysql-test/include/subquery_sj.inc
mysql-test/r/subquery_sj_all.result
mysql-test/r/subquery_sj_all_bka.result
mysql-test/r/subquery_sj_all_bka_nixbnl.result
mysql-test/r/subquery_sj_all_bkaunique.result
mysql-test/r/subquery_sj_dupsweed.result
mysql-test/r/subquery_sj_dupsweed_bka.result
mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result
mysql-test/r/subquery_sj_dupsweed_bkaunique.result
mysql-test/r/subquery_sj_firstmatch.result
mysql-test/r/subquery_sj_firstmatch_bka.result
mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result
mysql-test/r/subquery_sj_firstmatch_bkaunique.result
mysql-test/r/subquery_sj_loosescan.result
mysql-test/r/subquery_sj_loosescan_bka.result
mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result
mysql-test/r/subquery_sj_loosescan_bkaunique.result
mysql-test/r/subquery_sj_mat.result
mysql-test/r/subquery_sj_mat_bka.result
mysql-test/r/subquery_sj_mat_bka_nixbnl.result
mysql-test/r/subquery_sj_mat_bkaunique.result
mysql-test/r/subquery_sj_mat_nosj.result
mysql-test/r/subquery_sj_none.result
mysql-test/r/subquery_sj_none_bka.result
mysql-test/r/subquery_sj_none_bka_nixbnl.result
mysql-test/r/subquery_sj_none_bkaunique.result
sql/sql_executor.cc
3510 Roy Lyseng 2012-01-09
Bug#13552968: Extra row with materialization on join + subquery in where +
The problem here occurs with an outer join between two "system" tables
(tables with exactly one row each), and with an "expensive" join
condition between the two tables. User-defined functions and materialized
subqueries are considered expensive in this context.
The fix for bug#52344 is related to this problem, as it moved subquery
materialization out of the optimizer phase and into the execution phase.
However, when marking the inner table of the outer join as "const",
it is not possible to later evaluate the join condition and subsequently
mark the fields from the inner tables as NULL. This is contrary to the
condition attached to the JOIN object: If there are only const tables
in the JOIN, do_select() will evaluate this condition and correctly
return an empty result if the condition evaluates to FALSE.
The cases for where the bug hit were quite interesting:
- With semijoin and materialization ON, the subquery is first marked
as semi-join candidate, but because the outer query is OUTER JOIN,
it is instead reverted to IN-TO-EXISTS. This is marked non-expensive
and thus does not pose a problem.
- With semijoin OFF, materialization is selected. The condition is then
marked as expensive and the problem occurs.
- With STRAIGHT JOIN applied to the outer query, materialization is
always selected, and the problem occurs.
The chosen solution is to stop marking the inner table of an outer join
that has an expensive join condition as a const table.
Fixing the executor to handle this particular case does not seem viable.
mysql-test/include/subquery_mat.inc
Added test case for bug#13552968.
Modified test case for bug#52344 so that problem is visible here as well.
mysql-test/r/subquery_mat.result
mysql-test/r/subquery_mat_all.result
mysql-test/r/subquery_mat_none.result
Added test case results for bug#13552968.
sql/sql_executor.cc
Ensure that join_read_const_table() is not called with an outer-joined
table with an expensive condition.
sql/sql_optimizer.cc
For the inner table of an outer join that has an expensive condition,
do not mark a table containing one row as const.
modified:
mysql-test/include/subquery_mat.inc
mysql-test/r/subquery_mat.result
mysql-test/r/subquery_mat_all.result
mysql-test/r/subquery_mat_none.result
sql/sql_executor.cc
sql/sql_optimizer.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2011-12-13 08:49:11 +0000
+++ b/mysql-test/include/subquery_sj.inc 2012-01-10 08:49:18 +0000
@@ -3910,9 +3910,6 @@ WHERE (grandparent1.col_varchar_key) IN
eval EXPLAIN $query;
eval $query;
---echo -- Notice that Materialize-scan algorithm reports wrong result for this query.
---echo -- This problem will be filed as a separate bug and dealt with in WL#5561.
-
DROP TABLE t1, t2;
--echo # End of test for bug#12603183.
@@ -4575,4 +4572,58 @@ DROP TABLE t1, t2;
--echo # End of test for bug#13414014.
+--echo #
+--echo # Bug#13545215: Missing rows on nested in-subquery with materialization
+--echo #
+
+CREATE TABLE t1 (
+ col_int_key int,
+ col_varchar_key varchar(1),
+ col_varchar_nokey varchar(1),
+ KEY col_int_key (col_int_key),
+ KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+
+INSERT INTO t1 VALUES
+ (8,'x','x'), (0,'p','p'), (8,'c','c');
+
+CREATE TABLE t2 (
+ pk int NOT NULL,
+ col_varchar_key varchar(1),
+ col_varchar_nokey varchar(1),
+ PRIMARY KEY (pk),
+ KEY col_varchar_key (col_varchar_key)
+);
+
+INSERT INTO t2 VALUES
+ (1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+ (5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+
+CREATE TABLE t3 (
+ col_int_nokey int
+);
+
+INSERT INTO t3 VALUES (7);
+
+let $query=
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+ SELECT col_varchar_nokey
+ FROM t2 AS parent1
+ WHERE col_varchar_key IN (
+ SELECT child1.col_varchar_nokey
+ FROM t2 AS child1 LEFT JOIN t3 AS child2
+ ON child1.pk < child2.col_int_nokey
+ )
+ );
+
+eval explain $query;
+--sorted_result
+eval $query;
+
+DROP TABLE t1, t2, t3;
+
+--echo # End of test for bug#13545215.
+
--echo # End of 5.6 tests
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2012-01-10 08:49:18 +0000
@@ -6595,6 +6595,7 @@ col_varchar_nokey
i
h
q
+a
v
u
s
@@ -6606,8 +6607,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7270,5 +7269,67 @@ x
q
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Start materialize; Scan
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; End materialize; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 8 Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2012-01-10 08:49:18 +0000
@@ -6596,6 +6596,7 @@ col_varchar_nokey
i
h
q
+a
v
u
s
@@ -6607,8 +6608,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7271,6 +7270,68 @@ x
q
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Start materialize; Scan
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; End materialize; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 8 Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-01-10 08:49:18 +0000
@@ -6596,6 +6596,7 @@ col_varchar_nokey
i
h
q
+a
v
u
s
@@ -6607,8 +6608,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7271,6 +7270,68 @@ q
x
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 ref col_int_key col_int_key 5 test.grandparent1.col_int_key 2 Using index
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Using where; Start materialize; Scan
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; End materialize
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where; Using join buffer (Batched Key Access)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2012-01-10 08:49:18 +0000
@@ -6597,6 +6597,7 @@ col_varchar_nokey
i
h
q
+a
v
u
s
@@ -6608,8 +6609,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7272,6 +7271,68 @@ x
q
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Start materialize; Scan
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; End materialize; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 8 Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2012-01-10 08:49:18 +0000
@@ -6606,8 +6606,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7270,5 +7268,67 @@ q
x
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 8 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-01-10 08:49:18 +0000
@@ -6607,8 +6607,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7271,6 +7269,68 @@ q
x
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 8 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-01-10 08:49:18 +0000
@@ -6607,8 +6607,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7271,6 +7269,68 @@ x
q
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 ref col_int_key col_int_key 5 test.grandparent1.col_int_key 2 Using index
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Using where; Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where; End temporary
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-01-10 08:49:18 +0000
@@ -6608,8 +6608,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7272,6 +7270,68 @@ q
x
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 8 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2012-01-10 08:49:18 +0000
@@ -6607,8 +6607,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7271,6 +7269,68 @@ x
q
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 8 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-01-10 08:49:18 +0000
@@ -6608,8 +6608,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7272,6 +7270,68 @@ x
q
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 8 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-01-10 08:49:18 +0000
@@ -6608,8 +6608,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7272,6 +7270,68 @@ x
q
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 ref col_int_key col_int_key 5 test.grandparent1.col_int_key 2 Using index
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Using where; Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where; End temporary
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-01-10 08:49:18 +0000
@@ -6609,8 +6609,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7273,6 +7271,68 @@ x
q
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 8 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
#
# Bug#51457 Firstmatch semijoin strategy gives wrong results for
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2012-01-10 08:49:18 +0000
@@ -6607,8 +6607,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7271,5 +7269,67 @@ q
x
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 8 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2012-01-10 08:49:18 +0000
@@ -6608,8 +6608,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7272,6 +7270,68 @@ q
x
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 8 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-01-10 08:49:18 +0000
@@ -6608,8 +6608,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7272,6 +7270,68 @@ x
q
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 ref col_int_key col_int_key 5 test.grandparent1.col_int_key 2 Using index
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Using where; Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where; End temporary
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-01-10 08:49:18 +0000
@@ -6609,8 +6609,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7273,6 +7271,68 @@ q
x
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Start temporary
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 8 Using where; End temporary; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2012-01-10 08:49:18 +0000
@@ -6595,6 +6595,7 @@ col_varchar_nokey
i
h
q
+a
v
u
s
@@ -6606,8 +6607,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7270,5 +7269,67 @@ q
x
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Start materialize; Scan
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; End materialize; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 8 Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2012-01-10 08:49:18 +0000
@@ -6596,6 +6596,7 @@ col_varchar_nokey
i
h
q
+a
v
u
s
@@ -6607,8 +6608,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7271,6 +7270,68 @@ q
x
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Start materialize; Scan
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; End materialize; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 8 Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-01-10 08:49:18 +0000
@@ -6596,6 +6596,7 @@ col_varchar_nokey
i
h
q
+a
v
u
s
@@ -6607,8 +6608,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7271,6 +7270,68 @@ q
x
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 ref col_int_key col_int_key 5 test.grandparent1.col_int_key 2 Using index
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Using where; Start materialize; Scan
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; End materialize
+2 DEPENDENT SUBQUERY parent1 ref col_varchar_key col_varchar_key 4 test.child1.col_varchar_nokey 2 Using where
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-01-10 08:49:18 +0000
@@ -6597,6 +6597,7 @@ col_varchar_nokey
i
h
q
+a
v
u
s
@@ -6608,8 +6609,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7272,6 +7271,68 @@ q
x
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Start materialize; Scan
+2 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; End materialize; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL col_varchar_key NULL NULL NULL 8 Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2012-01-10 08:49:18 +0000
@@ -6683,8 +6683,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7347,5 +7345,67 @@ q
x
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 SUBQUERY parent1 ALL NULL NULL NULL NULL 8 Using where
+3 SUBQUERY child1 ALL NULL NULL NULL NULL 8
+3 SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2012-01-10 08:49:18 +0000
@@ -6618,8 +6618,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7282,5 +7280,67 @@ q
x
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 8 Using where
+3 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Using where
+3 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2012-01-10 08:49:18 +0000
@@ -6619,8 +6619,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7283,6 +7281,68 @@ q
x
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 8 Using where
+3 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Using where
+3 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-01-10 08:49:18 +0000
@@ -6619,8 +6619,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7283,6 +7281,68 @@ q
x
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 ref col_int_key col_int_key 5 test.grandparent1.col_int_key 2 Using index
+2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 8 Using where
+3 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Using where
+3 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result 2011-12-13 08:49:11 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2012-01-10 08:49:18 +0000
@@ -6620,8 +6620,6 @@ e
i
y
w
--- Notice that Materialize-scan algorithm reports wrong result for this query.
--- This problem will be filed as a separate bug and dealt with in WL#5561.
DROP TABLE t1, t2;
# End of test for bug#12603183.
#
@@ -7284,6 +7282,68 @@ q
x
DROP TABLE t1, t2;
# End of test for bug#13414014.
+#
+# Bug#13545215: Missing rows on nested in-subquery with materialization
+#
+CREATE TABLE t1 (
+col_int_key int,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+KEY col_int_key (col_int_key),
+KEY col_varchar_key (col_varchar_key,col_int_key)
+) ;
+INSERT INTO t1 VALUES
+(8,'x','x'), (0,'p','p'), (8,'c','c');
+CREATE TABLE t2 (
+pk int NOT NULL,
+col_varchar_key varchar(1),
+col_varchar_nokey varchar(1),
+PRIMARY KEY (pk),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t2 VALUES
+(1,'v','v'), (2,'v','v'), (3,'c','c'), (4,NULL,NULL),
+(5,'x','x'), (6,'i','i'), (7,'e','e'), (8,'p','p');
+CREATE TABLE t3 (
+col_int_nokey int
+);
+INSERT INTO t3 VALUES (7);
+explain SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY grandparent1 ALL col_int_key NULL NULL NULL 3 Using where
+1 PRIMARY grandparent2 index col_int_key col_int_key 5 NULL 3 Using where; Using index; Using join buffer (Block Nested Loop)
+2 DEPENDENT SUBQUERY parent1 ALL NULL NULL NULL NULL 8 Using where
+3 DEPENDENT SUBQUERY child1 ALL NULL NULL NULL NULL 8 Using where
+3 DEPENDENT SUBQUERY child2 ALL NULL NULL NULL NULL 1 Using where; Using join buffer (Block Nested Loop)
+SELECT grandparent1.col_varchar_nokey
+FROM t1 AS grandparent1 JOIN t1 AS grandparent2 USING (col_int_key)
+WHERE grandparent1.col_varchar_key IN (
+SELECT col_varchar_nokey
+FROM t2 AS parent1
+WHERE col_varchar_key IN (
+SELECT child1.col_varchar_nokey
+FROM t2 AS child1 LEFT JOIN t3 AS child2
+ON child1.pk < child2.col_int_nokey
+)
+);
+col_varchar_nokey
+c
+c
+p
+x
+x
+DROP TABLE t1, t2, t3;
+# End of test for bug#13545215.
# End of 5.6 tests
set optimizer_switch=default;
set optimizer_switch=default;
=== modified file 'sql/sql_executor.cc'
--- a/sql/sql_executor.cc 2012-01-09 14:19:49 +0000
+++ b/sql/sql_executor.cc 2012-01-10 08:49:18 +0000
@@ -1751,6 +1751,7 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
// Save contents of join tab for possible repeated materializations:
const READ_RECORD saved_access= last_tab->read_record;
const READ_RECORD::Setup_func saved_rfr= last_tab->read_first_record;
+ st_join_table *const saved_last_inner= last_tab->last_inner;
// Initialize full scan
if (init_read_record(&last_tab->read_record, join->thd,
@@ -1777,6 +1778,7 @@ sub_select_sjm(JOIN *join, JOIN_TAB *joi
last_tab->set_condition(save_cond, __LINE__);
last_tab->read_record= saved_access;
last_tab->read_first_record= saved_rfr;
+ last_tab->last_inner= saved_last_inner;
}
else
{
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3510 to 3511) Bug#13545215 | Roy Lyseng | 10 Jan |
