List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:January 20 2012 4:06pm
Subject:bzr push into mysql-trunk branch (roy.lyseng:3529 to 3530) Bug#13596176
View as plain text  
 3530 Roy Lyseng	2012-01-20
      Bug#13596176: Missing row on select with nested in clause when matr=on
                    and bnl=off + MyISAM
      
      This is a case where Materialize-scan semi-join strategy is selected,
      and ref access is used to look up a row in the outer table.
      The referenced field in the outer table belongs in a multiple equality
      together with three fields from the inner tables.
      
      setup_sj_materialization() sets up objects to copy fields from the
      materialized tables back to original field objects. For a multiple
      equality, a "best" field is picked for this, the remaining fields
      are not copied. This best field coincides with fields used by regular
      conditions, but not necessarily with fields used for ref access.
      
      The reason for this is that before fields are substituted within
      regular conditions (see substitute_for_best_equal_field()), multiple
      equality objects are sorted so that fields come in table order.
      This sorting has not been done when calling set_access_methods(),
      which sets up ref access.
      
      The solution to the problem is to execute set_access_methods() after
      the multiple equalities have been sorted, and use the same algorithm
      as setup_sj_materialization() uses to pick the proper field for
      ref access.
      
      A pleasant side effect of this fix is also that
      make_cond_for_table_from_pred() now does a better job in replacing
      equality predicates that are also covered by ref accesses.
      
      mysql-test/include/subquery_sj.inc
        Added test case for bug#13596176.
      
      mysql-test/r/derived.result
      mysql-test/r/greedy_optimizer.result
      mysql-test/r/join_cache_bka.result
      mysql-test/r/join_cache_bka_nixbnl.result
      mysql-test/r/join_cache_bkaunique.result
      mysql-test/r/join_cache_bnl.result
      mysql-test/r/join_cache_nojb.result
      mysql-test/r/join_nested.result
      mysql-test/r/join_nested_bka.result
      mysql-test/r/join_nested_bka_nixbnl.result
      mysql-test/suite/opt_trace/r/general2_no_prot.result
      mysql-test/suite/opt_trace/r/general2_ps_prot.result
        Updated plans. Mostly these are removed "Using where" and
        "Using index condition", which means that a condition has been
        correctly identified as ref access and removed.
      
      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_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#13596176, plus some updated plans.
      
      sql/sql_optimizer.cc
        Added function get_best_field() that picks the "best" field from
        a multiple equality.
        Calls set_access_methods() after multiple equalities have been sorted.
        Calls drop_unused_derived_keys() after ref access has been set up.
        Calls update_depend_map() after ref access has been set up.
        Calls initialize_tables() after ref access has been set up.
      
      sql/sql_optimizer.h
        Added definition for function get_best_field().
      
      sql/sql_select.cc
        Calls get_best_field() in create_ref_for_key() and
        setup_sj_materialization() to get the "best" field.
        Call to set_access_methods() and update_depend_map() moved to a more
        appropriate place.

    modified:
      mysql-test/include/subquery_sj.inc
      mysql-test/r/derived.result
      mysql-test/r/greedy_optimizer.result
      mysql-test/r/join_cache_bka.result
      mysql-test/r/join_cache_bka_nixbnl.result
      mysql-test/r/join_cache_bkaunique.result
      mysql-test/r/join_cache_bnl.result
      mysql-test/r/join_cache_nojb.result
      mysql-test/r/join_nested.result
      mysql-test/r/join_nested_bka.result
      mysql-test/r/join_nested_bka_nixbnl.result
      mysql-test/r/subquery_mat.result
      mysql-test/r/subquery_mat_all.result
      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
      mysql-test/suite/opt_trace/r/general2_no_prot.result
      mysql-test/suite/opt_trace/r/general2_ps_prot.result
      sql/sql_optimizer.cc
      sql/sql_optimizer.h
      sql/sql_select.cc
 3529 Guilhem Bichot	2012-01-19
      Fix for Bug#13596330 - EXTRA ROW ON SELECT WITH NESTED IN CLAUSE
      + IS NULL WHEN SEMIJOIN + BNL IS ON :
      table->status must say "there is a record" when we process
      records from the join buffer.
     @ mysql-test/r/join_cache_bnl.result
        without the code fix, SELECT would return 'e'
     @ sql/sql_join_buffer.cc
        Scenario of the bug follows (see query in join_cache.inc).
        Records are read from PARENT1 (the first record having
        GRANDPARENT1.col_varchar_key='e'), and buffered into the join buffer
        of t1. When the end of PARENT1 is reached (table->status
        set to "no record"), buffered records are "flushed"
        (JOIN_CACHE::join_records()),
        which triggers execution of the most inner subquery (with IN->EXISTS
        and indexsubquery_engine); the first step of that is JOIN::optimize();
        IS NULL is first converted to "=the_zero_datetime" (this is a "feature"
        of DATETIME...) and then convert_constant_item() tries to optimize
        this equality to a comparison of longlongs. This last function does
        this:
        a) backup the value of the left member (the DATETIME Field)
        b) store the right member (an Item, the zero datetime) into the left
        member
        c) if this doesn't cause truncation, decide that the comparison can be
        done as longlongs
        d) restore the value saved in (a)
        But (a)(d) are done only if there was a value to preserve, i.e. if there
        was a record (into which Field::ptr points); the existence of a record
        is determined by testing TABLE::status (per the fix for Bug 37870;
        search for save_field_value in convert_constant_item()).
        In our scenario, given that PARENT1 has 'status' with STATUS_NOT_FOUND,
        (a)(d) doesn't happen. So, the record gets modified, ending up with "0"
        in PARENT1.col_datetime_nokey, thus the WHERE clause of the (most
        inner) subquery is satisfied, and 'e' is put in the result set.
        Wrongly.
        
        The fix: when we bring a record from the join buffer, we put it
        in the table's record[0], and thus, table->status must say that
        there is a record in record[0]; so it must not have STATUS_NOT_FOUND
        and STATUS_GARBAGE. The status of the last real-read-from-table does
        not matter for the join buffering code, as this read was superseded by a
        read-from-cache. So we save/restore the status.
        
        Note that the fix for BUG 11766522 removed the save/restore logic
        because:
        - it was absurd
        - it was buggy (not re-entrant).
        Well, you live and learn... it was not absurd. The new implementation
        puts back the save/restore logic, making it re-entrant by using a
        per-join-buffer backup of TABLE::status, local to
        join_records() instead of join_matching_records().
        
        Below some notes of investigations made.
        
        * Why do we restore the status afterwards? Based on the scenario of
        BUG 11766522 (see commit comments of that bugfix), we could imagine this
        in a t1-t2 scenario:
         read_from_t1, EOF # turns on STATUS_NOT_FOUND
         call sub_select_cache() # for t2, to flush buffered records; sets status of t1&t2 to 0
         if (t1 has STATUS_NOT_FOUND) etc; # i.e. this uses status to remember that it hit EOF above
        If we didn't restore the status, the final if() would not be entered.
        
        * Do we need a per-buffered-record status?
        a) Certain flags of TABLE::status (STATUS_GARBAGE, STATUS_NOT_FOUND)
        describe the presence of a record; those simply have to be restored
        at end as we do. Indeed:
         - if there was no record in table->record[0] when we started flushing
         of inner table's buffer: given that we restore this "no record"
         information at end of flushing, it's correct
         - if there was a record when flushing started: after flushing of
         buffered records, we have in table->record[0] what there was before
         flushing started (we simply made an excursion through older records
         but ended with the current one).
        b) On the other hand, other flags describe the record: STATUS_NOT_NULL,
        STATUS_UPDATED/DELETED. In theory, we could need to store their value
        per buffered record (like we do for table->null_row, see
        add_flag_field_to_join_cache()). But in fact, there is no need, because:
        - STATUS_UPDATED/DELETED serve only for multi-update/delete, which
        never use join buffering (btw see BUG 13602655)
        - STATUS_NULL_ROW is not used while evaluating records (so,
        not inside join_records()), only in join_read_key2(): this function
        needs the status of the last read-from-index record; and as said in
        point (a) above, the last-record information is properly restored.
     @ sql/sql_join_buffer.h
        Changed alloc_buffer() to bool.
     @ sql/table.h
        Reduce 'status' from uint to uint8; it's ok, I checked all read/writes
        of this member.
        Move copy_blobs into the my_bool section.
        Move status into the my_bool section.
        All this gives better padding, I verified that it saves 8 bytes on
        sizeof(TABLE).

    modified:
      mysql-test/include/join_cache.inc
      mysql-test/r/join_cache_bka.result
      mysql-test/r/join_cache_bka_nixbnl.result
      mysql-test/r/join_cache_bkaunique.result
      mysql-test/r/join_cache_bnl.result
      mysql-test/r/join_cache_nojb.result
      sql/sql_join_buffer.cc
      sql/sql_join_buffer.h
      sql/structs.h
      sql/table.h
=== modified file 'mysql-test/include/subquery_sj.inc'
--- a/mysql-test/include/subquery_sj.inc	2012-01-16 12:51:06 +0000
+++ b/mysql-test/include/subquery_sj.inc	2012-01-20 15:30:14 +0000
@@ -4819,4 +4819,48 @@ eval $query;
 
 DROP TABLE t1,t2,t3;
 
+--echo #
+--echo # Bug #13596176: Missing row on select with nested in clause when
+--echo #                matr=on and bnl=off + MyISAM
+--echo #
+
+CREATE TABLE t1 (
+  int_key int DEFAULT NULL,
+  vc_key varchar(1) DEFAULT NULL,
+  vc_nokey varchar(1) DEFAULT NULL,
+  KEY int_key (int_key),
+  KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+
+INSERT INTO t1 VALUES
+ (8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+ (9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+ (2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+ (0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+ (NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+
+CREATE TABLE t2 (
+  int_key int DEFAULT NULL,
+  vc_key varchar(1) DEFAULT NULL,
+  KEY int_key (int_key),
+  KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+
+INSERT INTO t2 VALUES (8,'g');
+
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+ (SELECT vc_nokey, vc_nokey
+  FROM t1 middle
+  WHERE vc_nokey IN
+   (SELECT child1.vc_key
+    FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+   )
+ );
+
+DROP TABLE t1, t2;
+
+--echo # End of test for bug#13596176.
+
 --echo # End of 5.6 tests

=== modified file 'mysql-test/r/derived.result'
--- a/mysql-test/r/derived.result	2012-01-18 13:53:00 +0000
+++ b/mysql-test/r/derived.result	2012-01-20 15:30:14 +0000
@@ -845,8 +845,8 @@ EXPLAIN EXTENDED
 SELECT * FROM t1,v3 AS v31,v3 WHERE t1.f1=v31.f1 and t1.f1=v3.f1;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	Using where
-1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	5	test.t1.f1	3	100.00	Using where
-1	PRIMARY	<derived3>	ref	auto_key0	auto_key0	5	v31.f1	3	100.00	Using where
+1	PRIMARY	<derived2>	ref	auto_key0	auto_key0	5	test.t1.f1	3	100.00	
+1	PRIMARY	<derived3>	ref	auto_key0	auto_key0	5	test.t1.f1	3	100.00	
 3	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	
 3	DERIVED	t11	ALL	NULL	NULL	NULL	NULL	6	100.00	Using join buffer (Block Nested Loop)
 2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	6	100.00	

=== modified file 'mysql-test/r/greedy_optimizer.result'
--- a/mysql-test/r/greedy_optimizer.result	2011-08-03 11:29:20 +0000
+++ b/mysql-test/r/greedy_optimizer.result	2012-01-20 15:30:14 +0000
@@ -1068,7 +1068,7 @@ AND t10000.K=t10.I;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t10	ALL	NULL	NULL	NULL	NULL	10	Using where
 1	SIMPLE	t100	eq_ref	PRIMARY	PRIMARY	4	test.t10.I	1	Using index
-1	SIMPLE	t10000	eq_ref	PRIMARY	PRIMARY	4	test.t100.K	1	Using where; Using index
+1	SIMPLE	t10000	eq_ref	PRIMARY	PRIMARY	4	test.t10.I	1	Using index
 SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t100,t10000
 WHERE t100.K=t10.I
 AND t10000.K=t10.I;
@@ -1094,7 +1094,7 @@ AND t10000.K=t10.I;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t10	ALL	NULL	NULL	NULL	NULL	10	Using where
 1	SIMPLE	t100	eq_ref	PRIMARY	PRIMARY	4	test.t10.I	1	Using index
-1	SIMPLE	t10000	eq_ref	PRIMARY	PRIMARY	4	test.t100.K	1	Using where; Using index
+1	SIMPLE	t10000	eq_ref	PRIMARY	PRIMARY	4	test.t10.I	1	Using index
 SELECT COUNT(*) FROM t10,t100,t10000
 WHERE t100.K=t10.I
 AND t10000.K=t10.I;
@@ -1107,7 +1107,7 @@ AND t10000.K=t10.I;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t10	ALL	NULL	NULL	NULL	NULL	10	Using where
 1	SIMPLE	t100	eq_ref	PRIMARY	PRIMARY	4	test.t10.I	1	Using index
-1	SIMPLE	t10000	eq_ref	PRIMARY	PRIMARY	4	test.t100.K	1	Using where; Using index
+1	SIMPLE	t10000	eq_ref	PRIMARY	PRIMARY	4	test.t10.I	1	Using index
 SELECT COUNT(*) FROM t10,t10000,t100
 WHERE t100.K=t10.I
 AND t10000.K=t10.I;
@@ -1429,7 +1429,7 @@ AND t10000.I=t10.I;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t10	index	IX	IX	5	NULL	10	Using where; Using index
 1	SIMPLE	t100	eq_ref	PRIMARY	PRIMARY	4	test.t10.I	1	Using index
-1	SIMPLE	t10000	ref	IX	IX	5	test.t100.K	1000	Using where; Using index
+1	SIMPLE	t10000	ref	IX	IX	5	test.t10.I	1000	Using index
 SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t100,t10000
 WHERE t100.K=t10.I
 AND t10000.I=t10.I;
@@ -1442,7 +1442,7 @@ AND t10000.I=t10.I;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t10	index	IX	IX	5	NULL	10	Using where; Using index
 1	SIMPLE	t100	eq_ref	PRIMARY	PRIMARY	4	test.t10.I	1	Using index
-1	SIMPLE	t10000	ref	IX	IX	5	test.t100.K	1000	Using where; Using index
+1	SIMPLE	t10000	ref	IX	IX	5	test.t10.I	1000	Using index
 SELECT COUNT(*) FROM t10,t100,t10000
 WHERE t100.K=t10.I
 AND t10000.I=t10.I;
@@ -1455,7 +1455,7 @@ AND t10000.I=t10.I;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t10	index	IX	IX	5	NULL	10	Using where; Using index
 1	SIMPLE	t100	eq_ref	PRIMARY	PRIMARY	4	test.t10.I	1	Using index
-1	SIMPLE	t10000	ref	IX	IX	5	test.t100.K	1000	Using where; Using index
+1	SIMPLE	t10000	ref	IX	IX	5	test.t10.I	1000	Using index
 SELECT COUNT(*) FROM t10,t10000,t100
 WHERE t100.K=t10.I
 AND t10000.I=t10.I;
@@ -1501,7 +1501,7 @@ AND Y.I=t10.I;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t10	index	IX	IX	5	NULL	10	Using where; Using index
 1	SIMPLE	X	eq_ref	PRIMARY	PRIMARY	4	test.t10.I	1	Using index
-1	SIMPLE	Y	ref	IX	IX	5	test.X.K	1000	Using where; Using index
+1	SIMPLE	Y	ref	IX	IX	5	test.t10.I	1000	Using index
 SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000 X,t10000 Y
 WHERE X.K=t10.I
 AND Y.I=t10.I;
@@ -1514,7 +1514,7 @@ AND Y.I=t10.I;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t10	index	IX	IX	5	NULL	10	Using where; Using index
 1	SIMPLE	X	eq_ref	PRIMARY	PRIMARY	4	test.t10.I	1	Using index
-1	SIMPLE	Y	ref	IX	IX	5	test.X.K	1000	Using where; Using index
+1	SIMPLE	Y	ref	IX	IX	5	test.t10.I	1000	Using index
 SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
 WHERE X.K=t10.I
 AND Y.I=t10.I;
@@ -1527,7 +1527,7 @@ AND Y.I=t10.I;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t10	index	IX	IX	5	NULL	10	Using where; Using index
 1	SIMPLE	X	eq_ref	PRIMARY	PRIMARY	4	test.t10.I	1	Using index
-1	SIMPLE	Y	ref	IX	IX	5	test.X.K	1000	Using where; Using index
+1	SIMPLE	Y	ref	IX	IX	5	test.t10.I	1000	Using index
 SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
 WHERE X.K=t10.I
 AND Y.I=t10.I;
@@ -1540,7 +1540,7 @@ AND Y.I=X.K;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t10	index	IX	IX	5	NULL	10	Using where; Using index
 1	SIMPLE	X	eq_ref	PRIMARY	PRIMARY	4	test.t10.I	1	Using index
-1	SIMPLE	Y	ref	IX	IX	5	test.X.K	1000	Using where; Using index
+1	SIMPLE	Y	ref	IX	IX	5	test.t10.I	1000	Using index
 SELECT STRAIGHT_JOIN COUNT(*) FROM t10,t10000 X,t10000 Y
 WHERE X.K=t10.I
 AND Y.I=X.K;
@@ -1553,7 +1553,7 @@ AND Y.I=X.K;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t10	index	IX	IX	5	NULL	10	Using where; Using index
 1	SIMPLE	X	eq_ref	PRIMARY	PRIMARY	4	test.t10.I	1	Using index
-1	SIMPLE	Y	ref	IX	IX	5	test.X.K	1000	Using where; Using index
+1	SIMPLE	Y	ref	IX	IX	5	test.t10.I	1000	Using index
 SELECT COUNT(*) FROM t10,t10000 X,t10000 Y
 WHERE X.K=t10.I
 AND Y.I=X.K;
@@ -1566,7 +1566,7 @@ AND Y.I=X.K;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t10	index	IX	IX	5	NULL	10	Using where; Using index
 1	SIMPLE	X	eq_ref	PRIMARY	PRIMARY	4	test.t10.I	1	Using index
-1	SIMPLE	Y	ref	IX	IX	5	test.X.K	1000	Using where; Using index
+1	SIMPLE	Y	ref	IX	IX	5	test.t10.I	1000	Using index
 SELECT COUNT(*) FROM t10,t10000 Y,t10000 X
 WHERE X.K=t10.I
 AND Y.I=X.K;

=== modified file 'mysql-test/r/join_cache_bka.result'
--- a/mysql-test/r/join_cache_bka.result	2012-01-19 09:57:18 +0000
+++ b/mysql-test/r/join_cache_bka.result	2012-01-20 15:30:14 +0000
@@ -477,7 +477,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition; Using where; Using join buffer (Batched Key Access)
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -784,7 +784,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition; Using where; Using join buffer (Batched Key Access)
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1308,7 +1308,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using join buffer (Batched Key Access)
 1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index
 1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	Using join buffer (Batched Key Access)
-1	SIMPLE	t9	ref	PRIMARY,t9_subgenreid,t9_metaid	t9_metaid	4	test.t7.metaid	2	Using index condition; Using join buffer (Batched Key Access)
+1	SIMPLE	t9	ref	PRIMARY,t9_subgenreid,t9_metaid	t9_metaid	4	test.t1.metaid	2	Using join buffer (Batched Key Access)
 1	SIMPLE	t10	eq_ref	PRIMARY,t10_genreid	PRIMARY	4	test.t9.subgenreid	1	Using join buffer (Batched Key Access)
 1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t10.genreid	1	Using join buffer (Batched Key Access)
 1	SIMPLE	t3	ALL	t3_metaid,t3_formatid,t3_metaidformatid	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)

=== modified file 'mysql-test/r/join_cache_bka_nixbnl.result'
--- a/mysql-test/r/join_cache_bka_nixbnl.result	2012-01-19 09:57:18 +0000
+++ b/mysql-test/r/join_cache_bka_nixbnl.result	2012-01-20 15:30:14 +0000
@@ -477,7 +477,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition; Using where; Using join buffer (Batched Key Access)
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -784,7 +784,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access)
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition; Using where; Using join buffer (Batched Key Access)
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access)
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1308,7 +1308,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using join buffer (Batched Key Access)
 1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index
 1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	Using join buffer (Batched Key Access)
-1	SIMPLE	t9	ref	PRIMARY,t9_subgenreid,t9_metaid	t9_metaid	4	test.t7.metaid	2	Using index condition; Using join buffer (Batched Key Access)
+1	SIMPLE	t9	ref	PRIMARY,t9_subgenreid,t9_metaid	t9_metaid	4	test.t1.metaid	2	Using join buffer (Batched Key Access)
 1	SIMPLE	t10	eq_ref	PRIMARY,t10_genreid	PRIMARY	4	test.t9.subgenreid	1	Using join buffer (Batched Key Access)
 1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t10.genreid	1	Using join buffer (Batched Key Access)
 1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaid	4	test.t1.metaid	2	Using where; Using join buffer (Batched Key Access)

=== modified file 'mysql-test/r/join_cache_bkaunique.result'
--- a/mysql-test/r/join_cache_bkaunique.result	2012-01-19 09:57:18 +0000
+++ b/mysql-test/r/join_cache_bkaunique.result	2012-01-20 15:30:14 +0000
@@ -478,7 +478,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access (unique))
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition; Using where; Using join buffer (Batched Key Access (unique))
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access (unique))
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -785,7 +785,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where; Using join buffer (Batched Key Access (unique))
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition; Using where; Using join buffer (Batched Key Access (unique))
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where; Using join buffer (Batched Key Access (unique))
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1309,7 +1309,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using join buffer (Batched Key Access (unique))
 1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index
 1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	Using join buffer (Batched Key Access (unique))
-1	SIMPLE	t9	ref	PRIMARY,t9_subgenreid,t9_metaid	t9_metaid	4	test.t7.metaid	2	Using index condition; Using join buffer (Batched Key Access (unique))
+1	SIMPLE	t9	ref	PRIMARY,t9_subgenreid,t9_metaid	t9_metaid	4	test.t1.metaid	2	Using join buffer (Batched Key Access (unique))
 1	SIMPLE	t10	eq_ref	PRIMARY,t10_genreid	PRIMARY	4	test.t9.subgenreid	1	Using join buffer (Batched Key Access (unique))
 1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t10.genreid	1	Using join buffer (Batched Key Access (unique))
 1	SIMPLE	t3	ALL	t3_metaid,t3_formatid,t3_metaidformatid	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)

=== modified file 'mysql-test/r/join_cache_bnl.result'
--- a/mysql-test/r/join_cache_bnl.result	2012-01-19 09:57:18 +0000
+++ b/mysql-test/r/join_cache_bnl.result	2012-01-20 15:30:14 +0000
@@ -478,7 +478,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition; Using where
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -785,7 +785,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition; Using where
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1309,7 +1309,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	
 1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index
 1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	
-1	SIMPLE	t9	ref	PRIMARY,t9_subgenreid,t9_metaid	t9_metaid	4	test.t7.metaid	2	Using index condition
+1	SIMPLE	t9	ref	PRIMARY,t9_subgenreid,t9_metaid	t9_metaid	4	test.t1.metaid	2	
 1	SIMPLE	t10	eq_ref	PRIMARY,t10_genreid	PRIMARY	4	test.t9.subgenreid	1	
 1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t10.genreid	1	
 1	SIMPLE	t3	ALL	t3_metaid,t3_formatid,t3_metaidformatid	NULL	NULL	NULL	2	Using where; Using join buffer (Block Nested Loop)

=== modified file 'mysql-test/r/join_cache_nojb.result'
--- a/mysql-test/r/join_cache_nojb.result	2012-01-19 09:57:18 +0000
+++ b/mysql-test/r/join_cache_nojb.result	2012-01-20 15:30:14 +0000
@@ -478,7 +478,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition; Using where
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -785,7 +785,7 @@ CountryLanguage.Percentage > 50;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	CountryLanguage	ALL	PRIMARY,Percentage	NULL	NULL	NULL	984	Using where
 1	SIMPLE	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using where
-1	SIMPLE	City	ref	Country	Country	3	world.Country.Code	18	Using index condition; Using where
+1	SIMPLE	City	ref	Country	Country	3	world.CountryLanguage.Country	18	Using where
 SELECT City.Name, Country.Name, CountryLanguage.Language
 FROM City,Country,CountryLanguage
 WHERE City.Country=Country.Code AND
@@ -1309,7 +1309,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t2	eq_ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	
 1	SIMPLE	t7	ref	PRIMARY	PRIMARY	4	test.t1.metaid	1	Using index
 1	SIMPLE	t8	eq_ref	PRIMARY	PRIMARY	4	test.t7.artistid	1	
-1	SIMPLE	t9	ref	PRIMARY,t9_subgenreid,t9_metaid	t9_metaid	4	test.t7.metaid	2	Using index condition
+1	SIMPLE	t9	ref	PRIMARY,t9_subgenreid,t9_metaid	t9_metaid	4	test.t1.metaid	2	
 1	SIMPLE	t10	eq_ref	PRIMARY,t10_genreid	PRIMARY	4	test.t9.subgenreid	1	
 1	SIMPLE	t11	eq_ref	PRIMARY	PRIMARY	4	test.t10.genreid	1	
 1	SIMPLE	t3	ref	t3_metaid,t3_formatid,t3_metaidformatid	t3_metaid	4	test.t1.metaid	2	Using where

=== modified file 'mysql-test/r/join_nested.result'
--- a/mysql-test/r/join_nested.result	2011-08-03 11:29:20 +0000
+++ b/mysql-test/r/join_nested.result	2012-01-20 15:30:14 +0000
@@ -1729,9 +1729,9 @@ LEFT JOIN 
 (t5 JOIN t4 ON t5.carrier_id = t4.id)
 ON t4.carrier = t1.carrier;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	36	
-1	SIMPLE	t3	ref	package_id	package_id	5	test.t1.id	1	Using where; Using index
-1	SIMPLE	t2	ref	package_id	package_id	5	test.t3.package_id	5	Using where; Using index
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	36	Using where
+1	SIMPLE	t3	ref	package_id	package_id	5	test.t1.id	1	Using index
+1	SIMPLE	t2	ref	package_id	package_id	5	test.t1.id	5	Using index
 1	SIMPLE	t4	eq_ref	PRIMARY,id	PRIMARY	2	test.t1.carrier	1	
 1	SIMPLE	t5	ref	carrier_id	carrier_id	5	test.t4.id	22	Using index
 SELECT COUNT(*) 

=== modified file 'mysql-test/r/join_nested_bka.result'
--- a/mysql-test/r/join_nested_bka.result	2011-09-07 10:21:50 +0000
+++ b/mysql-test/r/join_nested_bka.result	2012-01-20 15:30:14 +0000
@@ -1730,9 +1730,9 @@ LEFT JOIN 
 (t5 JOIN t4 ON t5.carrier_id = t4.id)
 ON t4.carrier = t1.carrier;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	36	
-1	SIMPLE	t3	ref	package_id	package_id	5	test.t1.id	1	Using where; Using index
-1	SIMPLE	t2	ref	package_id	package_id	5	test.t3.package_id	5	Using where; Using index
+1	SIMPLE	t1	ALL	PRIMARY	NULL	NULL	NULL	36	Using where
+1	SIMPLE	t3	ref	package_id	package_id	5	test.t1.id	1	Using index
+1	SIMPLE	t2	ref	package_id	package_id	5	test.t1.id	5	Using index
 1	SIMPLE	t4	eq_ref	PRIMARY,id	PRIMARY	2	test.t1.carrier	1	
 1	SIMPLE	t5	ref	carrier_id	carrier_id	5	test.t4.id	22	Using index
 SELECT COUNT(*) 

=== modified file 'mysql-test/r/join_nested_bka_nixbnl.result'
--- a/mysql-test/r/join_nested_bka_nixbnl.result	2011-10-05 13:16:38 +0000
+++ b/mysql-test/r/join_nested_bka_nixbnl.result	2012-01-20 15:30:14 +0000
@@ -1734,7 +1734,7 @@ id	select_type	table	type	possible_keys	
 1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t2.package_id	1	Using join buffer (Batched Key Access)
 1	SIMPLE	t4	eq_ref	PRIMARY,id	PRIMARY	2	test.t1.carrier	1	
 1	SIMPLE	t5	ref	carrier_id	carrier_id	5	test.t4.id	22	Using index
-1	SIMPLE	t3	ref	package_id	package_id	5	test.t1.id	1	Using where; Using index
+1	SIMPLE	t3	ref	package_id	package_id	5	test.t2.package_id	1	Using index
 SELECT COUNT(*) 
 FROM ((t2 JOIN t1 ON t2.package_id = t1.id) 
 JOIN t3 ON t3.package_id = t1.id)

=== modified file 'mysql-test/r/subquery_mat.result'
--- a/mysql-test/r/subquery_mat.result	2012-01-20 09:07:08 +0000
+++ b/mysql-test/r/subquery_mat.result	2012-01-20 15:30:14 +0000
@@ -1276,7 +1276,7 @@ HAVING COUNT(*) > 0
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	table1	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	table2	eq_ref	PRIMARY	PRIMARY	4	const	1	Using where
+1	PRIMARY	table2	const	PRIMARY	PRIMARY	4	const	1	Using where
 2	SUBQUERY	innr	ALL	NULL	NULL	NULL	NULL	2	Using where; Using temporary; Using filesort
 SELECT table1.pk, table2.pk
 FROM t2 AS table1 LEFT JOIN t2 AS table2

=== modified file 'mysql-test/r/subquery_mat_all.result'
--- a/mysql-test/r/subquery_mat_all.result	2012-01-20 09:07:08 +0000
+++ b/mysql-test/r/subquery_mat_all.result	2012-01-20 15:30:14 +0000
@@ -1277,7 +1277,7 @@ HAVING COUNT(*) > 0
 );
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	table1	system	NULL	NULL	NULL	NULL	1	
-1	PRIMARY	table2	eq_ref	PRIMARY	PRIMARY	4	const	1	Using where
+1	PRIMARY	table2	const	PRIMARY	PRIMARY	4	const	1	Using where
 2	SUBQUERY	innr	ALL	NULL	NULL	NULL	NULL	2	Using where; Using temporary; Using filesort
 SELECT table1.pk, table2.pk
 FROM t2 AS table1 LEFT JOIN t2 AS table2

=== modified file 'mysql-test/r/subquery_sj_all.result'
--- a/mysql-test/r/subquery_sj_all.result	2012-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_all.result	2012-01-20 15:30:14 +0000
@@ -5333,7 +5333,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using index condition; Using MRR; Materialize; Scan
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using index condition; Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using index condition; Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5876,7 +5876,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using index condition; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where; Using join buffer (Block Nested Loop)
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7626,5 +7626,43 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_all_bka.result	2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using index condition; Using MRR; Materialize; Scan
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; Using join buffer (Batched Key Access)
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using index condition; Using where; Using join buffer (Batched Key Access)
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using index condition; Using where; Using join buffer (Batched Key Access)
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using index condition; End temporary; Using join buffer (Batched Key Access)
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary; Using join buffer (Batched Key Access)
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where; Using join buffer (Block Nested Loop)
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result	2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using index condition; Using MRR; Materialize; Scan
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; Using join buffer (Batched Key Access)
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using index condition; Using where; Using join buffer (Batched Key Access)
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using index condition; Using where; Using join buffer (Batched Key Access)
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using index condition; End temporary; Using join buffer (Batched Key Access)
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary; Using join buffer (Batched Key Access)
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_all_bkaunique.result	2012-01-20 15:30:14 +0000
@@ -5335,7 +5335,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using index condition; Using MRR; Materialize; Scan
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; Using join buffer (Batched Key Access (unique))
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using index condition; Using where; Using join buffer (Batched Key Access (unique))
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using index condition; Using where; Using join buffer (Batched Key Access (unique))
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5878,7 +5878,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using index condition; End temporary; Using join buffer (Batched Key Access (unique))
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary; Using join buffer (Batched Key Access (unique))
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where; Using join buffer (Block Nested Loop)
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7628,6 +7628,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed.result	2012-01-20 15:30:14 +0000
@@ -5332,7 +5332,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using where; Start temporary
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5875,7 +5875,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using where; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where; Using join buffer (Block Nested Loop)
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7625,5 +7625,43 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka.result	2012-01-20 15:30:14 +0000
@@ -5333,7 +5333,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using where; Start temporary
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5876,7 +5876,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using where; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where; Using join buffer (Block Nested Loop)
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7626,6 +7626,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result	2012-01-20 15:30:14 +0000
@@ -5333,7 +5333,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using where; Start temporary
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5876,7 +5876,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using where; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7626,6 +7626,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result	2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using where; Start temporary
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using where; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where; Using join buffer (Block Nested Loop)
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch.result	2012-01-20 15:30:14 +0000
@@ -5333,7 +5333,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using where; Start temporary
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5876,7 +5876,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using where; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where; Using join buffer (Block Nested Loop)
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7626,6 +7626,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka.result	2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using where; Start temporary
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using where; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where; Using join buffer (Block Nested Loop)
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result	2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using where; Start temporary
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using where; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result	2012-01-20 15:30:14 +0000
@@ -5335,7 +5335,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using where; Start temporary
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5878,7 +5878,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using where; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where; Using join buffer (Block Nested Loop)
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7628,6 +7628,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_loosescan.result	2012-01-20 15:30:14 +0000
@@ -5333,7 +5333,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using where; Start temporary
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5876,7 +5876,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using where; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where; Using join buffer (Block Nested Loop)
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7626,5 +7626,43 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka.result	2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using where; Start temporary
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using where; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where; Using join buffer (Block Nested Loop)
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result	2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using where; Start temporary
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using where; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result	2012-01-20 15:30:14 +0000
@@ -5335,7 +5335,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using where; Start temporary
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where; End temporary
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5878,7 +5878,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using where; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where; Using join buffer (Block Nested Loop)
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7628,6 +7628,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_mat.result	2012-01-20 15:30:14 +0000
@@ -5333,7 +5333,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using where; Materialize; Scan
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5876,7 +5876,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using where; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where; Using join buffer (Block Nested Loop)
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7626,5 +7626,43 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka.result	2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using where; Materialize; Scan
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using where; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where; Using join buffer (Block Nested Loop)
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result	2012-01-20 15:30:14 +0000
@@ -5334,7 +5334,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using where; Materialize; Scan
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5877,7 +5877,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using where; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7627,6 +7627,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_mat_bkaunique.result	2012-01-20 15:30:14 +0000
@@ -5335,7 +5335,7 @@ t2.Population > 100000);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t1	range	Population,Country	Population	4	NULL	1	Using where; Materialize; Scan
 1	PRIMARY	t2	eq_ref	PRIMARY,Population	PRIMARY	3	test.t1.Country	1	Using where
-1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t2.Code,const	1	Using where
+1	PRIMARY	t3	eq_ref	PRIMARY,Percentage	PRIMARY	33	test.t1.Country,const	1	Using where
 DROP TABLE t1,t2,t3;
 CREATE TABLE t1 (
 Code char(3) NOT NULL DEFAULT '',
@@ -5878,7 +5878,7 @@ and t2.uid=t1.fid;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t3	ref	uid	uid	5	const	4	Using where; Start temporary
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t3.fid	1	Using index
-1	PRIMARY	t1	ref	uid	uid	5	test.t4.uid	2	Using where; End temporary
+1	PRIMARY	t1	ref	uid	uid	5	test.t3.fid	2	End temporary
 1	PRIMARY	t2	ALL	PRIMARY	NULL	NULL	NULL	9	Using where; Using join buffer (Block Nested Loop)
 select name from t2, t1 
 where t1.uid in (select t4.uid from t4, t3 where t3.uid=1 and t4.uid=t3.fid)
@@ -7628,6 +7628,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_mat_nosj.result	2012-01-20 15:30:14 +0000
@@ -7702,5 +7702,43 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_none.result	2012-01-20 15:30:14 +0000
@@ -3402,7 +3402,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	DEPENDENT SUBQUERY	t1	index	a	a	5	NULL	10	Using where; Using index
 2	DEPENDENT SUBQUERY	t2	ref	a	a	5	test.t1.a	1	Using index
-2	DEPENDENT SUBQUERY	t3	ref	a	a	5	test.t2.a	1	Using where; Using index
+2	DEPENDENT SUBQUERY	t3	ref	a	a	5	test.t1.a	1	Using index
 drop table t0, t1,t2,t3;
 
 Test that neither MaterializeLookup strategy for semijoin,
@@ -7637,5 +7637,43 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_none_bka.result	2012-01-20 15:30:14 +0000
@@ -3403,7 +3403,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	DEPENDENT SUBQUERY	t1	index	a	a	5	NULL	10	Using where; Using index
 2	DEPENDENT SUBQUERY	t2	ref	a	a	5	test.t1.a	1	Using index
-2	DEPENDENT SUBQUERY	t3	ref	a	a	5	test.t2.a	1	Using where; Using index
+2	DEPENDENT SUBQUERY	t3	ref	a	a	5	test.t1.a	1	Using index
 drop table t0, t1,t2,t3;
 
 Test that neither MaterializeLookup strategy for semijoin,
@@ -7638,6 +7638,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result	2012-01-20 15:30:14 +0000
@@ -3403,7 +3403,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	DEPENDENT SUBQUERY	t1	index	a	a	5	NULL	10	Using where; Using index
 2	DEPENDENT SUBQUERY	t2	ref	a	a	5	test.t1.a	1	Using index
-2	DEPENDENT SUBQUERY	t3	ref	a	a	5	test.t2.a	1	Using where; Using index
+2	DEPENDENT SUBQUERY	t3	ref	a	a	5	test.t1.a	1	Using index
 drop table t0, t1,t2,t3;
 
 Test that neither MaterializeLookup strategy for semijoin,
@@ -7638,6 +7638,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # 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-01-16 12:51:06 +0000
+++ b/mysql-test/r/subquery_sj_none_bkaunique.result	2012-01-20 15:30:14 +0000
@@ -3404,7 +3404,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	DEPENDENT SUBQUERY	t1	index	a	a	5	NULL	10	Using where; Using index
 2	DEPENDENT SUBQUERY	t2	ref	a	a	5	test.t1.a	1	Using index
-2	DEPENDENT SUBQUERY	t3	ref	a	a	5	test.t2.a	1	Using where; Using index
+2	DEPENDENT SUBQUERY	t3	ref	a	a	5	test.t1.a	1	Using index
 drop table t0, t1,t2,t3;
 
 Test that neither MaterializeLookup strategy for semijoin,
@@ -7639,6 +7639,44 @@ FROM t2 LEFT JOIN t3 USING (col_varchar_
 id
 200
 DROP TABLE t1,t2,t3;
+#
+# Bug #13596176: Missing row on select with nested in clause when
+#                matr=on and bnl=off + MyISAM
+#
+CREATE TABLE t1 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+vc_nokey varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t1 VALUES
+(8,'x','x'), (7,'d','d'), (1,'r','r'), (7,'f','f'),
+(9,'y','y'), (NULL,'u','u'), (1,'m','m'), (9,NULL,NULL),
+(2,'o','o'), (9,'w','w'), (2,'m','m'), (4,'q','q'),
+(0,NULL,NULL), (4,'d','d'), (8,'g','g'), (NULL,'x','x'),
+(NULL,'f','f'), (0,'p','p'), (NULL,'j','j'), (8,'c','c');
+CREATE TABLE t2 (
+int_key int DEFAULT NULL,
+vc_key varchar(1) DEFAULT NULL,
+KEY int_key (int_key),
+KEY vc_key (vc_key, int_key)
+) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (8,'g');
+SELECT vc_key
+FROM t1 as outr
+WHERE (vc_nokey, vc_key ) IN
+(SELECT vc_nokey, vc_nokey
+FROM t1 middle
+WHERE vc_nokey IN
+(SELECT child1.vc_key
+FROM t2 AS child1 JOIN t1 AS child2 USING (int_key)
+)
+);
+vc_key
+g
+DROP TABLE t1, t2;
+# End of test for bug#13596176.
 # End of 5.6 tests
 set optimizer_switch=default;
 set optimizer_switch=default;

=== modified file 'mysql-test/suite/opt_trace/r/general2_no_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_no_prot.result	2012-01-10 18:58:10 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_no_prot.result	2012-01-20 15:30:14 +0000
@@ -2622,7 +2622,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	DEPENDENT SUBQUERY	t1	index	a	a	5	NULL	10	Using where; Using index
 2	DEPENDENT SUBQUERY	t2	ref	a	a	5	test.t1.a	1	Using index
-2	DEPENDENT SUBQUERY	t3	ref	a	a	5	test.t2.a	1	Using where; Using index
+2	DEPENDENT SUBQUERY	t3	ref	a	a	5	test.t1.a	1	Using index
 select * from information_schema.optimizer_trace;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
 explain select * 
@@ -3271,7 +3271,7 @@ from t0 where a in
                       {
                         "database": "test",
                         "table": "t3",
-                        "attached": "(`test`.`t3`.`a` = `test`.`t1`.`a`)"
+                        "attached": null
                       }
                     ] /* attached_conditions_summary */
                   } /* attaching_conditions_to_tables */

=== modified file 'mysql-test/suite/opt_trace/r/general2_ps_prot.result'
--- a/mysql-test/suite/opt_trace/r/general2_ps_prot.result	2012-01-10 18:58:10 +0000
+++ b/mysql-test/suite/opt_trace/r/general2_ps_prot.result	2012-01-20 15:30:14 +0000
@@ -2675,7 +2675,7 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t0	ALL	NULL	NULL	NULL	NULL	10	Using where
 2	DEPENDENT SUBQUERY	t1	index	a	a	5	NULL	10	Using where; Using index
 2	DEPENDENT SUBQUERY	t2	ref	a	a	5	test.t1.a	1	Using index
-2	DEPENDENT SUBQUERY	t3	ref	a	a	5	test.t2.a	1	Using where; Using index
+2	DEPENDENT SUBQUERY	t3	ref	a	a	5	test.t1.a	1	Using index
 select * from information_schema.optimizer_trace;
 QUERY	TRACE	MISSING_BYTES_BEYOND_MAX_MEM_SIZE	INSUFFICIENT_PRIVILEGES
 explain select * 
@@ -3324,7 +3324,7 @@ from t0 where a in
                       {
                         "database": "test",
                         "table": "t3",
-                        "attached": "(`test`.`t3`.`a` = `test`.`t1`.`a`)"
+                        "attached": null
                       }
                     ] /* attached_conditions_summary */
                   } /* attaching_conditions_to_tables */

=== modified file 'sql/sql_optimizer.cc'
--- a/sql/sql_optimizer.cc	2012-01-20 09:07:08 +0000
+++ b/sql/sql_optimizer.cc	2012-01-20 15:30:14 +0000
@@ -362,8 +362,6 @@ JOIN::optimize()
     DBUG_RETURN(1);
   }
 
-  drop_unused_derived_keys();
-
   if (rollup.state != ROLLUP::STATE_NONE)
   {
     if (rollup_process_const_fields())
@@ -378,12 +376,6 @@ JOIN::optimize()
     select_distinct= select_distinct && (const_tables != tables);
   }
 
-  THD_STAGE_INFO(thd, stage_preparing);
-  if (result->initialize_tables(this))
-  {
-    DBUG_PRINT("error",("Error: initialize_tables() failed"));
-    DBUG_RETURN(1);				// error == -1
-  }
   if (const_table_map != found_const_table_map &&
       !(select_options & SELECT_DESCRIBE) &&
       (!conds ||
@@ -438,8 +430,7 @@ JOIN::optimize()
   }
 
   /*
-    Permorm the the optimization on fields evaluation mentioned above
-    for all on expressions.
+    Perform the same optimization on field evaluation for all join conditions.
   */ 
   for (JOIN_TAB *tab= join_tab + const_tables; tab < join_tab + tables ; tab++)
   {
@@ -464,6 +455,26 @@ JOIN::optimize()
     conds=new Item_int((longlong) 0,1);	// Always false
   }
 
+  if (set_access_methods())
+  {
+    error= 1;
+    DBUG_PRINT("error",("Error from set_access_methods"));
+    DBUG_RETURN(1);
+  }
+
+  // We need all derived keys until access methods have been set.
+  drop_unused_derived_keys();
+
+  // Update table dependencies after assigning ref access fields
+  update_depend_map(this);
+
+  THD_STAGE_INFO(thd, stage_preparing);
+  if (result->initialize_tables(this))
+  {
+    DBUG_PRINT("error",("Error: initialize_tables() failed"));
+    DBUG_RETURN(1);				// error == -1
+  }
+
   if (make_join_select(this, conds))
   {
     zero_result_cause=
@@ -984,7 +995,33 @@ finish:
   return item;
 }
 
-  
+
+/**
+  Get the best field substitution for a given field.
+
+  If the field is member of a multiple equality, look up that equality
+  and return the most appropriate field. Usually this is the equivalenced
+  field belonging to the outer-most table in the join order, but
+  @see Item_field::get_subst_item() for details.
+  Otherwise, return the same field.
+
+  @param item_field The field that we are seeking a substitution for.
+  @param cond_equal multiple equalities to search in
+
+  @return The substituted field.
+*/
+
+Item_field *get_best_field(Item_field *item_field, COND_EQUAL *cond_equal)
+{
+  bool dummy;
+  Item_equal *item_eq= find_item_equal(cond_equal, item_field->field, &dummy);
+  if (!item_eq)
+    return item_field;
+
+  return item_eq->get_subst_item(item_field);
+}
+
+
 /**
   Check whether an equality can be used to build multiple equalities.
 
@@ -2593,8 +2630,8 @@ void update_depend_map(JOIN *join)
     uint i;
     for (i=0 ; i < ref->key_parts ; i++,item++)
       depend_map|=(*item)->used_tables();
-    ref->depend_map=depend_map & ~OUTER_REF_TABLE_BIT;
-    depend_map&= ~OUTER_REF_TABLE_BIT;
+    depend_map&= ~PSEUDO_TABLE_BITS;
+    ref->depend_map= depend_map;
     for (JOIN_TAB **tab=join->map2table;
 	 depend_map ;
 	 tab++,depend_map>>=1 )

=== modified file 'sql/sql_optimizer.h'
--- a/sql/sql_optimizer.h	2011-12-15 09:00:42 +0000
+++ b/sql/sql_optimizer.h	2012-01-20 15:30:14 +0000
@@ -509,6 +509,7 @@ Item *remove_eq_conds(THD *thd, Item *co
 bool is_indexed_agg_distinct(JOIN *join, List<Item_field> *out_args);
 Item_equal *find_item_equal(COND_EQUAL *cond_equal, Field *field,
                             bool *inherited_fl);
+Item_field *get_best_field(Item_field *item_field, COND_EQUAL *cond_equal);
 Item *
 make_cond_for_table(Item *cond, table_map tables, table_map used_table,
                     bool exclude_expensive_cond);

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-01-17 09:27:34 +0000
+++ b/sql/sql_select.cc	2012-01-20 15:30:14 +0000
@@ -1224,11 +1224,6 @@ bool JOIN::get_best_combination()
 
   set_semijoin_info();
 
-  if (set_access_methods())
-    DBUG_RETURN(true);
-
-  update_depend_map(this);
-
   DBUG_RETURN(false);
 }
 
@@ -1242,6 +1237,11 @@ bool JOIN::get_best_combination()
      - There is no key selected (use JT_ALL)
      - Loose scan semi-join strategy is selected (use JT_ALL)
      - A ref key can be used (use JT_REF, JT_REF_OR_NULL, JT_EQ_REF or JT_FT)
+
+  @note that we cannot setup fields used for ref access before we have sorted
+        the items within multiple equalities according to the final order of
+        the tables involved in the join operation. Currently, this occurs in
+        @see substitute_for_best_equal_field().
 */
 bool JOIN::set_access_methods()
 {
@@ -1360,8 +1360,14 @@ void JOIN::set_semijoin_info()
   @details
     This function will set up a ref access using the best key found
     during access path analysis and cost analysis.
-*/
 
+  @note that we cannot setup fields used for ref access before we have sorted
+        the items within multiple equalities according to the final order of
+        the tables involved in the join operation. Currently, this occurs in
+        @see substitute_for_best_equal_field().
+        The exception is ref access for const tables, which are fixed
+        before the greedy search planner is invoked.  
+*/
 
 bool create_ref_for_key(JOIN *join, JOIN_TAB *j, Key_use *org_keyuse,
                         table_map used_tables)
@@ -1452,6 +1458,14 @@ bool create_ref_for_key(JOIN *join, JOIN
         keyuse++;                               // Skip other parts
 
       uint maybe_null= test(keyinfo->key_part[part_no].null_bit);
+
+      if (keyuse->val->type() == Item::FIELD_ITEM)
+      {
+        // Look up the most appropriate field to base the ref access on.
+        keyuse->val= get_best_field(static_cast<Item_field *>(keyuse->val),
+                                    join->cond_equal);
+        keyuse->used_tables= keyuse->val->used_tables();
+      }
       j->ref.items[part_no]=keyuse->val;        // Save for cond removal
       j->ref.cond_guards[part_no]= keyuse->cond_guard;
       if (keyuse->null_rejecting) 
@@ -2579,14 +2593,11 @@ bool setup_sj_materialization(JOIN_TAB *
     it.rewind();
     for (uint i=0; i < sjm->table_cols.elements; i++)
     {
-      bool dummy;
-      Item_equal *item_eq;
       Item *item= (it++)->real_item();
       DBUG_ASSERT(item->type() == Item::FIELD_ITEM);
-      Field *copy_to= ((Item_field*)item)->field;
       /*
-        Tricks with Item_equal are due to the following: suppose we have a
-        query:
+        The trick with get_best_field() is due to the following;
+        suppose we have a query:
         
         ... WHERE cond(ot.col) AND ot.col IN (SELECT it2.col FROM it1,it2
                                                WHERE it1.col= it2.col)
@@ -2609,21 +2620,8 @@ bool setup_sj_materialization(JOIN_TAB *
          element equality propagation member that refers to table that is
          within the subquery.
       */
-      item_eq= find_item_equal(tab->join->cond_equal, copy_to, &dummy);
-
-      if (item_eq)
-      {
-        List_iterator<Item_field> it(item_eq->fields);
-        Item_field *item;
-        while ((item= it++))
-        {
-          if (!(item->used_tables() & ~emb_sj_nest->sj_inner_tables))
-          {
-            copy_to= item->field;
-            break;
-          }
-        }
-      }
+      Field *copy_to= get_best_field(static_cast<Item_field *>(item),
+                                     tab->join->cond_equal)->field;
       sjm->copy_field[i].set(copy_to, sjm->table->field[i], FALSE);
       /* The write_set for source tables must be set up to allow the copying */
       bitmap_set_bit(copy_to->table->write_set, copy_to->field_index);
@@ -2634,8 +2632,6 @@ bool setup_sj_materialization(JOIN_TAB *
 }
 
 
-
-
 /**
   Plan refinement stage: do various setup things for the executor
 

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3529 to 3530) Bug#13596176Roy Lyseng21 Jan