[Commits] Rev 3393: BUG#920255: Wrong result (extra rows) with loosescan and IN subquery in file:///home/psergey/dev2/5.3-look48/

Sergey Petrunya psergey at askmonty.org
Wed Jan 25 16:33:59 EET 2012


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

------------------------------------------------------------
revno: 3393
revision-id: psergey at askmonty.org-20120125143357-8mdlbpurobce40w7
parent: psergey at askmonty.org-20120125142734-kwwse8mrr74zwkhf
committer: Sergey Petrunya <psergey at askmonty.org>
branch nick: 5.3-look48
timestamp: Wed 2012-01-25 18:33:57 +0400
message:
  BUG#920255: Wrong result (extra rows) with loosescan and IN subquery
  The problem was that LooseScan execution code assumed that tab->key holds 
  the index used for looseScan. This is only true when range or full index
  scan are used. In case of ref access, the index is in tab->ref.key (and 
  tab->index==0 which explains how LooseScan passed tests with ref access: they 
  used one index)
  
  Fixed by setting/using loosescan_key, which always the correct index#.
=== modified file 'mysql-test/r/subselect_sj.result'
--- a/mysql-test/r/subselect_sj.result	2012-01-08 10:43:14 +0000
+++ b/mysql-test/r/subselect_sj.result	2012-01-25 14:33:57 +0000
@@ -2169,4 +2169,38 @@
 a	COUNT(*)
 NULL	0
 DROP TABLE t1, t2, t3;
+#
+# BUG#920255: Wrong result (extra rows) with loosescan and IN subquery
+#
+CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) );
+INSERT INTO t1 VALUES
+(1,2),(2,1),(3,3),(4,2),(5,5),
+(6,3),(7,1),(8,4),(9,3),(10,2);
+CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+INSERT INTO t2 VALUES
+(1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+( SELECT b, d FROM t1, t2 WHERE b = c );
+a	b	d
+2	1	2
+7	1	2
+2	1	2
+7	1	2
+1	2	1
+4	2	1
+10	2	1
+1	2	1
+4	2	1
+10	2	1
+3	3	3
+6	3	3
+9	3	3
+3	3	3
+6	3	3
+9	3	3
+8	4	2
+8	4	2
+5	5	5
+DROP TABLE t1, t2;
 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-19 22:11:53 +0000
+++ b/mysql-test/r/subselect_sj_jcl6.result	2012-01-25 14:33:57 +0000
@@ -2183,6 +2183,40 @@
 a	COUNT(*)
 NULL	0
 DROP TABLE t1, t2, t3;
+#
+# BUG#920255: Wrong result (extra rows) with loosescan and IN subquery
+#
+CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) );
+INSERT INTO t1 VALUES
+(1,2),(2,1),(3,3),(4,2),(5,5),
+(6,3),(7,1),(8,4),(9,3),(10,2);
+CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+INSERT INTO t2 VALUES
+(1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+( SELECT b, d FROM t1, t2 WHERE b = c );
+a	b	d
+1	2	1
+1	2	1
+2	1	2
+2	1	2
+3	3	3
+3	3	3
+4	2	1
+4	2	1
+5	5	5
+6	3	3
+6	3	3
+7	1	2
+7	1	2
+8	4	2
+8	4	2
+9	3	3
+9	3	3
+10	2	1
+10	2	1
+DROP TABLE t1, t2;
 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-08 10:43:14 +0000
+++ b/mysql-test/t/subselect_sj.test	2012-01-25 14:33:57 +0000
@@ -2017,5 +2017,24 @@
 
 DROP TABLE t1, t2, t3;
 
+--echo #
+--echo # BUG#920255: Wrong result (extra rows) with loosescan and IN subquery
+--echo #
+CREATE TABLE t1 ( a INT PRIMARY KEY, b INT, KEY(b) );
+INSERT INTO t1 VALUES
+  (1,2),(2,1),(3,3),(4,2),(5,5),
+  (6,3),(7,1),(8,4),(9,3),(10,2);
+
+CREATE TABLE t2 ( c INT, d INT, UNIQUE KEY(c) );
+INSERT INTO t2 VALUES
+  (1,2),(2,1),(3,3),(4,2),(5,5),(6,3),(7,1);
+
+SELECT a, b, d FROM t1, t2
+WHERE ( b, d ) IN
+  ( SELECT b, d FROM t1, t2 WHERE b = c );
+
+DROP TABLE t1, t2;
+
+
 # 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-19 19:44:43 +0000
+++ b/sql/opt_subselect.cc	2012-01-25 14:33:57 +0000
@@ -4107,6 +4107,7 @@
         for (uint kp=0; kp < pos->loosescan_picker.loosescan_parts; kp++)
           keylen += tab->table->key_info[keyno].key_part[kp].store_length;
 
+        tab->loosescan_key= keyno;
         tab->loosescan_key_len= keylen;
         if (pos->n_sj_tables > 1) 
           tab[pos->n_sj_tables - 1].do_firstmatch= tab;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2012-01-19 22:11:53 +0000
+++ b/sql/sql_select.cc	2012-01-25 14:33:57 +0000
@@ -15372,7 +15372,7 @@
     if (join_tab->loosescan_match_tab && 
         join_tab->loosescan_match_tab->found_match)
     {
-      KEY *key= join_tab->table->key_info + join_tab->index;
+      KEY *key= join_tab->table->key_info + join_tab->loosescan_key;
       key_copy(join_tab->loosescan_buf, join_tab->table->record[0], key, 
                join_tab->loosescan_key_len);
       skip_over= TRUE;
@@ -15382,7 +15382,7 @@
 
     if (skip_over && !error) 
     {
-      if(!key_cmp(join_tab->table->key_info[join_tab->index].key_part,
+      if(!key_cmp(join_tab->table->key_info[join_tab->loosescan_key].key_part,
                   join_tab->loosescan_buf, join_tab->loosescan_key_len))
       {
         /* 

=== modified file 'sql/sql_select.h'
--- a/sql/sql_select.h	2012-01-10 21:26:00 +0000
+++ b/sql/sql_select.h	2012-01-25 14:33:57 +0000
@@ -379,6 +379,12 @@
   /* Buffer to save index tuple to be able to skip duplicates */
   uchar *loosescan_buf;
   
+  /* 
+    Index used by LooseScan (we store it here separately because ref access
+    stores it in tab->ref.key, while range scan stores it in tab->index, etc)
+  */
+  uint loosescan_key;
+
   /* Length of key tuple (depends on #keyparts used) to store in the above */
   uint loosescan_key_len;
 



More information about the commits mailing list