List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:October 24 2011 2:01pm
Subject:bzr push into mysql-trunk branch (roy.lyseng:3467 to 3468) Bug#12664936
View as plain text  
 3468 Roy Lyseng	2011-10-24
      Bug#12664936: Same query executed as where subquery gives different
                    results on in() compare #2
      
      Step 2 of 2 - Bugfix
      
      The problem here is that ref access is set up wrongly for a table
      inside a materialized semi-join nest. The reference is made to a table
      outside of the semi-join nest, which obviously is not available when
      performing the materialization.
      
      However, best_access_path() has successfully found a ref access pattern
      based on a table inside the semi-join nest, the problem is only that
      create_ref_for_key() gets invalid used_tables data when setting up the
      ref access. Instead of considering all tables that have been traversed
      already in get_best_combination(), we need to ignore tables that are
      outside of the semi-join nest.
      
      mysql-test/include/subquery_sj.inc
        Added test for bug#12664936.
      
      mysql-test/r/subquery_sj_all.result
      mysql-test/r/subquery_sj_all_bka.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_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 results for bug#12664936.
      
      mysql-test/r/subquery_sj_all_bka_nixbnl.result
      mysql-test/r/subquery_sj_mat_bka_nixbnl.result
        Plan is corrected to not use a reference to an outside column
        in a materialized semi-join nest.
        Added test results for bug#12664936.
      
      sql/sql_select.cc
        In JOIN::set_access_methods(), calculated "available tables" as the
        set of used tables found inside the materialized semi-join nest.
        Added some cross references to other places where tables from
        outside the semi-join nest must be excluded.

    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_select.cc
 3467 Roy Lyseng	2011-10-24
      Bug#12664936: Same query executed as where subquery gives different
                    results on in() compare #2
      
      Step 1 of 2 - Refactoring
      
      This is a local refactoring done before the actual bugfix.
      It makes get_best_combination() a public member function of class
      JOIN, and splits the main work over a few smaller functions.
      It also changes the order of some evaluations so that compiled
      semi-join information is now available in the JOIN_TAB objects at
      an earlier stage.
      get_best_combination() should actually be private, because it is
      only called from make_join_statistics(), which is a free function
      that should also be a private member function of class JOIN.
      
      sql/sql_select.cc
        get_best_combination() is refactored according to pseudo code:
         - Fix semi-join strategies after table order selection.
         - Create the new JOIN_TAB array.
         - A loop that initializes this JOIN_TAB array.
         - A function set_semijoin_info() that sets semi-join information
           for the selected table order in the JOIN_TAB array.
         - A function set_access_methods() that sets up access methods
           based on key information.
         - Update dependencies between tables based on the selected
           key access patterns.
      
      sql/sql_select.h
        Defined get_best_combination() as public member function of JOIN.
        Created set_semijoin_info() and set_access_methods() as private
        member functions of class JOIN. 

    modified:
      sql/sql_select.cc
      sql/sql_select.h
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2011-10-07 12:44:17 +0000
+++ b/mysql-test/include/subquery_sj.inc	2011-10-24 14:01:11 +0000
@@ -4241,4 +4241,49 @@ DROP VIEW v1;
 
 --echo # End of test for bug#12711441.
 
+--echo #
+--echo # Bug#12664936: Same query executed as where subquery ...
+--echo #
+
+CREATE TABLE t1 (
+  col_varchar_key VARCHAR(1),
+  KEY col_varchar_key (col_varchar_key)
+);
+
+INSERT INTO t1 VALUES
+ ('o'), ('w'), ('m'), ('q'),
+ ('f'), ('p'), ('j'), ('c');
+
+CREATE TABLE t2 (
+  col_int_nokey INTEGER,
+  col_int_key INTEGER,
+  col_varchar_key varchar(1),
+  KEY col_int_key (col_int_key)
+);
+
+INSERT INTO t2 VALUES
+ (8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+ (1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+
+let $query=
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+   (SELECT t2.col_int_nokey, t2.col_varchar_key
+    FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+    WHERE t2.col_int_key = 1
+);
+
+eval EXPLAIN $query;
+eval $query;
+
+DROP TABLE t1, t2, t4;
+
+--echo # End of test for bug#12664936.
+
 --echo # End of 5.6 tests

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2011-10-24 14:01:11 +0000
@@ -6902,5 +6902,51 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; Start materialize
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index; End materialize
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bka.result'
--- a/mysql-test/r/subquery_sj_all_bka.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result	2011-10-24 14:01:11 +0000
@@ -6903,6 +6903,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; Start materialize
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index; End materialize
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2011-10-24 14:01:11 +0000
@@ -3179,12 +3179,12 @@ insert into t1 select A.a, B.a, 'filler'
 create table t2 as select * from t1;
 explain select * from t2 where a in (select b from t1 where a=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	PRIMARY	t1	ref	a	a	10	const,test.t2.a	8	Using index; Materialize
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	
+1	PRIMARY	t1	ref	a	a	5	const	8	Using index; Materialize
 explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	PRIMARY	t1	ref	a	a	10	const,test.t2.a	8	Using index; Materialize
+1	PRIMARY	t1	ref	a	a	5	const	8	Using index; Materialize
 drop table t1,t2;
 create table t1 (a int, b int);
 insert into t1 select a,a from t0;
@@ -6903,6 +6903,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; Start materialize
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index; End materialize
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_all_bkaunique.result'
--- a/mysql-test/r/subquery_sj_all_bkaunique.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result	2011-10-24 14:01:11 +0000
@@ -6904,6 +6904,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; Start materialize
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index; End materialize
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed.result'
--- a/mysql-test/r/subquery_sj_dupsweed.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2011-10-24 14:01:11 +0000
@@ -6902,5 +6902,51 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t4.col_varchar_key	2	Using index; Start temporary
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; End temporary
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result	2011-10-24 14:01:11 +0000
@@ -6903,6 +6903,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t4.col_varchar_key	2	Using index
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; End temporary
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2011-10-24 14:01:11 +0000
@@ -6903,6 +6903,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t4.col_varchar_key	2	Using index
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; End temporary
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result'
--- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2011-10-24 14:01:11 +0000
@@ -6904,6 +6904,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t4.col_varchar_key	2	Using index
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; End temporary
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_firstmatch.result'
--- a/mysql-test/r/subquery_sj_firstmatch.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2011-10-24 14:01:11 +0000
@@ -6903,6 +6903,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t4.col_varchar_key	2	Using index
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; FirstMatch(t4)
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result	2011-10-24 14:01:11 +0000
@@ -6904,6 +6904,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t4.col_varchar_key	2	Using index
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; FirstMatch(t4)
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2011-10-24 14:01:11 +0000
@@ -6904,6 +6904,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t4.col_varchar_key	2	Using index
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; FirstMatch(t4)
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result'
--- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2011-10-24 14:01:11 +0000
@@ -6905,6 +6905,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t4.col_varchar_key	2	Using index
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; FirstMatch(t4)
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 #
 # Bug#51457 Firstmatch semijoin strategy gives wrong results for

=== modified file 'mysql-test/r/subquery_sj_loosescan.result'
--- a/mysql-test/r/subquery_sj_loosescan.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2011-10-24 14:01:11 +0000
@@ -6903,5 +6903,51 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t4.col_varchar_key	2	Using index; Start temporary
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; End temporary
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result	2011-10-24 14:01:11 +0000
@@ -6904,6 +6904,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t4.col_varchar_key	2	Using index
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; End temporary
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2011-10-24 14:01:11 +0000
@@ -6904,6 +6904,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t4.col_varchar_key	2	Using index
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; End temporary
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result'
--- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2011-10-24 14:01:11 +0000
@@ -6905,6 +6905,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where; Start temporary
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t4.col_varchar_key	2	Using index
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; End temporary
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat.result'
--- a/mysql-test/r/subquery_sj_mat.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2011-10-24 14:01:11 +0000
@@ -6902,5 +6902,51 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; Start materialize
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index; End materialize
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bka.result'
--- a/mysql-test/r/subquery_sj_mat_bka.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result	2011-10-24 14:01:11 +0000
@@ -6903,6 +6903,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; Start materialize
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index; End materialize
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2011-10-24 14:01:11 +0000
@@ -3179,12 +3179,12 @@ insert into t1 select A.a, B.a, 'filler'
 create table t2 as select * from t1;
 explain select * from t2 where a in (select b from t1 where a=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	PRIMARY	t1	ref	a	a	10	const,test.t2.a	8	Using index; Materialize
+1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	
+1	PRIMARY	t1	ref	a	a	5	const	8	Using index; Materialize
 explain select * from t2 where (b,a) in (select a,b from t1 where a=3);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	100	Using where
-1	PRIMARY	t1	ref	a	a	10	const,test.t2.a	8	Using index; Materialize
+1	PRIMARY	t1	ref	a	a	5	const	8	Using index; Materialize
 drop table t1,t2;
 create table t1 (a int, b int);
 insert into t1 select a,a from t0;
@@ -6903,6 +6903,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; Start materialize
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index; End materialize
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result'
--- a/mysql-test/r/subquery_sj_mat_bkaunique.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result	2011-10-24 14:01:11 +0000
@@ -6904,6 +6904,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	
+1	PRIMARY	t2	ref	col_int_key	col_int_key	5	const	3	Using where; Start materialize
+1	PRIMARY	t1	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index; End materialize
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_mat_nosj.result'
--- a/mysql-test/r/subquery_sj_mat_nosj.result	2011-10-19 08:13:00 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2011-10-24 14:01:11 +0000
@@ -6979,5 +6979,51 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	SUBQUERY	t2	ref	col_int_key	col_int_key	5	const	3	Using where
+2	SUBQUERY	t1	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none.result'
--- a/mysql-test/r/subquery_sj_none.result	2011-10-18 10:23:09 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2011-10-24 14:01:11 +0000
@@ -6914,5 +6914,51 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2	ref	col_int_key	col_int_key	5	const	3	Using where
+2	DEPENDENT SUBQUERY	t1	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bka.result'
--- a/mysql-test/r/subquery_sj_none_bka.result	2011-10-19 06:26:28 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result	2011-10-24 14:01:11 +0000
@@ -6915,6 +6915,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2	ref	col_int_key	col_int_key	5	const	3	Using where
+2	DEPENDENT SUBQUERY	t1	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result'
--- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2011-10-19 06:26:28 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2011-10-24 14:01:11 +0000
@@ -6915,6 +6915,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2	ref	col_int_key	col_int_key	5	const	3	Using where
+2	DEPENDENT SUBQUERY	t1	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/subquery_sj_none_bkaunique.result'
--- a/mysql-test/r/subquery_sj_none_bkaunique.result	2011-10-19 06:26:28 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result	2011-10-24 14:01:11 +0000
@@ -6916,6 +6916,52 @@ field1
 DROP TABLE t1, t2, t3, t4, ts;
 DROP VIEW v1;
 # End of test for bug#12711441.
+#
+# Bug#12664936: Same query executed as where subquery ...
+#
+CREATE TABLE t1 (
+col_varchar_key VARCHAR(1),
+KEY col_varchar_key (col_varchar_key)
+);
+INSERT INTO t1 VALUES
+('o'), ('w'), ('m'), ('q'),
+('f'), ('p'), ('j'), ('c');
+CREATE TABLE t2 (
+col_int_nokey INTEGER,
+col_int_key INTEGER,
+col_varchar_key varchar(1),
+KEY col_int_key (col_int_key)
+);
+INSERT INTO t2 VALUES
+(8,5,'u'),(4,5,'p'),(8,1,'o'),(NULL,7,'v'),
+(1,2,'g'),(2,1,'q'),(NULL,7,'l'),(3,1,'n');
+CREATE TABLE t4
+SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1;
+EXPLAIN SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t4	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DEPENDENT SUBQUERY	t2	ref	col_int_key	col_int_key	5	const	3	Using where
+2	DEPENDENT SUBQUERY	t1	ref	col_varchar_key	col_varchar_key	4	test.t2.col_varchar_key	2	Using index
+SELECT *
+FROM t4
+WHERE (col_int_nokey, col_varchar_key) IN
+(SELECT t2.col_int_nokey, t2.col_varchar_key
+FROM t1 JOIN t2 ON t2.col_varchar_key = t1.col_varchar_key
+WHERE t2.col_int_key = 1
+);
+col_int_nokey	col_varchar_key
+8	o
+2	q
+DROP TABLE t1, t2, t4;
+# End of test for bug#12664936.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-10-24 13:25:03 +0000
+++ b/sql/sql_select.cc	2011-10-24 14:01:11 +0000
@@ -10073,7 +10073,15 @@ bool JOIN::set_access_methods()
      }
     else
     {
-      if (create_ref_for_key(this, tab, keyuse, used_tables))
+      /*
+        In a materialized semi-join nest, only the inner tables are available.
+        @see make_join_select()
+        @see Item_equal::get_subst_item()
+      */
+      const table_map available_tables=
+        sj_is_materialize_strategy(tab->get_sj_strategy()) ?
+          used_tables & tab->emb_sj_nest->sj_inner_tables : used_tables;
+      if (create_ref_for_key(this, tab, keyuse, available_tables))
         DBUG_RETURN(true);
     }
    }
@@ -10927,6 +10935,8 @@ static bool make_join_select(JOIN *join,
         conditions referring to preceding non-const tables.
          - If we're looking at the first SJM table, reset used_tables
            to refer to only allowed tables
+        @see create_ref_for_key()
+        @see Item_equal::get_subst_item()
       */
       if (sj_is_materialize_strategy(tab->get_sj_strategy()) &&
           !(used_tables & tab->emb_sj_nest->sj_inner_tables))
@@ -14362,6 +14372,8 @@ Item *eliminate_item_equal(Item *cond, C
         against the first item within the SJM nest (if the item is not the first
         item within the SJM nest), or match against the first item in the
         list (if the item is the first one in the SJM nest).
+        @see create_ref_for_key()
+        @see make_join_select()
       */
       head= item_const ? item_const : item_equal->get_subst_item(item_field);
       if (head == item_field)                   // First item in SJM nest

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3467 to 3468) Bug#12664936Roy Lyseng25 Oct