List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:May 29 2012 1:45pm
Subject:bzr push into mysql-trunk branch (roy.lyseng:3916 to 3917) Bug#14064201
View as plain text  
 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#14064201Roy Lyseng29 May