bzr commit into mysql-5.1-bugteam branch (mhansson:2895) Bug#42846

[Martin Hansson]

#At file:///data0/martin/bzr/bug42846/5.1bt-gca-commit/ based on revid:bar@stripped

 2895 Martin Hansson	2009-05-13
      Bug#42846: wrong result returned for range scan when using
      covering index
      
      When an open-ended range was merged with a closed range, the 
      closed range copied the (non-existent) endpoint from the open
      range, thereby violating the range optimizer invariant that 
      ranges must be disjoint. This led to duplicate answers.
      Fixed by not copying endpoints off open-ended ranges.
     @ mysql-test/r/range.result
        Bug#42846: Result
     @ mysql-test/t/range.test
        Bug#42846: Test case
     @ sql/opt_range.cc
        Bug#42846: Fix

    modified:
      mysql-test/r/range.result
      mysql-test/t/range.test
      sql/opt_range.cc
=== modified file 'mysql-test/r/range.result'
--- a/mysql-test/r/range.result	2008-03-27 02:18:46 +0000
+++ b/mysql-test/r/range.result	2009-05-13 15:14:31 +0000
@@ -1219,3 +1219,18 @@ explain select * from t2 where a=1000 an
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	ref	a	a	5	const	502	Using where
 drop table t1, t2;
+CREATE TABLE t1(
+a CHAR( 1 ),
+b CHAR( 1 ),
+KEY( a, b )
+);
+INSERT INTO t1( a, b ) 
+VALUES ('0', 'a'), ('1', 'd'), ('1', 'b'), ('2', 'c'), ('9', 'g');
+SELECT * FROM t1 WHERE  
+( '3' <= a AND b = 'h' ) OR  
+a > 'b'  OR 
+( a >= '5' AND b >= 'a' ) OR
+a > '2';
+a	b
+9	g
+DROP TABLE t1;

=== modified file 'mysql-test/t/range.test'
--- a/mysql-test/t/range.test	2008-03-27 02:18:46 +0000
+++ b/mysql-test/t/range.test	2009-05-13 15:14:31 +0000
@@ -1046,3 +1046,22 @@ explain select * from t2 where a=1000 an
 
 drop table t1, t2;
 
+#
+# Bug#42846: wrong result returned for range scan when using covering index
+#
+CREATE TABLE t1(
+  a CHAR( 1 ),
+  b CHAR( 1 ),
+  KEY( a, b )
+);
+
+INSERT INTO t1( a, b ) 
+VALUES ('0', 'a'), ('1', 'd'), ('1', 'b'), ('2', 'c'), ('9', 'g');
+
+SELECT * FROM t1 WHERE  
+( '3' <= a AND b = 'h' ) OR  
+a > 'b'  OR 
+( a >= '5' AND b >= 'a' ) OR
+a > '2';
+
+DROP TABLE t1;

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2009-03-24 13:58:52 +0000
+++ b/sql/opt_range.cc	2009-05-13 15:14:31 +0000
@@ -6659,7 +6659,8 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *
 	  key1=key1->tree_delete(save);
 	}
         last->copy_min(tmp);
-	if (last->copy_min(key2) || last->copy_max(key2))
+	if (last->copy_min(key2) || 
+            ((key2->max_flag & NO_MAX_RANGE) == 0 && last->copy_max(key2)))
 	{					// Full range
 	  key1->free_tree();
 	  for (; key2 ; key2=key2->next)


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