[Commits] Rev 3402: BUG#923246: Loosescan reports different result than other semijoin methods in file:///home/psergey/dev2/5.3-look52/

Sergey Petrunya psergey at askmonty.org
Mon Jan 30 18:34:49 EET 2012


At file:///home/psergey/dev2/5.3-look52/

------------------------------------------------------------
revno: 3402
revision-id: psergey at askmonty.org-20120130163447-45fbi6mk1q6vz3w7
parent: psergey at askmonty.org-20120130133814-yrru443nqatypsuk
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.3-look52
timestamp: Mon 2012-01-30 20:34:47 +0400
message:
  BUG#923246: Loosescan reports different result than other semijoin methods
  - If LooseScan is used with quick select, require that quick select produces 
    data in key order (this disables use of MRR, which can return data in arbitrary order).
=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- a/mysql-test/r/subselect3_jcl6.result	2012-01-18 10:53:50 +0000
+++ b/mysql-test/r/subselect3_jcl6.result	2012-01-30 16:34:47 +0000
@@ -1156,7 +1156,7 @@
 explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
 and t4.pk=t1.c);
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using index condition; Using where; Rowid-ordered scan; LooseScan
+1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using index condition; Using where; LooseScan
 1	PRIMARY	t4	eq_ref	PRIMARY	PRIMARY	4	test.t1.c	1	Using index; FirstMatch(t1)
 1	PRIMARY	t3	ALL	NULL	NULL	NULL	NULL	100	Using where; Using join buffer (flat, BNL join)
 drop table t1, t3, t4;

=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2012-01-28 03:23:08 +0000
+++ b/mysql-test/r/subselect_sj.result	2012-01-30 16:34:47 +0000
@@ -2588,4 +2588,44 @@
 g	0	j
 g	8	v
 DROP TABLE t1, t2;
+#
+# BUG#923246: Loosescan reports different result than other semijoin methods
+#
+set @tmp_923246= @@optimizer_switch;
+set optimizer_switch='mrr=on,materialization=off';
+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;
+select * from t3 where a in (select kp1 from t1 where kp1<20);
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+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; Using join buffer (flat, BNL join)
+drop table t0,t1,t3;
+set optimizer_switch= @tmp_923246;
 set optimizer_switch=@subselect_sj_tmp;

=== modified file 'mysql-test/r/subselect_sj_jcl6.result'
--- a/mysql-test/r/subselect_sj_jcl6.result	2012-01-28 03:23:08 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2012-01-30 16:34:47 +0000
@@ -2602,6 +2602,46 @@
 g	0	j
 g	8	v
 DROP TABLE t1, t2;
+#
+# BUG#923246: Loosescan reports different result than other semijoin methods
+#
+set @tmp_923246= @@optimizer_switch;
+set optimizer_switch='mrr=on,materialization=off';
+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;
+select * from t3 where a in (select kp1 from t1 where kp1<20);
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+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; Using join buffer (flat, BNL join)
+drop table t0,t1,t3;
+set optimizer_switch= @tmp_923246;
 set optimizer_switch=@subselect_sj_tmp;
 #
 # BUG#49129: Wrong result with IN-subquery with join_cache_level=6 and firstmatch=off

=== modified file 'mysql-test/t/subselect_sj.test'
--- a/mysql-test/t/subselect_sj.test	2012-01-28 03:23:08 +0000
+++ b/mysql-test/t/subselect_sj.test	2012-01-30 16:34:47 +0000
@@ -2309,5 +2309,27 @@
 
 DROP TABLE t1, t2;
 
+--echo #
+--echo # BUG#923246: Loosescan reports different result than other semijoin methods
+--echo #
+set @tmp_923246= @@optimizer_switch;
+set optimizer_switch='mrr=on,materialization=off';
+
+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;
+
+select * from t3 where a in (select kp1 from t1 where kp1<20);
+explain select * from t3 where a in (select kp1 from t1 where kp1<20);
+
+drop table t0,t1,t3;
+set optimizer_switch= @tmp_923246;
+
 # The following command must be the last one the file 
 set optimizer_switch=@subselect_sj_tmp;

=== modified file 'sql/opt_subselect.cc'
--- a/sql/opt_subselect.cc	2012-01-25 18:05:20 +0000
+++ b/sql/opt_subselect.cc	2012-01-30 16:34:47 +0000
@@ -4099,6 +4099,11 @@
       {
         /* We jump from the last table to the first one */
         tab->loosescan_match_tab= tab + pos->n_sj_tables - 1;
+        
+        /* LooseScan requires records to be produced in order */
+        if (tab->select && tab->select->quick)
+          tab->select->quick->need_sorted_output();
+
         for (uint j= i; j < i + pos->n_sj_tables; j++)
           join->join_tab[j].inside_loosescan_range= TRUE;
 



More information about the commits mailing list