List:Commits« Previous MessageNext Message »
From:Oystein.Grovlen Date:May 7 2010 7:33am
Subject:bzr push into mysql-next-mr-bugfixing branch (oystein.grovlen:3139 to
3140) Bug#48093
View as plain text  
 3140 oystein.grovlen@stripped	2010-05-07
      Bug#48093: 6.0 Server not processing equivalent IN clauses 
                 properly with Innodb tables
      
      (Backporting of jorgen.loland@stripped)
            
      When MRR was used to lookup multiple values in an index that
      return ROR ordered records, it was assumed that the returned
      records from MRR were also ROR ordered. This is not true since
      MRR returns all records retrieved for the first value, then all
      records for the second value and so on. Although the records are
      ROR ordered for each value, the end result is not ROR order.
            
      Since the optimizer thought multi-value lookups returned ROR
      ordered records, it tried to do index merge on the returned
      records. These turned out not to be ROR ordered, resulting in
      incorrect merging and in turn missing rows in the result set.
            
      The fix is to invalidate ROR ordering for index lookup 
      algorithms when looking up multiple key values.
     @ mysql-test/r/innodb_mysql.result
        Added test for BUG#48093
     @ mysql-test/t/innodb_mysql.test
        Added test for BUG#48093
     @ sql/opt_range.cc
        Invalidate ROR ordering for index lookup algorithms when looking up multiple key values.

    modified:
      mysql-test/r/innodb_mysql.result
      mysql-test/t/innodb_mysql.test
      sql/opt_range.cc
 3139 oystein.grovlen@stripped	2010-05-06
      Bug#46733 - NULL value not returned for aggregate on empty result 
                  set w/ semijoin on
      
      Issue was fixed by BUG#46328. This patch adds a regression test
      for the query that was reported in BUG#46733.
     @ mysql-test/r/join_cache.result
        Add regression test for BUG#46733
     @ mysql-test/t/join_cache.test
        Add regression test for BUG#46733

    modified:
      mysql-test/r/join_cache.result
      mysql-test/t/join_cache.test
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result	2010-05-02 19:14:50 +0000
+++ b/mysql-test/r/innodb_mysql.result	2010-05-07 07:33:01 +0000
@@ -2396,4 +2396,35 @@ b
 1
 set join_cache_level=default;
 DROP TABLE t1,t2;
+#
+# BUG#48093: 6.0 Server not processing equivalent IN clauses properly 
+#            with Innodb tables
+#
+CREATE TABLE t1 (
+i int(11) DEFAULT NULL,
+v1 varchar(1) DEFAULT NULL,
+v2 varchar(20) DEFAULT NULL,
+KEY i (i),
+KEY v (v1,i)
+) ENGINE=innodb;
+INSERT INTO t1 VALUES (1,'f','no');
+INSERT INTO t1 VALUES (2,'u','yes-u');
+INSERT INTO t1 VALUES (2,'h','yes-h');
+INSERT INTO t1 VALUES (3,'d','no');
+
+SELECT v2  
+FROM t1
+WHERE v1  IN  ('f', 'd', 'h', 'u' ) AND i  =  2;
+v2
+yes-u
+yes-h
+
+# Should not use index_merge
+EXPLAIN
+SELECT v2  
+FROM t1
+WHERE v1  IN  ('f', 'd', 'h', 'u' ) AND i  =  2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ref	i,v	i	5	const	2	Using where
+DROP TABLE t1;
 End of 6.0 tests

=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test	2010-05-02 19:14:50 +0000
+++ b/mysql-test/t/innodb_mysql.test	2010-05-07 07:33:01 +0000
@@ -671,4 +671,36 @@ set join_cache_level=default;
 
 DROP TABLE t1,t2;
 
+--echo #
+--echo # BUG#48093: 6.0 Server not processing equivalent IN clauses properly 
+--echo #            with Innodb tables
+--echo #
+
+CREATE TABLE t1 (
+  i int(11) DEFAULT NULL,
+  v1 varchar(1) DEFAULT NULL,
+  v2 varchar(20) DEFAULT NULL,
+  KEY i (i),
+  KEY v (v1,i)
+) ENGINE=innodb;
+
+INSERT INTO t1 VALUES (1,'f','no');
+INSERT INTO t1 VALUES (2,'u','yes-u');
+INSERT INTO t1 VALUES (2,'h','yes-h');
+INSERT INTO t1 VALUES (3,'d','no');
+
+--echo
+SELECT v2  
+FROM t1
+WHERE v1  IN  ('f', 'd', 'h', 'u' ) AND i  =  2;
+
+--echo
+--echo # Should not use index_merge
+EXPLAIN
+SELECT v2  
+FROM t1
+WHERE v1  IN  ('f', 'd', 'h', 'u' ) AND i  =  2;
+
+DROP TABLE t1;
+
 --echo End of 6.0 tests

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2010-05-05 11:22:58 +0000
+++ b/sql/opt_range.cc	2010-05-07 07:33:01 +0000
@@ -7925,11 +7925,20 @@ ha_rows check_quick_select(PARAM *param,
     */
     param->is_ror_scan= FALSE;
   }
-  else
+  else if (param->table->s->primary_key == keynr && pk_is_clustered)
   {
     /* Clustered PK scan is always a ROR scan (TODO: same as above) */
-    if (param->table->s->primary_key == keynr && pk_is_clustered)
-      param->is_ror_scan= TRUE;
+    param->is_ror_scan= TRUE;
+  }
+  else if (param->range_count > 1)
+  {
+    /* 
+      Scaning multiple key values in the index: the records are ROR
+      for each value, but not between values. E.g, "SELECT ... x IN
+      (1,3)" returns ROR order for all records with x=1, then ROR
+      order for records with x=3
+    */
+    param->is_ror_scan= FALSE;
   }
   if (param->table->file->index_flags(keynr, 0, TRUE) & HA_KEY_SCAN_NOT_ROR)
     param->is_ror_scan= FALSE;


Attachment: [text/bzr-bundle]
Thread
bzr push into mysql-next-mr-bugfixing branch (oystein.grovlen:3139 to3140) Bug#48093Oystein.Grovlen7 May