From: Sergey Petrunia
Date: November 22 2008 12:46am
Subject: bzr push into mysql-6.0-opt-subqueries branch (sergefp:2731 to 2732)
List-Archive: http://lists.mysql.com/commits/59612
Message-Id: <20081122004620.8ACE615A0FE@pslp2.localdomain>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7BIT

 2732 Sergey Petrunia	2008-11-22
      Testcase for the previous cset
modified:
  mysql-test/r/subselect3.result
  mysql-test/t/subselect3.test

 2731 Sergey Petrunia	2008-11-22
      WL#3985: Subquery optimization: Smart choice between semi-join and materialization
      - Make LooseScan handling re-use the results of equality propagation.
modified:
  sql/sql_select.cc

=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2008-11-22 00:29:16 +0000
+++ b/mysql-test/r/subselect3.result	2008-11-22 00:44:45 +0000
@@ -943,3 +943,15 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	t1	ALL	NULL	NULL	NULL	NULL	20	Using where; FirstMatch(<derived2>)
 2	DERIVED	t0	ALL	NULL	NULL	NULL	NULL	11	
 drop table t0, t1;
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (kp1 int, kp2 int, c int, filler char(100), key(kp1, kp2));
+insert into t1 select A.a+10*(B.a+10*C.a), 0, 0, 'filler' from t0 A, t0 B, t0 C;
+insert into t1 select * from t1 where kp1 < 20;
+create table t3 (a int);
+insert into t3 select A.a + 10*B.a from t0 A, t0 B;
+explain select * from t3 where a in (select kp1 from t1 where kp1<20);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using where; Using index; LooseScan
+1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where
+drop table t0, t1, t3;

=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test	2008-11-22 00:29:16 +0000
+++ b/mysql-test/t/subselect3.test	2008-11-22 00:44:45 +0000
@@ -762,3 +762,20 @@ set @@optimizer_switch='';
 #
 explain select * from (select a from t0) X where a in (select a from t1);
 drop table t0, t1;
+
+#
+# LooseScan: Check if we can pick it together with range access
+#
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+
+create table t1 (kp1 int, kp2 int, c int, filler char(100), key(kp1, kp2));
+insert into t1 select A.a+10*(B.a+10*C.a), 0, 0, 'filler' from t0 A, t0 B, t0 C;
+insert into t1 select * from t1 where kp1 < 20;
+
+create table t3 (a int);
+insert into t3 select A.a + 10*B.a from t0 A, t0 B;
+
+explain select * from t3 where a in (select kp1 from t1 where kp1<20);
+
+drop table t0, t1, t3;