MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Georgi Kodinov Date:November 20 2009 12:53pm
Subject:bzr commit into mysql-5.1-bugteam branch (joro:3208) Bug#48665
View as plain text  
#At file:///home/kgeorge/mysql/work/B48665-5.1-bugteam/ based on revid:joro@stripped

 3208 Georgi Kodinov	2009-11-19
      Bug #48665: sql-bench's insert test fails due to wrong result
      
      When merging ranges during calculation of the result of OR
      to two range sets the current range may be obsoleted by the 
      resulting merged range.
      The first overlapping range can be obsoleted as well.
      
      Fixed by moving the pointer to the first overlapping range to the
      pointer of the resulting union range.
      Added few comments at key places in key_or().

    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	2009-11-02 12:24:07 +0000
+++ b/mysql-test/r/range.result	2009-11-19 16:26:19 +0000
@@ -1603,4 +1603,21 @@ SELECT str_to_date('', '%Y-%m-%d');
 str_to_date('', '%Y-%m-%d')
 0000-00-00
 DROP TABLE t1, t2;
+#
+# Bug #48665: sql-bench's insert test fails due to wrong result
+#
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a));
+INSERT INTO t1 VALUES (0,0), (1,1);
+EXPLAIN 
+SELECT * FROM t1 FORCE INDEX (PRIMARY) 
+WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+@	@	@	range	@	@	@	@	@	@
+# Should return 2 rows
+SELECT * FROM t1 FORCE INDEX (PRIMARY) 
+WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10);
+a	b
+0	0
+1	1
+DROP TABLE t1;
 End of 5.1 tests

=== modified file 'mysql-test/t/range.test'
--- a/mysql-test/t/range.test	2009-11-02 12:24:07 +0000
+++ b/mysql-test/t/range.test	2009-11-19 16:26:19 +0000
@@ -1260,4 +1260,25 @@ SELECT str_to_date('', '%Y-%m-%d');
 
 DROP TABLE t1, t2;
 
+
+--echo #
+--echo # Bug #48665: sql-bench's insert test fails due to wrong result
+--echo #
+
+CREATE TABLE t1 (a INT, b INT, PRIMARY KEY (a));
+
+INSERT INTO t1 VALUES (0,0), (1,1);
+
+--replace_column 1 @ 2 @ 3 @ 5 @ 6 @ 7 @ 8 @ 9 @ 10 @
+EXPLAIN 
+SELECT * FROM t1 FORCE INDEX (PRIMARY) 
+ WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10);
+
+--echo # Should return 2 rows
+SELECT * FROM t1 FORCE INDEX (PRIMARY) 
+ WHERE (a>=1 AND a<=2) OR (a>=4 AND a<=5) OR (a>=0 AND a <=10);
+
+DROP TABLE t1;
+
+
 --echo End of 5.1 tests

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2009-11-02 12:24:07 +0000
+++ b/sql/opt_range.cc	2009-11-19 16:26:19 +0000
@@ -6671,6 +6671,7 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *
     else if ((cmp=tmp->cmp_max_to_min(key2)) < 0)
     {						// Found tmp.max < key2.min
       SEL_ARG *next=tmp->next;
+      /* key1 on the left of key2 non-overlapping */
       if (cmp == -2 && eq_tree(tmp->next_key_part,key2->next_key_part))
       {
 	// Join near ranges like tmp.max < 0 and key2.min >= 0
@@ -6699,6 +6700,7 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *
       int tmp_cmp;
       if ((tmp_cmp=tmp->cmp_min_to_max(key2)) > 0) // if tmp.min > key2.max
       {
+        /* tmp is on the right of key2 non-overlapping */
 	if (tmp_cmp == 2 && eq_tree(tmp->next_key_part,key2->next_key_part))
 	{					// ranges are connected
 	  tmp->copy_min_to_min(key2);
@@ -6733,25 +6735,52 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *
       }
     }
 
-    // tmp.max >= key2.min && tmp.min <= key.max  (overlapping ranges)
+    /* 
+      tmp.min >= key2.min && tmp.min <= key.max  (overlapping ranges)
+      key2.min <= tmp.min <= key2.max 
+    */  
     if (eq_tree(tmp->next_key_part,key2->next_key_part))
     {
       if (tmp->is_same(key2))
       {
+        /* 
+          Found exact match of key2 inside key1. 
+          Use the relevant range in key1.
+        */
 	tmp->merge_flags(key2);			// Copy maybe flags
 	key2->increment_use_count(-1);		// Free not used tree
       }
       else
       {
 	SEL_ARG *last=tmp;
+        SEL_ARG *first=tmp;
+        /* 
+          Find the last range in tmp that overlaps key2 and has the same 
+          condition on the rest of the keyparts.
+        */
 	while (last->next && last->next->cmp_min_to_max(key2) <= 0 &&
 	       eq_tree(last->next->next_key_part,key2->next_key_part))
 	{
+          /*
+            We've found the last overlapping key1 range in last.
+            This means that the ranges between (and including) the 
+            first overlapping range (tmp) and the last overlapping range
+            (last) are fully nested into the current range of key2 
+            and can safely be discarded. We just need the minimum endpoint
+            of the first overlapping range (tmp) so we can compare it with
+            the minimum endpoint of the enclosing key2 range.
+          */
 	  SEL_ARG *save=last;
 	  last=last->next;
 	  key1=key1->tree_delete(save);
 	}
-        last->copy_min(tmp);
+        /*
+          The tmp range (the first overlapping range) could have been discarded
+          by the previous loop. We should re-direct tmp to the new united range 
+          that's taking its place.
+        */
+        tmp= last;
+        last->copy_min(first);
         bool full_range= last->copy_min(key2);
         if (!full_range)
         {


Attachment: [text/bzr-bundle] bzr/joro@sun.com-20091119162619-op3ny0dvpw8eibij.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (joro:3208) Bug#48665Georgi Kodinov20 Nov