From: Roy Lyseng Date: January 27 2012 12:45pm Subject: bzr push into mysql-trunk branch (roy.lyseng:3533 to 3534) Bug#13596176 List-Archive: http://lists.mysql.com/commits/142602 X-Bug: 13596176 Message-Id: <20120127124547.563C4207@tyr67.norway.sun.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 3534 Roy Lyseng 2012-01-27 [merge] Bug#13596176: Missing row on select with nested in clause when matr=on and bnl=off + MyISAM Minor post-push fixes: Added EXPLAIN to test cases. Fixed two comment typos. Additional commit comment to update_depend_map() in sql_optimizer.cc The loop in this function that shifts depend_map had to mask out all pseudo bits, not only OUTER_REF_TABLE_BIT, because otherwise the process might crash. modified: mysql-test/include/subquery_sj.inc mysql-test/r/subquery_sj_all.result mysql-test/r/subquery_sj_all_bka.result mysql-test/r/subquery_sj_all_bka_nixbnl.result mysql-test/r/subquery_sj_all_bkaunique.result mysql-test/r/subquery_sj_dupsweed.result mysql-test/r/subquery_sj_dupsweed_bka.result mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result mysql-test/r/subquery_sj_dupsweed_bkaunique.result mysql-test/r/subquery_sj_firstmatch.result mysql-test/r/subquery_sj_firstmatch_bka.result mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result mysql-test/r/subquery_sj_firstmatch_bkaunique.result mysql-test/r/subquery_sj_loosescan.result mysql-test/r/subquery_sj_loosescan_bka.result mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result mysql-test/r/subquery_sj_loosescan_bkaunique.result mysql-test/r/subquery_sj_mat.result mysql-test/r/subquery_sj_mat_bka.result mysql-test/r/subquery_sj_mat_bka_nixbnl.result mysql-test/r/subquery_sj_mat_bkaunique.result mysql-test/r/subquery_sj_mat_nosj.result mysql-test/r/subquery_sj_none.result mysql-test/r/subquery_sj_none_bka.result mysql-test/r/subquery_sj_none_bka_nixbnl.result mysql-test/r/subquery_sj_none_bkaunique.result sql/sql_select.cc 3533 Tor Didriksen 2012-01-27 WL#5957 post-push fix: dont count_bits unless we are going to use them modified: sql/handler.cc === modified file 'mysql-test/include/subquery_sj.inc' --- a/mysql-test/include/subquery_sj.inc 2012-01-20 15:30:14 +0000 +++ b/mysql-test/include/subquery_sj.inc 2012-01-27 12:28:14 +0000 @@ -4848,6 +4848,7 @@ CREATE TABLE t2 ( INSERT INTO t2 VALUES (8,'g'); +let $query= SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN @@ -4859,6 +4860,9 @@ WHERE (vc_nokey, vc_key ) IN ) ); +eval explain $query; +eval $query; + DROP TABLE t1, t2; --echo # End of test for bug#13596176. === modified file 'mysql-test/r/subquery_sj_all.result' --- a/mysql-test/r/subquery_sj_all.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_all.result 2012-01-27 12:28:14 +0000 @@ -7650,6 +7650,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (Block Nested Loop) SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_all_bka.result' --- a/mysql-test/r/subquery_sj_all_bka.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_all_bka.result 2012-01-27 12:28:14 +0000 @@ -7651,6 +7651,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where; Using join buffer (Batched Key Access) +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (Block Nested Loop) SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_all_bka_nixbnl.result' --- a/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_all_bka_nixbnl.result 2012-01-27 12:28:14 +0000 @@ -7651,6 +7651,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start materialize; Scan +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End materialize +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where; Using join buffer (Batched Key Access) SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_all_bkaunique.result' --- a/mysql-test/r/subquery_sj_all_bkaunique.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_all_bkaunique.result 2012-01-27 12:28:14 +0000 @@ -7652,6 +7652,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where; Using join buffer (Batched Key Access (unique)) +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (Block Nested Loop) SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_dupsweed.result' --- a/mysql-test/r/subquery_sj_dupsweed.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_dupsweed.result 2012-01-27 12:28:14 +0000 @@ -7649,6 +7649,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (Block Nested Loop) SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_dupsweed_bka.result' --- a/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_dupsweed_bka.result 2012-01-27 12:28:14 +0000 @@ -7650,6 +7650,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (Block Nested Loop) SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result' --- a/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_dupsweed_bka_nixbnl.result 2012-01-27 12:28:14 +0000 @@ -7650,6 +7650,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_dupsweed_bkaunique.result' --- a/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_dupsweed_bkaunique.result 2012-01-27 12:28:14 +0000 @@ -7651,6 +7651,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (Block Nested Loop) SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_firstmatch.result' --- a/mysql-test/r/subquery_sj_firstmatch.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_firstmatch.result 2012-01-27 12:28:14 +0000 @@ -7650,6 +7650,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (Block Nested Loop) SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_firstmatch_bka.result' --- a/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_firstmatch_bka.result 2012-01-27 12:28:14 +0000 @@ -7651,6 +7651,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (Block Nested Loop) SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result' --- a/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_firstmatch_bka_nixbnl.result 2012-01-27 12:28:14 +0000 @@ -7651,6 +7651,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_firstmatch_bkaunique.result' --- a/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_firstmatch_bkaunique.result 2012-01-27 12:28:14 +0000 @@ -7652,6 +7652,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (Block Nested Loop) SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_loosescan.result' --- a/mysql-test/r/subquery_sj_loosescan.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_loosescan.result 2012-01-27 12:28:14 +0000 @@ -7650,6 +7650,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (Block Nested Loop) SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_loosescan_bka.result' --- a/mysql-test/r/subquery_sj_loosescan_bka.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_loosescan_bka.result 2012-01-27 12:28:14 +0000 @@ -7651,6 +7651,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (Block Nested Loop) SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result' --- a/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_loosescan_bka_nixbnl.result 2012-01-27 12:28:14 +0000 @@ -7651,6 +7651,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_loosescan_bkaunique.result' --- a/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_loosescan_bkaunique.result 2012-01-27 12:28:14 +0000 @@ -7652,6 +7652,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (Block Nested Loop) SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_mat.result' --- a/mysql-test/r/subquery_sj_mat.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_mat.result 2012-01-27 12:28:14 +0000 @@ -7650,6 +7650,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (Block Nested Loop) SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_mat_bka.result' --- a/mysql-test/r/subquery_sj_mat_bka.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_mat_bka.result 2012-01-27 12:28:14 +0000 @@ -7651,6 +7651,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (Block Nested Loop) SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_mat_bka_nixbnl.result' --- a/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_mat_bka_nixbnl.result 2012-01-27 12:28:14 +0000 @@ -7651,6 +7651,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start materialize; Scan +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End materialize +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_mat_bkaunique.result' --- a/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_mat_bkaunique.result 2012-01-27 12:28:14 +0000 @@ -7652,6 +7652,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY child1 index int_key,vc_key vc_key 9 NULL 1 Using where; Using index; Start temporary +1 PRIMARY outr ref vc_key vc_key 4 test.child1.vc_key 3 Using where +1 PRIMARY child2 ref int_key int_key 5 test.child1.int_key 3 Using index +1 PRIMARY middle ALL NULL NULL NULL NULL 20 Using where; End temporary; Using join buffer (Block Nested Loop) SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_mat_nosj.result' --- a/mysql-test/r/subquery_sj_mat_nosj.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_mat_nosj.result 2012-01-27 12:28:14 +0000 @@ -7726,6 +7726,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY outr ALL NULL NULL NULL NULL 20 Using where +2 SUBQUERY middle ALL NULL NULL NULL NULL 20 Using where +3 SUBQUERY child1 system int_key NULL NULL NULL 1 +3 SUBQUERY child2 ref int_key int_key 5 const 3 Using index SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_none.result' --- a/mysql-test/r/subquery_sj_none.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_none.result 2012-01-27 12:28:14 +0000 @@ -7661,6 +7661,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY outr ALL NULL NULL NULL NULL 20 Using where +2 DEPENDENT SUBQUERY middle ALL NULL NULL NULL NULL 20 Using where +3 DEPENDENT SUBQUERY child1 system int_key,vc_key NULL NULL NULL 1 +3 DEPENDENT SUBQUERY child2 ref int_key int_key 5 const 3 Using where; Using index SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_none_bka.result' --- a/mysql-test/r/subquery_sj_none_bka.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_none_bka.result 2012-01-27 12:28:14 +0000 @@ -7662,6 +7662,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY outr ALL NULL NULL NULL NULL 20 Using where +2 DEPENDENT SUBQUERY middle ALL NULL NULL NULL NULL 20 Using where +3 DEPENDENT SUBQUERY child1 system int_key,vc_key NULL NULL NULL 1 +3 DEPENDENT SUBQUERY child2 ref int_key int_key 5 const 3 Using where; Using index SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_none_bka_nixbnl.result' --- a/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_none_bka_nixbnl.result 2012-01-27 12:28:14 +0000 @@ -7662,6 +7662,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY outr ALL NULL NULL NULL NULL 20 Using where +2 DEPENDENT SUBQUERY middle ALL NULL NULL NULL NULL 20 Using where +3 DEPENDENT SUBQUERY child1 system int_key,vc_key NULL NULL NULL 1 +3 DEPENDENT SUBQUERY child2 ref int_key int_key 5 const 3 Using where; Using index SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'mysql-test/r/subquery_sj_none_bkaunique.result' --- a/mysql-test/r/subquery_sj_none_bkaunique.result 2012-01-20 15:30:14 +0000 +++ b/mysql-test/r/subquery_sj_none_bkaunique.result 2012-01-27 12:28:14 +0000 @@ -7663,6 +7663,21 @@ KEY int_key (int_key), KEY vc_key (vc_key, int_key) ) ENGINE=MyISAM; INSERT INTO t2 VALUES (8,'g'); +explain 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) +) +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY outr ALL NULL NULL NULL NULL 20 Using where +2 DEPENDENT SUBQUERY middle ALL NULL NULL NULL NULL 20 Using where +3 DEPENDENT SUBQUERY child1 system int_key,vc_key NULL NULL NULL 1 +3 DEPENDENT SUBQUERY child2 ref int_key int_key 5 const 3 Using where; Using index SELECT vc_key FROM t1 as outr WHERE (vc_nokey, vc_key ) IN === modified file 'sql/sql_select.cc' --- a/sql/sql_select.cc 2012-01-20 15:30:14 +0000 +++ b/sql/sql_select.cc 2012-01-27 12:28:14 +0000 @@ -1238,7 +1238,7 @@ bool JOIN::get_best_combination() - 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 + @note 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(). @@ -1361,7 +1361,7 @@ void JOIN::set_semijoin_info() 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 + @note 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(). No bundle (reason: useless for push emails).