3917 Roy Lyseng 2012-05-29
Bug#14064201: Missing data on join of derived table + WHERE .. IN with two ops
The fix for bug#13414014 and the first fix for bug#14064201 improved
the situation for these kinds of queries, but there were still problems.
The second test case for bug#14064201 generates an equality for an outer
join condition for the two columns from the materialized semi-joined
tables, but only the first of these are available when processing
the condition.
We have this multiple equality:
=(gp1.col_int_nokey, t2.col_int_nokey, t2.col_int_key, t1.col_int_nokey)
gp1 is the first table of the join plan, t2 is the second and t1 is the
third. t2 is also the materialized semi-join table.
When trying to generate an equality for t2.col_int_key, which is
found to match t2.col_int_nokey, only the first column of the multiple
equality coming from the inner table (t2) is available during processing
of the outer join condition. Thus, we have to eliminate all columns
following the first. We do that by adding the check
item_equal->get_subst_item(item_field) == item_field
when testing for fields to be included in eliminate_item_equal().
mysql-test/include/subquery_sj.inc
Added test case for bug#14064201.
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#14064201.
sql/sql_optimizer.cc
In eliminate_item_equal(), an if test was enhanced with a check
for the column in question being the first coming from the
materialized semi-join tables.
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_optimizer.cc
3916 Roy Lyseng 2012-05-29
Bug#13980954: Missing data on left join + null value + where..in
Followup patch after review.
modified:
sql/sql_optimizer.cc
sql/sql_select.h
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc 2012-05-23 11:12:19 +0000
+++ b/mysql-test/include/subquery_sj.inc 2012-05-29 13:24:49 +0000
@@ -6229,6 +6229,40 @@ eval $query;
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+ col_int_nokey int NOT NULL,
+ col_int_key int NOT NULL,
+ KEY col_int_key (col_int_key)
+);
+
+INSERT INTO t1 VALUES
+ (1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+ (1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+
+CREATE TABLE t2 (
+ col_int_nokey int NOT NULL,
+ col_int_key int NOT NULL,
+ KEY col_int_key (col_int_key)
+);
+
+INSERT INTO t2 VALUES
+ (4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+ (1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+
+let $query=
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+ LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+ (SELECT col_int_nokey, col_int_key
+ FROM t2
+ );
+
+eval explain format=json $query;
+eval $query;
+
+DROP TABLE t1, t2;
+
--echo # End of test for bug#14064201.
--echo # End of 5.6 tests
=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_all.result 2012-05-29 13:24:49 +0000
@@ -9936,6 +9936,100 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "using_temporary_table": true,
+ "access_type": "eq_ref",
+ "key": "<auto_key>",
+ "rows": 1,
+ "materialized_from_subquery": {
+ "query_block": {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ALL",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "(`test`.`t2`.`col_int_key` = `test`.`t2`.`col_int_nokey`)"
+ }
+ }
+ }
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on(((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`))) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`t2`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# 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 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result 2012-05-29 13:24:49 +0000
@@ -9941,6 +9941,100 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "using_temporary_table": true,
+ "access_type": "eq_ref",
+ "key": "<auto_key>",
+ "rows": 1,
+ "materialized_from_subquery": {
+ "query_block": {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ALL",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "(`test`.`t2`.`col_int_key` = `test`.`t2`.`col_int_nokey`)"
+ }
+ }
+ }
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on(((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`))) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`t2`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
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 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-05-29 13:24:49 +0000
@@ -9940,6 +9940,99 @@ y y
y y
y y
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "using_temporary_table": true,
+ "access_type": "eq_ref",
+ "key": "<auto_key>",
+ "rows": 1,
+ "materialized_from_subquery": {
+ "query_block": {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ALL",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "(`test`.`t2`.`col_int_key` = `test`.`t2`.`col_int_nokey`)"
+ }
+ }
+ }
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on(((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`))) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`t2`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 7
+7 0
+7 0
+7 3
+7 3
+1 6
+1 6
+8 5
+8 5
+8 1
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2012-05-29 13:24:49 +0000
@@ -9942,6 +9942,100 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "using_temporary_table": true,
+ "access_type": "eq_ref",
+ "key": "<auto_key>",
+ "rows": 1,
+ "materialized_from_subquery": {
+ "query_block": {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ALL",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "(`test`.`t2`.`col_int_key` = `test`.`t2`.`col_int_nokey`)"
+ }
+ }
+ }
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on(((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`))) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`t2`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result 2012-05-29 13:24:49 +0000
@@ -9918,6 +9918,98 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "duplicates_removal": {
+ "using_temporary_table": true,
+ "table": {
+ "table_name": "t2",
+ "access_type": "ref",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "key": "col_int_key",
+ "key_length": "4",
+ "ref": [
+ "test.grandparent1.col_int_nokey"
+ ],
+ "rows": 2,
+ "filtered": 100,
+ "attached_condition": "(`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)"
+ }
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# 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 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-05-29 13:24:49 +0000
@@ -9919,6 +9919,102 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "duplicates_removal": {
+ "using_temporary_table": true,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ref",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "key": "col_int_key",
+ "key_length": "4",
+ "ref": [
+ "test.grandparent1.col_int_nokey"
+ ],
+ "rows": 2,
+ "filtered": 100,
+ "attached_condition": "(`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)"
+ }
+ }
+ ]
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
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 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-05-29 13:24:49 +0000
@@ -9920,6 +9920,101 @@ y y
y y
y y
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "duplicates_removal": {
+ "using_temporary_table": true,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ref",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "key": "col_int_key",
+ "key_length": "4",
+ "ref": [
+ "test.grandparent1.col_int_nokey"
+ ],
+ "rows": 2,
+ "filtered": 100,
+ "attached_condition": "(`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)"
+ }
+ }
+ ]
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 7
+7 0
+7 0
+7 3
+7 3
+1 6
+1 6
+8 5
+8 5
+8 1
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-05-29 13:24:49 +0000
@@ -9920,6 +9920,102 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "duplicates_removal": {
+ "using_temporary_table": true,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ref",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "key": "col_int_key",
+ "key_length": "4",
+ "ref": [
+ "test.grandparent1.col_int_nokey"
+ ],
+ "rows": 2,
+ "filtered": 100,
+ "attached_condition": "(`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)"
+ }
+ }
+ ]
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result 2012-05-29 13:24:49 +0000
@@ -9911,6 +9911,96 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ref",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "key": "col_int_key",
+ "key_length": "4",
+ "ref": [
+ "test.grandparent1.col_int_nokey"
+ ],
+ "rows": 2,
+ "filtered": 100,
+ "first_match": "grandparent1",
+ "attached_condition": "(`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)"
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
#
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-05-29 13:24:49 +0000
@@ -9912,6 +9912,96 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ref",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "key": "col_int_key",
+ "key_length": "4",
+ "ref": [
+ "test.grandparent1.col_int_nokey"
+ ],
+ "rows": 2,
+ "filtered": 100,
+ "first_match": "grandparent1",
+ "attached_condition": "(`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)"
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
#
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-05-29 13:24:49 +0000
@@ -9917,6 +9917,95 @@ y y
y y
y y
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ref",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "key": "col_int_key",
+ "key_length": "4",
+ "ref": [
+ "test.grandparent1.col_int_nokey"
+ ],
+ "rows": 2,
+ "filtered": 100,
+ "first_match": "grandparent1",
+ "attached_condition": "(`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)"
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 7
+7 0
+7 0
+7 3
+7 3
+1 6
+1 6
+8 5
+8 5
+8 1
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
#
=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-05-29 13:24:49 +0000
@@ -9913,6 +9913,96 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ref",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "key": "col_int_key",
+ "key_length": "4",
+ "ref": [
+ "test.grandparent1.col_int_nokey"
+ ],
+ "rows": 2,
+ "filtered": 100,
+ "first_match": "grandparent1",
+ "attached_condition": "(`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)"
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
#
=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result 2012-05-29 13:24:49 +0000
@@ -9919,6 +9919,98 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "duplicates_removal": {
+ "using_temporary_table": true,
+ "table": {
+ "table_name": "t2",
+ "access_type": "ref",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "key": "col_int_key",
+ "key_length": "4",
+ "ref": [
+ "test.grandparent1.col_int_nokey"
+ ],
+ "rows": 2,
+ "filtered": 100,
+ "attached_condition": "(`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)"
+ }
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# 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 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2012-05-29 13:24:49 +0000
@@ -9920,6 +9920,102 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "duplicates_removal": {
+ "using_temporary_table": true,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ref",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "key": "col_int_key",
+ "key_length": "4",
+ "ref": [
+ "test.grandparent1.col_int_nokey"
+ ],
+ "rows": 2,
+ "filtered": 100,
+ "attached_condition": "(`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)"
+ }
+ }
+ ]
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
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 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-05-29 13:24:49 +0000
@@ -9921,6 +9921,101 @@ y y
y y
y y
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "duplicates_removal": {
+ "using_temporary_table": true,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ref",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "key": "col_int_key",
+ "key_length": "4",
+ "ref": [
+ "test.grandparent1.col_int_nokey"
+ ],
+ "rows": 2,
+ "filtered": 100,
+ "attached_condition": "(`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)"
+ }
+ }
+ ]
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 7
+7 0
+7 0
+7 3
+7 3
+1 6
+1 6
+8 5
+8 5
+8 1
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-05-29 13:24:49 +0000
@@ -9921,6 +9921,102 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "duplicates_removal": {
+ "using_temporary_table": true,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ref",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "key": "col_int_key",
+ "key_length": "4",
+ "ref": [
+ "test.grandparent1.col_int_nokey"
+ ],
+ "rows": 2,
+ "filtered": 100,
+ "attached_condition": "(`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)"
+ }
+ }
+ ]
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`grandparent1`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_mat.result 2012-05-29 13:24:49 +0000
@@ -9934,6 +9934,100 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "using_temporary_table": true,
+ "access_type": "eq_ref",
+ "key": "<auto_key>",
+ "rows": 1,
+ "materialized_from_subquery": {
+ "query_block": {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ALL",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "(`test`.`t2`.`col_int_key` = `test`.`t2`.`col_int_nokey`)"
+ }
+ }
+ }
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on(((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`))) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`t2`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# 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 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result 2012-05-29 13:24:49 +0000
@@ -9935,6 +9935,100 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "using_temporary_table": true,
+ "access_type": "eq_ref",
+ "key": "<auto_key>",
+ "rows": 1,
+ "materialized_from_subquery": {
+ "query_block": {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ALL",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "(`test`.`t2`.`col_int_key` = `test`.`t2`.`col_int_nokey`)"
+ }
+ }
+ }
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on(((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`))) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`t2`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
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 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-05-29 13:24:49 +0000
@@ -9934,6 +9934,99 @@ y y
y y
y y
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "using_temporary_table": true,
+ "access_type": "eq_ref",
+ "key": "<auto_key>",
+ "rows": 1,
+ "materialized_from_subquery": {
+ "query_block": {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ALL",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "(`test`.`t2`.`col_int_key` = `test`.`t2`.`col_int_nokey`)"
+ }
+ }
+ }
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on(((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`))) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`t2`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 7
+7 0
+7 0
+7 3
+7 3
+1 6
+1 6
+8 5
+8 5
+8 1
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-05-29 13:24:49 +0000
@@ -9936,6 +9936,100 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ },
+ {
+ "table": {
+ "using_temporary_table": true,
+ "access_type": "eq_ref",
+ "key": "<auto_key>",
+ "rows": 1,
+ "materialized_from_subquery": {
+ "query_block": {
+ "table": {
+ "table_name": "t2",
+ "access_type": "ALL",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "(`test`.`t2`.`col_int_key` = `test`.`t2`.`col_int_nokey`)"
+ }
+ }
+ }
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` semi join (`test`.`t2`) left join `test`.`t1` on(((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`))) where ((`test`.`t2`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`) and (`test`.`t2`.`col_int_key` = `test`.`t2`.`col_int_nokey`))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result 2012-05-29 13:24:49 +0000
@@ -10004,6 +10004,100 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "<in_optimizer>((`test`.`grandparent1`.`col_int_nokey`,`test`.`grandparent1`.`col_int_nokey`),(`test`.`grandparent1`.`col_int_nokey`,`test`.`grandparent1`.`col_int_nokey`) in ( <materialize> (select `test`.`t2`.`col_int_nokey`,`test`.`t2`.`col_int_key` from `test`.`t2` ), <primary_index_lookup>(`test`.`grandparent1`.`col_int_nokey` in <temporary table> on distinct_key where ((`test`.`grandparent1`.`col_int_nokey` = `materialized subselect`.`col_int_nokey`) and (`test`.`grandparent1`.`col_int_nokey` = `materialized subselect`.`col_int_key`)))))",
+ "attached_subqueries": [
+ {
+ "using_temporary_table": true,
+ "dependent": false,
+ "cacheable": true,
+ "table": {
+ "access_type": "eq_ref",
+ "key": "<auto_key>",
+ "rows": 1
+ },
+ "query_block": {
+ "select_id": 2,
+ "table": {
+ "table_name": "t2",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100
+ }
+ }
+ }
+ ]
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where <in_optimizer>((`test`.`grandparent1`.`col_int_nokey`,`test`.`grandparent1`.`col_int_nokey`),(`test`.`grandparent1`.`col_int_nokey`,`test`.`grandparent1`.`col_int_nokey`) in ( <materialize> (/* select#2 */ select `test`.`t2`.`col_int_nokey`,`test`.`t2`.`col_int_key` from `test`.`t2` ), <primary_index_lookup>(`test`.`grandparent1`.`col_int_nokey` in <temporary table> on distinct_key where ((`test`.`grandparent1`.`col_int_nokey` = `materialized subselect`.`col_int_nokey`) and (`test`.`grandparent1`.`col_int_nokey` = `materialized subselect`.`col_int_key`)))))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# 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 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_none.result 2012-05-29 13:24:49 +0000
@@ -9917,6 +9917,103 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "<in_optimizer>((`test`.`grandparent1`.`col_int_nokey`,`test`.`grandparent1`.`col_int_nokey`),<exists>(<index_lookup>(<cache>(`test`.`grandparent1`.`col_int_nokey`) in t2 on col_int_key where ((<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_nokey`) and (<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_key`)))))",
+ "attached_subqueries": [
+ {
+ "dependent": true,
+ "cacheable": false,
+ "query_block": {
+ "select_id": 2,
+ "table": {
+ "table_name": "t2",
+ "access_type": "index_subquery",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "key": "col_int_key",
+ "key_length": "4",
+ "ref": [
+ "func"
+ ],
+ "rows": 2,
+ "filtered": 100,
+ "attached_condition": "(<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_nokey`)"
+ }
+ }
+ }
+ ]
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where <in_optimizer>((`test`.`grandparent1`.`col_int_nokey`,`test`.`grandparent1`.`col_int_nokey`),<exists>(<index_lookup>(<cache>(`test`.`grandparent1`.`col_int_nokey`) in t2 on col_int_key where ((<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_nokey`) and (<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_key`)))))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# 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 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result 2012-05-29 13:24:49 +0000
@@ -9918,6 +9918,103 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "<in_optimizer>((`test`.`grandparent1`.`col_int_nokey`,`test`.`grandparent1`.`col_int_nokey`),<exists>(<index_lookup>(<cache>(`test`.`grandparent1`.`col_int_nokey`) in t2 on col_int_key where ((<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_nokey`) and (<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_key`)))))",
+ "attached_subqueries": [
+ {
+ "dependent": true,
+ "cacheable": false,
+ "query_block": {
+ "select_id": 2,
+ "table": {
+ "table_name": "t2",
+ "access_type": "index_subquery",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "key": "col_int_key",
+ "key_length": "4",
+ "ref": [
+ "func"
+ ],
+ "rows": 2,
+ "filtered": 100,
+ "attached_condition": "(<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_nokey`)"
+ }
+ }
+ }
+ ]
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where <in_optimizer>((`test`.`grandparent1`.`col_int_nokey`,`test`.`grandparent1`.`col_int_nokey`),<exists>(<index_lookup>(<cache>(`test`.`grandparent1`.`col_int_nokey`) in t2 on col_int_key where ((<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_nokey`) and (<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_key`)))))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
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 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-05-29 13:24:49 +0000
@@ -9916,6 +9916,102 @@ y y
y y
y y
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "<in_optimizer>((`test`.`grandparent1`.`col_int_nokey`,`test`.`grandparent1`.`col_int_nokey`),<exists>(<index_lookup>(<cache>(`test`.`grandparent1`.`col_int_nokey`) in t2 on col_int_key where ((<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_nokey`) and (<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_key`)))))",
+ "attached_subqueries": [
+ {
+ "dependent": true,
+ "cacheable": false,
+ "query_block": {
+ "select_id": 2,
+ "table": {
+ "table_name": "t2",
+ "access_type": "index_subquery",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "key": "col_int_key",
+ "key_length": "4",
+ "ref": [
+ "func"
+ ],
+ "rows": 2,
+ "filtered": 100,
+ "attached_condition": "(<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_nokey`)"
+ }
+ }
+ }
+ ]
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where <in_optimizer>((`test`.`grandparent1`.`col_int_nokey`,`test`.`grandparent1`.`col_int_nokey`),<exists>(<index_lookup>(<cache>(`test`.`grandparent1`.`col_int_nokey`) in t2 on col_int_key where ((<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_nokey`) and (<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_key`)))))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 7
+7 0
+7 0
+7 3
+7 3
+1 6
+1 6
+8 5
+8 5
+8 1
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result 2012-05-24 11:55:37 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2012-05-29 13:24:49 +0000
@@ -9919,6 +9919,103 @@ m m
d d
d d
DROP TABLE t1, t2;
+CREATE TABLE t1 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t1 VALUES
+(1,7), (7,0), (4,9), (7,3), (0,4), (2,2), (9,5), (4,3), (0,1), (9,3),
+(1,6), (3,7), (8,5), (8,1), (18,204), (84,224), (6,9), (3,5), (6,0), (6,3);
+CREATE TABLE t2 (
+col_int_nokey int NOT NULL,
+col_int_key int NOT NULL,
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(4,0), (6,8), (3,1), (5,8), (3,9), (246,24), (2,6), (9,1), (3,6), (8,2),
+(1,4), (8,8), (8,4), (5,4), (7,7), (5,4), (1,1), (6,9), (2,4), (9,8);
+explain format=json SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+EXPLAIN
+{
+ "query_block": {
+ "select_id": 1,
+ "nested_loop": [
+ {
+ "table": {
+ "table_name": "grandparent1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "attached_condition": "<in_optimizer>((`test`.`grandparent1`.`col_int_nokey`,`test`.`grandparent1`.`col_int_nokey`),<exists>(<index_lookup>(<cache>(`test`.`grandparent1`.`col_int_nokey`) in t2 on col_int_key where ((<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_nokey`) and (<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_key`)))))",
+ "attached_subqueries": [
+ {
+ "dependent": true,
+ "cacheable": false,
+ "query_block": {
+ "select_id": 2,
+ "table": {
+ "table_name": "t2",
+ "access_type": "index_subquery",
+ "possible_keys": [
+ "col_int_key"
+ ],
+ "key": "col_int_key",
+ "key_length": "4",
+ "ref": [
+ "func"
+ ],
+ "rows": 2,
+ "filtered": 100,
+ "attached_condition": "(<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_nokey`)"
+ }
+ }
+ }
+ ]
+ }
+ },
+ {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "rows": 20,
+ "filtered": 100,
+ "using_join_buffer": "Block Nested Loop",
+ "attached_condition": "<if>(is_not_null_compl(t1), (`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`), true)"
+ }
+ }
+ ]
+ }
+}
+Warnings:
+Note 1003 /* select#1 */ select `test`.`grandparent1`.`col_int_nokey` AS `col_int_nokey`,`test`.`grandparent1`.`col_int_key` AS `col_int_key` from `test`.`t1` `grandparent1` left join `test`.`t1` on((`test`.`t1`.`col_int_nokey` = `test`.`grandparent1`.`col_int_nokey`)) where <in_optimizer>((`test`.`grandparent1`.`col_int_nokey`,`test`.`grandparent1`.`col_int_nokey`),<exists>(<index_lookup>(<cache>(`test`.`grandparent1`.`col_int_nokey`) in t2 on col_int_key where ((<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_nokey`) and (<cache>(`test`.`grandparent1`.`col_int_nokey`) = `test`.`t2`.`col_int_key`)))))
+SELECT grandparent1.*
+FROM t1 AS grandparent1
+LEFT JOIN t1 USING (col_int_nokey)
+WHERE (col_int_nokey, col_int_nokey) IN
+(SELECT col_int_nokey, col_int_key
+FROM t2
+);
+col_int_nokey col_int_key
+1 7
+1 6
+7 0
+7 3
+7 0
+7 3
+1 7
+1 6
+8 5
+8 1
+8 5
+8 1
+DROP TABLE t1, t2;
# End of test for bug#14064201.
# End of 5.6 tests
set optimizer_switch=default;
=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc 2012-05-29 10:48:54 +0000
+++ b/sql/sql_optimizer.cc 2012-05-29 13:24:49 +0000
@@ -1957,6 +1957,7 @@ static Item *eliminate_item_equal(Item *
if (item != item_field &&
sj_is_materialize_strategy(
item_field->field->table->reginfo.join_tab->get_sj_strategy()) &&
+ item_equal->get_subst_item(item_field) == item_field &&
item->field->table->reginfo.join_tab->emb_sj_nest !=
item_field->field->table->reginfo.join_tab->emb_sj_nest)
item= item_field;
No bundle (reason: useless for push emails).
| Thread |
|---|
| • bzr push into mysql-trunk branch (roy.lyseng:3916 to 3917) Bug#14064201 | Roy Lyseng | 29 May |
