MySQL Lists: commits: bzr push into mysql-trunk branch (roy.lyseng:3910 to 3911) Bug#14064201

List:	Commits	« Previous Message Next Message »
From:	Roy Lyseng	Date:	May 23 2012 12:57pm
Subject:	bzr push into mysql-trunk branch (roy.lyseng:3910 to 3911) Bug#14064201
View as plain text
 3911 Roy Lyseng	2012-05-23
      Bug#14064201: Missing data on join of derived table + WHERE .. IN with two ops
      
      The fix for bug#13414014 fixed an issue with field equivalences related
      to the semi-join MaterializeLookup strategy. During review, it was
      decided to add the same fix for MaterializeScan "for safety".
      Unfortunately, this caused a regression: Some predicates that were
      evaluated when the subquery was materialized were applied for the
      outer query as well.
      
      The query from the bug is:
      SELECT *
      FROM (SELECT * FROM t2) AS derived1 LEFT JOIN t1
           USING (col_varchar_nokey)
      WHERE (col_varchar_nokey, col_varchar_nokey) IN
       (SELECT col_varchar_nokey, col_varchar_key FROM t2 AS derived2);
      
      The plan generated for this query is:
        SJM(derived2) - derived1 - t1
      
      Here is explain JSON of the condition attached to materialized table
      derived2 (redundant syntax elements removed):
      
      "attached_condition":
        "derived2.col_varchar_key = derived2.col_varchar_nokey and
         derived2.col_varchar_nokey is not null"
      
      Here is the attached condition for the table t1 of the main query:
      
      "attached_condition":
        "(trigcond_if(is_not_null_compl(t1),
                      derived2.col_varchar_key = derived2.col_varchar_nokey, true)
          and
          trigcond_if(is_not_null_compl(t1),
                      t1.col_varchar_nokey = derived2.col_varchar_nokey), true))"
      
      As we see, the equality on derived2 is already evaluated during
      materialization. The MaterializeScan strategy copies back those fields
      that are needed for matching with columns from the outer query
      from the materialized table into the original tables. Due to multiple
      equality processing, the field col_varchar_nokey is copied back, but
      col_varchar_key is not. Thus, we have to eliminate references to columns
      that are only referred within the subquery. 
      
      Further, we realize that the problem from bug#13414014 is not only related
      to the MaterializeLookup strategy. It can also occur for MaterializeScan
      where some outer tables are placed before the inner tables in the join plan.
      (This is an unusual MatScan strategy, most practical strategies have the
      outer tables placed after the inner tables).
      Thus, we keep the test sj_is_materialize_strategy(), but limit the check
      to those fields that do not come from the materialized semi-join nest.
      
      To sum up the combined changes in bug#13414014 and bug#14064201:
      - All changes involve queries with a multi-level multiple equality
        spanning a materialized semi-join nest. Such equalities are used to
        model OR conditions, but also used to model outer join conditions:
        The WHERE clause is the upper level equality and the most-outer join
        condition is the lower level equality.
      - The lower level equalities are not covered by the upper level equalities
        if they involve outer tables placed before the materialized tables in the
        join plan. bug#13414014 was solved by repeating equalities from the upper
        level on the lower level. Due to the way items within multiple equalities
        are sorted (in planned JOIN_TAB order) this is a non-issue if outer
        tables come after the inner tables.
      - A join condition is evaluated for the last inner table of the outer join.
        This may cause problems for a MaterializeScan strategy because items
        used to produce the materialized table are not copied back to item
        buffers. The problem is solved by letting such inner predicates be
        evaluated on the upper level (usually the WHERE clause) and ignored on
        the lower level. May there be such conditions that are not evaluated
        on the upper level and thus have to be evaluated on the lower level?
        No, because these conditions are part of the subquery condition and
        thus propagated fully to the upper level multiple equality.
       
      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 used to provide coverage
        for multiple equalities on a lower level as well as on an upper level
        for conditions involved with a materialized semi-join. Coverage should
        not be made for conditions that are inner to the semi-join.

    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
 3910 Roy Lyseng	2012-05-22
      Bug#14048292: Segfault in Item_field::result_type on 2nd execution of prep stmt
      
      Post-review fix.
      
      sql/sql_optimizer.cc
        Comment fix.

    modified:
      sql/sql_optimizer.cc
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2012-05-18 09:50:03 +0000
+++ b/mysql-test/include/subquery_sj.inc	2012-05-23 11:12:19 +0000
@@ -6192,4 +6192,43 @@ DROP TABLE t1;
 
 --echo # End of test for bug#14048292.
 
+--echo #
+--echo # Bug#14064201: Missing data on join of derived table + WHERE .. IN
+--echo #               with two operands
+--echo #
+
+CREATE TABLE t1 (
+  col_varchar_nokey VARCHAR(1)
+);
+
+INSERT INTO t1 VALUES
+ ('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+ ('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+
+CREATE TABLE t2 (
+  col_varchar_key VARCHAR(1),
+  col_varchar_nokey VARCHAR(1),
+  KEY col_varchar_key(col_varchar_key)
+);
+
+INSERT INTO t2 VALUES
+ ('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+
+let $query=
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+     LEFT JOIN t1
+     USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+   (SELECT col_varchar_nokey, col_varchar_key
+    FROM t2 AS derived2
+   );
+
+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-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2012-05-23 11:12:19 +0000
@@ -9821,5 +9821,121 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "using_temporary_table": true,
+          "access_type": "ALL",
+          "materialized_from_subquery": {
+            "query_block": {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "derived1",
+          "access_type": "ref",
+          "possible_keys": [
+            "auto_key0"
+          ],
+          "key": "auto_key0",
+          "key_length": "4",
+          "ref": [
+            "test.derived2.col_varchar_nokey"
+          ],
+          "rows": 2,
+          "filtered": 100,
+          "materialized_from_subquery": {
+            "using_temporary_table": true,
+            "dependent": false,
+            "cacheable": true,
+            "query_block": {
+              "select_id": 2,
+              "table": {
+                "table_name": "t2",
+                "access_type": "ALL",
+                "rows": 7,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+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-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result	2012-05-23 11:12:19 +0000
@@ -9826,6 +9826,122 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "using_temporary_table": true,
+          "access_type": "ALL",
+          "materialized_from_subquery": {
+            "query_block": {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "derived1",
+          "access_type": "ref",
+          "possible_keys": [
+            "auto_key0"
+          ],
+          "key": "auto_key0",
+          "key_length": "4",
+          "ref": [
+            "test.derived2.col_varchar_nokey"
+          ],
+          "rows": 2,
+          "filtered": 100,
+          "materialized_from_subquery": {
+            "using_temporary_table": true,
+            "dependent": false,
+            "cacheable": true,
+            "query_block": {
+              "select_id": 2,
+              "table": {
+                "table_name": "t2",
+                "access_type": "ALL",
+                "rows": 7,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2012-05-23 11:12:19 +0000
@@ -9826,6 +9826,121 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "using_temporary_table": true,
+          "access_type": "ALL",
+          "materialized_from_subquery": {
+            "query_block": {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "derived1",
+          "access_type": "ref",
+          "possible_keys": [
+            "auto_key0"
+          ],
+          "key": "auto_key0",
+          "key_length": "4",
+          "ref": [
+            "test.derived2.col_varchar_nokey"
+          ],
+          "rows": 2,
+          "filtered": 100,
+          "materialized_from_subquery": {
+            "using_temporary_table": true,
+            "dependent": false,
+            "cacheable": true,
+            "query_block": {
+              "select_id": 2,
+              "table": {
+                "table_name": "t2",
+                "access_type": "ALL",
+                "rows": 7,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+j	j
+v	v
+v	v
+c	c
+m	m
+d	d
+d	d
+y	y
+y	y
+y	y
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result	2012-05-23 11:12:19 +0000
@@ -9827,6 +9827,122 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "using_temporary_table": true,
+          "access_type": "ALL",
+          "materialized_from_subquery": {
+            "query_block": {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "derived1",
+          "access_type": "ref",
+          "possible_keys": [
+            "auto_key0"
+          ],
+          "key": "auto_key0",
+          "key_length": "4",
+          "ref": [
+            "test.derived2.col_varchar_nokey"
+          ],
+          "rows": 2,
+          "filtered": 100,
+          "materialized_from_subquery": {
+            "using_temporary_table": true,
+            "dependent": false,
+            "cacheable": true,
+            "query_block": {
+              "select_id": 2,
+              "table": {
+                "table_name": "t2",
+                "access_type": "ALL",
+                "rows": 7,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2012-05-23 11:12:19 +0000
@@ -9804,5 +9804,120 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "duplicates_removal": {
+          "using_temporary_table": true,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            },
+            {
+              "table": {
+                "table_name": "derived1",
+                "access_type": "ref",
+                "possible_keys": [
+                  "auto_key0"
+                ],
+                "key": "auto_key0",
+                "key_length": "4",
+                "ref": [
+                  "test.derived2.col_varchar_nokey"
+                ],
+                "rows": 2,
+                "filtered": 100,
+                "materialized_from_subquery": {
+                  "using_temporary_table": true,
+                  "dependent": false,
+                  "cacheable": true,
+                  "query_block": {
+                    "select_id": 2,
+                    "table": {
+                      "table_name": "t2",
+                      "access_type": "ALL",
+                      "rows": 7,
+                      "filtered": 100
+                    }
+                  }
+                }
+              }
+            }
+          ]
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+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-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result	2012-05-23 11:12:19 +0000
@@ -9805,6 +9805,121 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "duplicates_removal": {
+          "using_temporary_table": true,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            },
+            {
+              "table": {
+                "table_name": "derived1",
+                "access_type": "ref",
+                "possible_keys": [
+                  "auto_key0"
+                ],
+                "key": "auto_key0",
+                "key_length": "4",
+                "ref": [
+                  "test.derived2.col_varchar_nokey"
+                ],
+                "rows": 2,
+                "filtered": 100,
+                "materialized_from_subquery": {
+                  "using_temporary_table": true,
+                  "dependent": false,
+                  "cacheable": true,
+                  "query_block": {
+                    "select_id": 2,
+                    "table": {
+                      "table_name": "t2",
+                      "access_type": "ALL",
+                      "rows": 7,
+                      "filtered": 100
+                    }
+                  }
+                }
+              }
+            }
+          ]
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2012-05-23 11:12:19 +0000
@@ -9807,6 +9807,120 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "duplicates_removal": {
+          "using_temporary_table": true,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            },
+            {
+              "table": {
+                "table_name": "derived1",
+                "access_type": "ref",
+                "possible_keys": [
+                  "auto_key0"
+                ],
+                "key": "auto_key0",
+                "key_length": "4",
+                "ref": [
+                  "test.derived2.col_varchar_nokey"
+                ],
+                "rows": 2,
+                "filtered": 100,
+                "materialized_from_subquery": {
+                  "using_temporary_table": true,
+                  "dependent": false,
+                  "cacheable": true,
+                  "query_block": {
+                    "select_id": 2,
+                    "table": {
+                      "table_name": "t2",
+                      "access_type": "ALL",
+                      "rows": 7,
+                      "filtered": 100
+                    }
+                  }
+                }
+              }
+            }
+          ]
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+j	j
+v	v
+v	v
+c	c
+m	m
+d	d
+d	d
+y	y
+y	y
+y	y
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2012-05-23 11:12:19 +0000
@@ -9806,6 +9806,121 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "duplicates_removal": {
+          "using_temporary_table": true,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            },
+            {
+              "table": {
+                "table_name": "derived1",
+                "access_type": "ref",
+                "possible_keys": [
+                  "auto_key0"
+                ],
+                "key": "auto_key0",
+                "key_length": "4",
+                "ref": [
+                  "test.derived2.col_varchar_nokey"
+                ],
+                "rows": 2,
+                "filtered": 100,
+                "materialized_from_subquery": {
+                  "using_temporary_table": true,
+                  "dependent": false,
+                  "cacheable": true,
+                  "query_block": {
+                    "select_id": 2,
+                    "table": {
+                      "table_name": "t2",
+                      "access_type": "ALL",
+                      "rows": 7,
+                      "filtered": 100
+                    }
+                  }
+                }
+              }
+            }
+          ]
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2012-05-23 11:12:19 +0000
@@ -9797,6 +9797,121 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "duplicates_removal": {
+          "using_temporary_table": true,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            },
+            {
+              "table": {
+                "table_name": "derived1",
+                "access_type": "ref",
+                "possible_keys": [
+                  "auto_key0"
+                ],
+                "key": "auto_key0",
+                "key_length": "4",
+                "ref": [
+                  "test.derived2.col_varchar_nokey"
+                ],
+                "rows": 2,
+                "filtered": 100,
+                "materialized_from_subquery": {
+                  "using_temporary_table": true,
+                  "dependent": false,
+                  "cacheable": true,
+                  "query_block": {
+                    "select_id": 2,
+                    "table": {
+                      "table_name": "t2",
+                      "access_type": "ALL",
+                      "rows": 7,
+                      "filtered": 100
+                    }
+                  }
+                }
+              }
+            }
+          ]
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-05-23 11:12:19 +0000
@@ -9798,6 +9798,121 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "duplicates_removal": {
+          "using_temporary_table": true,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            },
+            {
+              "table": {
+                "table_name": "derived1",
+                "access_type": "ref",
+                "possible_keys": [
+                  "auto_key0"
+                ],
+                "key": "auto_key0",
+                "key_length": "4",
+                "ref": [
+                  "test.derived2.col_varchar_nokey"
+                ],
+                "rows": 2,
+                "filtered": 100,
+                "materialized_from_subquery": {
+                  "using_temporary_table": true,
+                  "dependent": false,
+                  "cacheable": true,
+                  "query_block": {
+                    "select_id": 2,
+                    "table": {
+                      "table_name": "t2",
+                      "access_type": "ALL",
+                      "rows": 7,
+                      "filtered": 100
+                    }
+                  }
+                }
+              }
+            }
+          ]
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-05-23 11:12:19 +0000
@@ -9804,6 +9804,120 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "duplicates_removal": {
+          "using_temporary_table": true,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            },
+            {
+              "table": {
+                "table_name": "derived1",
+                "access_type": "ref",
+                "possible_keys": [
+                  "auto_key0"
+                ],
+                "key": "auto_key0",
+                "key_length": "4",
+                "ref": [
+                  "test.derived2.col_varchar_nokey"
+                ],
+                "rows": 2,
+                "filtered": 100,
+                "materialized_from_subquery": {
+                  "using_temporary_table": true,
+                  "dependent": false,
+                  "cacheable": true,
+                  "query_block": {
+                    "select_id": 2,
+                    "table": {
+                      "table_name": "t2",
+                      "access_type": "ALL",
+                      "rows": 7,
+                      "filtered": 100
+                    }
+                  }
+                }
+              }
+            }
+          ]
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+j	j
+v	v
+v	v
+c	c
+m	m
+d	d
+d	d
+y	y
+y	y
+y	y
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-05-23 11:12:19 +0000
@@ -9799,6 +9799,121 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "duplicates_removal": {
+          "using_temporary_table": true,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            },
+            {
+              "table": {
+                "table_name": "derived1",
+                "access_type": "ref",
+                "possible_keys": [
+                  "auto_key0"
+                ],
+                "key": "auto_key0",
+                "key_length": "4",
+                "ref": [
+                  "test.derived2.col_varchar_nokey"
+                ],
+                "rows": 2,
+                "filtered": 100,
+                "materialized_from_subquery": {
+                  "using_temporary_table": true,
+                  "dependent": false,
+                  "cacheable": true,
+                  "query_block": {
+                    "select_id": 2,
+                    "table": {
+                      "table_name": "t2",
+                      "access_type": "ALL",
+                      "rows": 7,
+                      "filtered": 100
+                    }
+                  }
+                }
+              }
+            }
+          ]
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2012-05-23 11:12:19 +0000
@@ -9805,5 +9805,120 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "duplicates_removal": {
+          "using_temporary_table": true,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            },
+            {
+              "table": {
+                "table_name": "derived1",
+                "access_type": "ref",
+                "possible_keys": [
+                  "auto_key0"
+                ],
+                "key": "auto_key0",
+                "key_length": "4",
+                "ref": [
+                  "test.derived2.col_varchar_nokey"
+                ],
+                "rows": 2,
+                "filtered": 100,
+                "materialized_from_subquery": {
+                  "using_temporary_table": true,
+                  "dependent": false,
+                  "cacheable": true,
+                  "query_block": {
+                    "select_id": 2,
+                    "table": {
+                      "table_name": "t2",
+                      "access_type": "ALL",
+                      "rows": 7,
+                      "filtered": 100
+                    }
+                  }
+                }
+              }
+            }
+          ]
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+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-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result	2012-05-23 11:12:19 +0000
@@ -9806,6 +9806,121 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "duplicates_removal": {
+          "using_temporary_table": true,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            },
+            {
+              "table": {
+                "table_name": "derived1",
+                "access_type": "ref",
+                "possible_keys": [
+                  "auto_key0"
+                ],
+                "key": "auto_key0",
+                "key_length": "4",
+                "ref": [
+                  "test.derived2.col_varchar_nokey"
+                ],
+                "rows": 2,
+                "filtered": 100,
+                "materialized_from_subquery": {
+                  "using_temporary_table": true,
+                  "dependent": false,
+                  "cacheable": true,
+                  "query_block": {
+                    "select_id": 2,
+                    "table": {
+                      "table_name": "t2",
+                      "access_type": "ALL",
+                      "rows": 7,
+                      "filtered": 100
+                    }
+                  }
+                }
+              }
+            }
+          ]
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2012-05-23 11:12:19 +0000
@@ -9808,6 +9808,120 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "duplicates_removal": {
+          "using_temporary_table": true,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            },
+            {
+              "table": {
+                "table_name": "derived1",
+                "access_type": "ref",
+                "possible_keys": [
+                  "auto_key0"
+                ],
+                "key": "auto_key0",
+                "key_length": "4",
+                "ref": [
+                  "test.derived2.col_varchar_nokey"
+                ],
+                "rows": 2,
+                "filtered": 100,
+                "materialized_from_subquery": {
+                  "using_temporary_table": true,
+                  "dependent": false,
+                  "cacheable": true,
+                  "query_block": {
+                    "select_id": 2,
+                    "table": {
+                      "table_name": "t2",
+                      "access_type": "ALL",
+                      "rows": 7,
+                      "filtered": 100
+                    }
+                  }
+                }
+              }
+            }
+          ]
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+j	j
+v	v
+v	v
+c	c
+m	m
+d	d
+d	d
+y	y
+y	y
+y	y
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2012-05-23 11:12:19 +0000
@@ -9807,6 +9807,121 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "duplicates_removal": {
+          "using_temporary_table": true,
+          "nested_loop": [
+            {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            },
+            {
+              "table": {
+                "table_name": "derived1",
+                "access_type": "ref",
+                "possible_keys": [
+                  "auto_key0"
+                ],
+                "key": "auto_key0",
+                "key_length": "4",
+                "ref": [
+                  "test.derived2.col_varchar_nokey"
+                ],
+                "rows": 2,
+                "filtered": 100,
+                "materialized_from_subquery": {
+                  "using_temporary_table": true,
+                  "dependent": false,
+                  "cacheable": true,
+                  "query_block": {
+                    "select_id": 2,
+                    "table": {
+                      "table_name": "t2",
+                      "access_type": "ALL",
+                      "rows": 7,
+                      "filtered": 100
+                    }
+                  }
+                }
+              }
+            }
+          ]
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2012-05-23 11:12:19 +0000
@@ -9819,5 +9819,121 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "using_temporary_table": true,
+          "access_type": "ALL",
+          "materialized_from_subquery": {
+            "query_block": {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "derived1",
+          "access_type": "ref",
+          "possible_keys": [
+            "auto_key0"
+          ],
+          "key": "auto_key0",
+          "key_length": "4",
+          "ref": [
+            "test.derived2.col_varchar_nokey"
+          ],
+          "rows": 2,
+          "filtered": 100,
+          "materialized_from_subquery": {
+            "using_temporary_table": true,
+            "dependent": false,
+            "cacheable": true,
+            "query_block": {
+              "select_id": 2,
+              "table": {
+                "table_name": "t2",
+                "access_type": "ALL",
+                "rows": 7,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+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-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result	2012-05-23 11:12:19 +0000
@@ -9820,6 +9820,122 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "using_temporary_table": true,
+          "access_type": "ALL",
+          "materialized_from_subquery": {
+            "query_block": {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "derived1",
+          "access_type": "ref",
+          "possible_keys": [
+            "auto_key0"
+          ],
+          "key": "auto_key0",
+          "key_length": "4",
+          "ref": [
+            "test.derived2.col_varchar_nokey"
+          ],
+          "rows": 2,
+          "filtered": 100,
+          "materialized_from_subquery": {
+            "using_temporary_table": true,
+            "dependent": false,
+            "cacheable": true,
+            "query_block": {
+              "select_id": 2,
+              "table": {
+                "table_name": "t2",
+                "access_type": "ALL",
+                "rows": 7,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2012-05-23 11:12:19 +0000
@@ -9820,6 +9820,121 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "using_temporary_table": true,
+          "access_type": "ALL",
+          "materialized_from_subquery": {
+            "query_block": {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "derived1",
+          "access_type": "ref",
+          "possible_keys": [
+            "auto_key0"
+          ],
+          "key": "auto_key0",
+          "key_length": "4",
+          "ref": [
+            "test.derived2.col_varchar_nokey"
+          ],
+          "rows": 2,
+          "filtered": 100,
+          "materialized_from_subquery": {
+            "using_temporary_table": true,
+            "dependent": false,
+            "cacheable": true,
+            "query_block": {
+              "select_id": 2,
+              "table": {
+                "table_name": "t2",
+                "access_type": "ALL",
+                "rows": 7,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+j	j
+v	v
+v	v
+c	c
+m	m
+d	d
+d	d
+y	y
+y	y
+y	y
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result	2012-05-23 11:12:19 +0000
@@ -9821,6 +9821,122 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "using_temporary_table": true,
+          "access_type": "ALL",
+          "materialized_from_subquery": {
+            "query_block": {
+              "table": {
+                "table_name": "derived2",
+                "access_type": "ALL",
+                "possible_keys": [
+                  "col_varchar_key"
+                ],
+                "rows": 7,
+                "filtered": 100,
+                "attached_condition": "((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`test`.`derived2`.`col_varchar_nokey` is not null))"
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "derived1",
+          "access_type": "ref",
+          "possible_keys": [
+            "auto_key0"
+          ],
+          "key": "auto_key0",
+          "key_length": "4",
+          "ref": [
+            "test.derived2.col_varchar_nokey"
+          ],
+          "rows": 2,
+          "filtered": 100,
+          "materialized_from_subquery": {
+            "using_temporary_table": true,
+            "dependent": false,
+            "cacheable": true,
+            "query_block": {
+              "select_id": 2,
+              "table": {
+                "table_name": "t2",
+                "access_type": "ALL",
+                "rows": 7,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` semi join (`test`.`t2` `derived2`) left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`)) where ((`test`.`derived2`.`col_varchar_key` = `test`.`derived2`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `test`.`derived2`.`col_varchar_nokey`))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2012-05-23 11:12:19 +0000
@@ -9895,5 +9895,115 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "derived1",
+          "access_type": "ALL",
+          "rows": 7,
+          "filtered": 100,
+          "attached_condition": "<in_optimizer>((`derived1`.`col_varchar_nokey`,`derived1`.`col_varchar_nokey`),(`derived1`.`col_varchar_nokey`,`derived1`.`col_varchar_nokey`) in ( <materialize> (select `test`.`derived2`.`col_varchar_nokey`,`test`.`derived2`.`col_varchar_key` from `test`.`t2` `derived2` ), <primary_index_lookup>(`derived1`.`col_varchar_nokey` in <temporary table> on distinct_key where ((`derived1`.`col_varchar_nokey` = `materialized subselect`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `materialized subselect`.`col_varchar_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": 3,
+                "table": {
+                  "table_name": "derived2",
+                  "access_type": "ALL",
+                  "rows": 7,
+                  "filtered": 100
+                }
+              }
+            }
+          ],
+          "materialized_from_subquery": {
+            "using_temporary_table": true,
+            "dependent": false,
+            "cacheable": true,
+            "query_block": {
+              "select_id": 2,
+              "table": {
+                "table_name": "t2",
+                "access_type": "ALL",
+                "rows": 7,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `derived1`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `derived1`.`col_varchar_nokey`)) where <in_optimizer>((`derived1`.`col_varchar_nokey`,`derived1`.`col_varchar_nokey`),(`derived1`.`col_varchar_nokey`,`derived1`.`col_varchar_nokey`) in ( <materialize> (/* select#3 */ select `test`.`derived2`.`col_varchar_nokey`,`test`.`derived2`.`col_varchar_key` from `test`.`t2` `derived2` ), <primary_index_lookup>(`derived1`.`col_varchar_nokey` in <temporary table> on distinct_key where ((`derived1`.`col_varchar_nokey` = `materialized subselect`.`col_varchar_nokey`) and (`derived1`.`col_varchar_nokey` = `materialized subselect`.`col_varchar_key`)))))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+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-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2012-05-23 11:12:19 +0000
@@ -9805,5 +9805,118 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "derived1",
+          "access_type": "ALL",
+          "rows": 7,
+          "filtered": 100,
+          "attached_condition": "<in_optimizer>((`derived1`.`col_varchar_nokey`,`derived1`.`col_varchar_nokey`),<exists>(select `test`.`derived2`.`col_varchar_nokey`,`test`.`derived2`.`col_varchar_key` from `test`.`t2` `derived2` where ((<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_nokey`) and (<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_key`))))",
+          "attached_subqueries": [
+            {
+              "dependent": true,
+              "cacheable": false,
+              "query_block": {
+                "select_id": 3,
+                "table": {
+                  "table_name": "derived2",
+                  "access_type": "index_subquery",
+                  "possible_keys": [
+                    "col_varchar_key"
+                  ],
+                  "key": "col_varchar_key",
+                  "key_length": "4",
+                  "ref": [
+                    "func"
+                  ],
+                  "rows": 2,
+                  "filtered": 100,
+                  "attached_condition": "(<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_nokey`)"
+                }
+              }
+            }
+          ],
+          "materialized_from_subquery": {
+            "using_temporary_table": true,
+            "dependent": false,
+            "cacheable": true,
+            "query_block": {
+              "select_id": 2,
+              "table": {
+                "table_name": "t2",
+                "access_type": "ALL",
+                "rows": 7,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `derived1`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `derived1`.`col_varchar_nokey`)) where <in_optimizer>((`derived1`.`col_varchar_nokey`,`derived1`.`col_varchar_nokey`),<exists>(<index_lookup>(<cache>(`derived1`.`col_varchar_nokey`) in t2 on col_varchar_key where ((<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_nokey`) and (<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_key`)))))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+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-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result	2012-05-23 11:12:19 +0000
@@ -9806,6 +9806,119 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "derived1",
+          "access_type": "ALL",
+          "rows": 7,
+          "filtered": 100,
+          "attached_condition": "<in_optimizer>((`derived1`.`col_varchar_nokey`,`derived1`.`col_varchar_nokey`),<exists>(select `test`.`derived2`.`col_varchar_nokey`,`test`.`derived2`.`col_varchar_key` from `test`.`t2` `derived2` where ((<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_nokey`) and (<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_key`))))",
+          "attached_subqueries": [
+            {
+              "dependent": true,
+              "cacheable": false,
+              "query_block": {
+                "select_id": 3,
+                "table": {
+                  "table_name": "derived2",
+                  "access_type": "index_subquery",
+                  "possible_keys": [
+                    "col_varchar_key"
+                  ],
+                  "key": "col_varchar_key",
+                  "key_length": "4",
+                  "ref": [
+                    "func"
+                  ],
+                  "rows": 2,
+                  "filtered": 100,
+                  "attached_condition": "(<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_nokey`)"
+                }
+              }
+            }
+          ],
+          "materialized_from_subquery": {
+            "using_temporary_table": true,
+            "dependent": false,
+            "cacheable": true,
+            "query_block": {
+              "select_id": 2,
+              "table": {
+                "table_name": "t2",
+                "access_type": "ALL",
+                "rows": 7,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `derived1`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `derived1`.`col_varchar_nokey`)) where <in_optimizer>((`derived1`.`col_varchar_nokey`,`derived1`.`col_varchar_nokey`),<exists>(<index_lookup>(<cache>(`derived1`.`col_varchar_nokey`) in t2 on col_varchar_key where ((<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_nokey`) and (<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_key`)))))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2012-05-23 11:12:19 +0000
@@ -9805,6 +9805,118 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "derived1",
+          "access_type": "ALL",
+          "rows": 7,
+          "filtered": 100,
+          "attached_condition": "<in_optimizer>((`derived1`.`col_varchar_nokey`,`derived1`.`col_varchar_nokey`),<exists>(select `test`.`derived2`.`col_varchar_nokey`,`test`.`derived2`.`col_varchar_key` from `test`.`t2` `derived2` where ((<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_nokey`) and (<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_key`))))",
+          "attached_subqueries": [
+            {
+              "dependent": true,
+              "cacheable": false,
+              "query_block": {
+                "select_id": 3,
+                "table": {
+                  "table_name": "derived2",
+                  "access_type": "index_subquery",
+                  "possible_keys": [
+                    "col_varchar_key"
+                  ],
+                  "key": "col_varchar_key",
+                  "key_length": "4",
+                  "ref": [
+                    "func"
+                  ],
+                  "rows": 2,
+                  "filtered": 100,
+                  "attached_condition": "(<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_nokey`)"
+                }
+              }
+            }
+          ],
+          "materialized_from_subquery": {
+            "using_temporary_table": true,
+            "dependent": false,
+            "cacheable": true,
+            "query_block": {
+              "select_id": 2,
+              "table": {
+                "table_name": "t2",
+                "access_type": "ALL",
+                "rows": 7,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `derived1`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `derived1`.`col_varchar_nokey`)) where <in_optimizer>((`derived1`.`col_varchar_nokey`,`derived1`.`col_varchar_nokey`),<exists>(<index_lookup>(<cache>(`derived1`.`col_varchar_nokey`) in t2 on col_varchar_key where ((<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_nokey`) and (<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_key`)))))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+j	j
+v	v
+v	v
+c	c
+m	m
+d	d
+d	d
+y	y
+y	y
+y	y
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result	2012-05-18 09:50:03 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result	2012-05-23 11:12:19 +0000
@@ -9807,6 +9807,119 @@ DEALLOCATE PREPARE stmt;
 DROP VIEW view_t1;
 DROP TABLE t1;
 # End of test for bug#14048292.
+#
+# Bug#14064201: Missing data on join of derived table + WHERE .. IN
+#               with two operands
+#
+CREATE TABLE t1 (
+col_varchar_nokey VARCHAR(1)
+);
+INSERT INTO t1 VALUES
+('v'), ('s'), ('l'), ('y'), ('c'), ('i'), ('h'), ('q'), ('a'), ('v'),
+('u'), ('s'), ('y'), ('z'), ('h'), ('p'), ('e'), ('i'), ('y'), ('w');
+CREATE TABLE t2 (
+col_varchar_key VARCHAR(1),
+col_varchar_nokey VARCHAR(1),
+KEY col_varchar_key(col_varchar_key)
+);
+INSERT INTO t2 VALUES
+('j','j'), ('v','v'), ('c','c'), ('m','m'), ('d','d'), ('d','d'), ('y','y');
+explain format=json SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+EXPLAIN
+{
+  "query_block": {
+    "select_id": 1,
+    "nested_loop": [
+      {
+        "table": {
+          "table_name": "derived1",
+          "access_type": "ALL",
+          "rows": 7,
+          "filtered": 100,
+          "attached_condition": "<in_optimizer>((`derived1`.`col_varchar_nokey`,`derived1`.`col_varchar_nokey`),<exists>(select `test`.`derived2`.`col_varchar_nokey`,`test`.`derived2`.`col_varchar_key` from `test`.`t2` `derived2` where ((<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_nokey`) and (<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_key`))))",
+          "attached_subqueries": [
+            {
+              "dependent": true,
+              "cacheable": false,
+              "query_block": {
+                "select_id": 3,
+                "table": {
+                  "table_name": "derived2",
+                  "access_type": "index_subquery",
+                  "possible_keys": [
+                    "col_varchar_key"
+                  ],
+                  "key": "col_varchar_key",
+                  "key_length": "4",
+                  "ref": [
+                    "func"
+                  ],
+                  "rows": 2,
+                  "filtered": 100,
+                  "attached_condition": "(<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_nokey`)"
+                }
+              }
+            }
+          ],
+          "materialized_from_subquery": {
+            "using_temporary_table": true,
+            "dependent": false,
+            "cacheable": true,
+            "query_block": {
+              "select_id": 2,
+              "table": {
+                "table_name": "t2",
+                "access_type": "ALL",
+                "rows": 7,
+                "filtered": 100
+              }
+            }
+          }
+        }
+      },
+      {
+        "table": {
+          "table_name": "t1",
+          "access_type": "ALL",
+          "rows": 20,
+          "filtered": 100,
+          "using_join_buffer": "Block Nested Loop",
+          "attached_condition": "trigcond_if(is_not_null_compl(t1), (`test`.`t1`.`col_varchar_nokey` = `derived1`.`col_varchar_nokey`), true)"
+        }
+      }
+    ]
+  }
+}
+Warnings:
+Note	1003	/* select#1 */ select `derived1`.`col_varchar_nokey` AS `col_varchar_nokey`,`derived1`.`col_varchar_key` AS `col_varchar_key` from (/* select#2 */ select `test`.`t2`.`col_varchar_key` AS `col_varchar_key`,`test`.`t2`.`col_varchar_nokey` AS `col_varchar_nokey` from `test`.`t2`) `derived1` left join `test`.`t1` on((`test`.`t1`.`col_varchar_nokey` = `derived1`.`col_varchar_nokey`)) where <in_optimizer>((`derived1`.`col_varchar_nokey`,`derived1`.`col_varchar_nokey`),<exists>(<index_lookup>(<cache>(`derived1`.`col_varchar_nokey`) in t2 on col_varchar_key where ((<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_nokey`) and (<cache>(`derived1`.`col_varchar_nokey`) = `test`.`derived2`.`col_varchar_key`)))))
+SELECT *
+FROM (SELECT * FROM t2) AS derived1
+LEFT JOIN t1
+USING (col_varchar_nokey)
+WHERE (col_varchar_nokey, col_varchar_nokey) IN
+(SELECT col_varchar_nokey, col_varchar_key
+FROM t2 AS derived2
+);
+col_varchar_nokey	col_varchar_key
+v	v
+y	y
+c	c
+v	v
+y	y
+y	y
+j	j
+m	m
+d	d
+d	d
+DROP TABLE t1, t2;
+# End of test for bug#14064201.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2012-05-22 13:07:27 +0000
+++ b/sql/sql_optimizer.cc	2012-05-23 11:12:19 +0000
@@ -1935,14 +1935,16 @@ static Item *eliminate_item_equal(Item *
         }
         /*
           If the field belongs to a semi-join nest that is used for
-          MaterializeLookup and was rejected due to being covered by an upper-
-          level multiple equality, the upper-level multiple equality may
-          refer to tables that are outside of the materialized semi-join nest.
-          We play it safe and generate the equality predicate regardless.
+          Materialization and was rejected due to being covered by an upper-
+          level multiple equality, and the upper-level multiple equality
+          refers to tables that are outside of the materialized semi-join nest,
+          generate the equality predicate regardless.
         */
         if (item != item_field &&
             sj_is_materialize_strategy(
-              item_field->field->table->reginfo.join_tab->get_sj_strategy()))
+              item_field->field->table->reginfo.join_tab->get_sj_strategy()) &&
+            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:3910 to 3911) Bug#14064201	Roy Lyseng	23 May