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

[Jorgen Loland]

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

 3691 Jorgen Loland	2009-10-30
      Bug#48093: 6.0 Server not processing equivalent IN clauses 
                 properly with Innodb tables
      
      InnoDB never appended the HA_KEY_SCAN_NOT_ROR flag in 
      index_flags(). This made the optimizer believe that all indexes
      returned records in ROR (Rowid Ordered Retrieval) order. However,
      only the primary key index returns records in order of row id. 
      
      Since the optimizer thought the indexes were ROR, it could 
      chose to use the index merge strategy on indexes that did not 
      preserve ROR order. This lead to missing records in the result 
      set.
      
      This patch makes index_flags return the HA_KEY_SCAN_NOT_ROR 
      flag unless the index is the primary key index.
     @ mysql-test/r/range.result
        Added test for BUG#48093
     @ mysql-test/t/range.test
        Added test for BUG#48093 and sourced have_innodb.inc so that the added test and existing tests in this file that actually require InnoDB can use this storage engine.
     @ storage/innobase/handler/ha_innodb.h
        Append HA_KEY_SCAN_NOT_ROR to flags returned from index_flags unless the index is the primary key index.

    modified:
      mysql-test/r/range.result
      mysql-test/t/range.test
      storage/innobase/handler/ha_innodb.h
=== modified file 'mysql-test/r/range.result'
--- a/mysql-test/r/range.result	2009-10-16 09:56:14 +0000
+++ b/mysql-test/r/range.result	2009-10-30 14:00:16 +0000
@@ -1420,3 +1420,27 @@ select 1 from (select c from t1,t2 where
 1
 1
 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 DEFAULT CHARSET=latin1;
+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
+DROP TABLE t1;
+# End of 6.0 tests

=== modified file 'mysql-test/t/range.test'
--- a/mysql-test/t/range.test	2009-10-16 09:56:14 +0000
+++ b/mysql-test/t/range.test	2009-10-30 14:00:16 +0000
@@ -1,3 +1,5 @@
+--source include/have_innodb.inc
+
 #
 # Problem with range optimizer
 #
@@ -1193,3 +1195,31 @@ create table t2 (c int);
 insert into t2(c) values (1),(5),(6),(7),(8);
 select 1 from (select c from t1,t2 where b >= 1 and a <=> c group by 1 limit 1) as d1;
 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 DEFAULT CHARSET=latin1;
+
+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;
+
+DROP TABLE t1;
+
+--echo # End of 6.0 tests
+

=== modified file 'storage/innobase/handler/ha_innodb.h'
--- a/storage/innobase/handler/ha_innodb.h	2009-10-09 19:45:32 +0000
+++ b/storage/innobase/handler/ha_innodb.h	2009-10-30 14:00:16 +0000
@@ -107,6 +107,7 @@ class ha_innobase: public handler
                           fl|=
                           ((idx == primary_key)? 0 :
                            HA_DO_INDEX_COND_PUSHDOWN););
+	  if (idx != primary_key) fl|= HA_KEY_SCAN_NOT_ROR;
           return fl;
 	}
 	uint max_supported_keys()	   const { return MAX_KEY; }


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