bzr commit into mysql-6.0-codebase-bugfixing branch (jorgen.loland:3699) Bug#48093

[Jorgen Loland]

#At file:///localhome/jl208045/mysql/mysql-6.0-codebase-bugfixing-48093/ based on revid:holyfoot@stripped

 3699 Jorgen Loland	2009-11-06
      Bug#48093: 6.0 Server not processing equivalent IN clauses 
                 properly with Innodb tables
            
      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
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result	2009-10-23 06:24:37 +0000
+++ b/mysql-test/r/innodb_mysql.result	2009-11-06 07:40:19 +0000
@@ -2281,4 +2281,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	2009-10-12 09:08:34 +0000
+++ b/mysql-test/t/innodb_mysql.test	2009-11-06 07:40:19 +0000
@@ -503,4 +503,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	2009-10-23 06:24:37 +0000
+++ b/sql/opt_range.cc	2009-11-06 07:40:19 +0000
@@ -7695,11 +7695,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;
   }
 
   DBUG_PRINT("exit", ("Records: %lu", (ulong) rows));


Attachment: [text/bzr-bundle] bzr/[e-mail]