bzr commit into mysql-next-mr-bugfixing branch (oystein.grovlen:3166) Bug#49845

[Oystein.Grovlen]

#At file:///home/oysteing/mysql/mysql-next-mr-opt-backporting/ based on revid:epotemkin@stripped

 3166 oystein.grovlen@stripped	2010-05-11
      Bug#49845 Loosescan reports different result than other semijoin methods
      
      (Backporting of oystein.grovlen@stripped)
      
      Duplicate elimination for LooseScan assumes that table is scanned in key order.
      However, MRR will by default return rows ordered by rowid (DiskSweep MRR).
      
      Fix: Tell range select to deliver a sorted output.
     @ mysql-test/r/subselect3.result
        Updated result file.
     @ mysql-test/r/subselect3_jcl6.result
        Updated result file.
     @ mysql-test/t/subselect3.test
        A test case for this bug already existed, but it was only explained, not
        executed.  Add execution of test case.
     @ sql/sql_select.cc
        For LooseScan, make sure output from index range scans are sorted by key.

    modified:
      mysql-test/r/subselect3.result
      mysql-test/r/subselect3_jcl6.result
      mysql-test/t/subselect3.test
      sql/sql_select.cc
=== modified file 'mysql-test/r/subselect3.result'
--- a/mysql-test/r/subselect3.result	2010-05-10 09:37:24 +0000
+++ b/mysql-test/r/subselect3.result	2010-05-11 16:56:38 +0000
@@ -1159,14 +1159,59 @@ explain select * from t3 where a in (sel
 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
+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
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
-explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
-and t4.pk=t1.c);
+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 MRR; LooseScan
+1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using index condition; 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
+select * from t3 where a in 
+(select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 drop table t1, t3, t4;
 create table t1 (a int) as select * from t0 where a < 5;
 set @save_max_heap_table_size=@@max_heap_table_size;

=== modified file 'mysql-test/r/subselect3_jcl6.result'
--- a/mysql-test/r/subselect3_jcl6.result	2010-05-10 09:37:24 +0000
+++ b/mysql-test/r/subselect3_jcl6.result	2010-05-11 16:56:38 +0000
@@ -1164,14 +1164,59 @@ explain select * from t3 where a in (sel
 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
+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
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
-explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
-and t4.pk=t1.c);
+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 MRR; LooseScan
+1	PRIMARY	t1	range	kp1	kp1	5	NULL	48	Using index condition; 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
+select * from t3 where a in 
+(select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
+a
+0
+1
+2
+3
+4
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
 drop table t1, t3, t4;
 create table t1 (a int) as select * from t0 where a < 5;
 set @save_max_heap_table_size=@@max_heap_table_size;

=== modified file 'mysql-test/t/subselect3.test'
--- a/mysql-test/t/subselect3.test	2010-05-10 09:37:24 +0000
+++ b/mysql-test/t/subselect3.test	2010-05-11 16:56:38 +0000
@@ -960,12 +960,15 @@ 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);
+select * from t3 where a in (select kp1 from t1 where kp1<20);
 
 create table t4 (pk int primary key);
 insert into t4 select a from t3;
 
-explain select * from t3 where a in (select t1.kp1 from t1,t4 where kp1<20
-and t4.pk=t1.c);
+explain select * from t3 where a in 
+          (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
+select * from t3 where a in 
+  (select t1.kp1 from t1,t4 where kp1<20 and t4.pk=t1.c);
 
 drop table t1, t3, t4;
 

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2010-05-11 16:23:10 +0000
+++ b/sql/sql_select.cc	2010-05-11 16:56:38 +0000
@@ -1239,6 +1239,18 @@ int setup_semijoin_dups_elimination(JOIN
         /* We jump from the last table to the first one */
         tab->loosescan_match_tab= tab + pos->n_sj_tables - 1;
 
+        /* For LooseScan, duplicate elimination is based on rows being sorted 
+           on key. We need to make sure that range select keep the sorted index
+           order. (When using MRR it may not.)  
+
+           Note: need_sorted_output() implementations for range select classes 
+           that do not support sorted output, will trigger an assert. This 
+           should happen since LooseScan strategy will not be picked if sorted 
+           output is not supported.
+        */
+        if (tab->select && tab->select->quick)
+          tab->select->quick->need_sorted_output();
+
         /* Calculate key length */
         keylen= 0;
         keyno= pos->loosescan_key;


Attachment: [text/bzr-bundle]