List:Commits« Previous MessageNext Message »
From:Roy Lyseng Date:January 27 2012 12:45pm
Subject:bzr push into mysql-trunk branch (roy.lyseng:3533 to 3534) Bug#13596176
View as plain text  
 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).
Thread
bzr push into mysql-trunk branch (roy.lyseng:3533 to 3534) Bug#13596176Roy Lyseng30 Jan