List:Commits« Previous MessageNext Message »
From:mhansson Date:June 15 2007 10:55am
Subject:bk commit into 5.1 tree (mhansson:1.2466) BUG#26106
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 repository of martin. When martin does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-06-15 13:55:18+03:00, mhansson@stripped +3 -0
  bug #26106: Wrong plan may be chosen when there are several possible range and ref 
  accesses
  
  For ref(const) access methods there are two kinds of statistics when a prefix of a 
  key is matched: range access estimate and table statistics, which are not necessarily
  consistent with each other. This may make the optimizer choose a key which matches 
  fewer key parts than some other key, which is obviously not correct.
  
  Fixed by giving preferrence to the keys that match the most parts, and choosing the
  best one among these.

  mysql-test/r/range.result@stripped, 2007-06-15 13:55:12+03:00, mhansson@stripped +57 -0
    bug #26106: test result

  mysql-test/t/range.test@stripped, 2007-06-15 13:55:12+03:00, mhansson@stripped +58 -0
    bug #26106: test case

  sql/sql_select.cc@stripped, 2007-06-15 13:55:12+03:00, mhansson@stripped +21 -2
    bug #26106: If it is discovered that one or more range estimates match more 
    key parts than all known ref estimates, we will look for and use the best range 
    estimate.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	mhansson
# Host:	linux-st28.site
# Root:	/home/martin/mysql/src/5.1o-bug26106

--- 1.497/sql/sql_select.cc	2007-02-27 10:39:02 +02:00
+++ 1.498/sql/sql_select.cc	2007-06-15 13:55:12 +03:00
@@ -3778,6 +3778,13 @@ best_access_path(JOIN      *join,
   double best=              DBL_MAX;
   double best_time=         DBL_MAX;
   double records=           DBL_MAX;
+  /* Holds the current number of records from the range optimizer, if any */
+  double quick_records=     DBL_MAX;
+  /* 
+     When a key from the range optimizer matches more parts than a ref key, the
+     estimates from these sources are not comparable.
+  */
+  double best_quick_records= DBL_MAX;
   table_map best_ref_depends_map= 0;
   double tmp;
   ha_rows rec;
@@ -3807,6 +3814,12 @@ best_access_path(JOIN      *join,
       /* Calculate how many key segments of the current key we can use */
       start_key= keyuse;
 
+      /* 
+         True if we find some keys from the range optimizer that match more
+         key parts than the best ref key. We then choose the best range key.
+      */
+      bool quick_matches_more_parts= false;
+      
       do /* For each keypart */
       {
         uint keypart= keyuse->keypart;
@@ -4040,7 +4053,10 @@ best_access_path(JOIN      *join,
                 if (!found_ref && table->quick_keys.is_set(key) &&    // (1)
                     table->quick_key_parts[key] > max_key_part &&     // (2)
                     records < (double)table->quick_rows[key])         // (3)
-                  records= (double)table->quick_rows[key];
+                {
+                  records= quick_records= (double)table->quick_rows[key];
+                  quick_matches_more_parts= true;                  
+                }
 
                 tmp= records;
               }
@@ -4129,8 +4145,11 @@ best_access_path(JOIN      *join,
             tmp= best_time;                    // Do nothing
         }
       } /* not ft_key */
-      if (tmp < best_time - records/(double) TIME_FOR_COMPARE)
+      if (tmp < best_time - records/(double) TIME_FOR_COMPARE ||
+          (quick_matches_more_parts && 
+           quick_records < best_quick_records))
       {
+        best_quick_records = quick_records;
         best_time= tmp + records/(double) TIME_FOR_COMPARE;
         best= tmp;
         best_records= records;

--- 1.59/mysql-test/r/range.result	2007-02-06 16:52:22 +02:00
+++ 1.60/mysql-test/r/range.result	2007-06-15 13:55:12 +03:00
@@ -1008,3 +1008,60 @@ 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 INT,
+b INT,
+KEY k ( a ),
+KEY l ( a, b )
+);
+INSERT INTO t1(a) VALUES (1);
+INSERT INTO t1 
+VALUES (2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3);
+INSERT INTO t1 SELECT 3, 4 FROM t1 WHERE a = 2 AND b = 3;
+INSERT INTO t1 SELECT 4, 1 FROM t1 WHERE a = 2 AND b = 3;
+ANALYZE TABLE t1;
+Table	Op	Msg_type	Msg_text
+test.t1	analyze	status	OK
+INSERT INTO t1 VALUES (1, 2);
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2;
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2;
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2;
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2;
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2;
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2;
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2;
+This must use range over index l, not k.
+EXPLAIN SELECT * FROM t1 WHERE a = 1 AND b >= 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	range	k,l	l	10	NULL	138	Using where; Using index
+CREATE TABLE t2( 
+a INT, 
+b INT, 
+c INT,
+KEY k ( a ), 
+KEY l ( a, b ),
+KEY m ( b ), 
+KEY n ( a, c )
+);
+INSERT INTO t2(a) VALUES (1);
+INSERT INTO t2 
+VALUES (2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3),
+(2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3);
+INSERT INTO t2 SELECT 3, 4, 4 FROM t2 WHERE a = 2 AND b = 3;
+INSERT INTO t2 SELECT 4, 1, 1 FROM t2 WHERE a = 2 AND b = 3;
+ANALYZE TABLE t2;
+Table	Op	Msg_type	Msg_text
+test.t2	analyze	status	OK
+INSERT INTO t2 VALUES (1, 2, 2);
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2;
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2;
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2;
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2;
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2;
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2;
+INSERT INTO t2 VALUES (1, 1, 2);
+This must use range over index l, not n.
+EXPLAIN SELECT * FROM t2 WHERE a = 1 AND b >= 2 AND c >= 2;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ref	k,l,m,n	l	5	const	69	Using where
+DROP TABLE t1, t2;

--- 1.47/mysql-test/t/range.test	2007-02-06 16:52:22 +02:00
+++ 1.48/mysql-test/t/range.test	2007-06-15 13:55:12 +03:00
@@ -820,3 +820,61 @@ explain select * from t2 where a=1000 an
 
 drop table t1, t2;
 
+#
+# Bug #26106: Wrong plan may be chosen when there are several possible range and
+# ref accesses
+#
+CREATE TABLE t1( 
+  a INT,
+  b INT,
+  KEY k ( a ),
+  KEY l ( a, b )
+); 
+
+INSERT INTO t1(a) VALUES (1);
+INSERT INTO t1 
+VALUES (2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3),(2,3);
+INSERT INTO t1 SELECT 3, 4 FROM t1 WHERE a = 2 AND b = 3;
+INSERT INTO t1 SELECT 4, 1 FROM t1 WHERE a = 2 AND b = 3;
+ANALYZE TABLE t1;
+INSERT INTO t1 VALUES (1, 2);
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 2
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 4
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 8
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 16
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 32
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 64
+INSERT INTO t1 SELECT a, b FROM t1 WHERE a=1 AND b=2; # 128
+--echo This must use range over index l, not k.
+EXPLAIN SELECT * FROM t1 WHERE a = 1 AND b >= 2;
+
+CREATE TABLE t2( 
+  a INT, 
+  b INT, 
+  c INT,
+  KEY k ( a ), 
+  KEY l ( a, b ),
+  KEY m ( b ), 
+  KEY n ( a, c )
+); 
+
+INSERT INTO t2(a) VALUES (1);
+INSERT INTO t2 
+VALUES (2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3),
+       (2,3,3),(2,3,3),(2,3,3),(2,3,3),(2,3,3);
+INSERT INTO t2 SELECT 3, 4, 4 FROM t2 WHERE a = 2 AND b = 3;
+INSERT INTO t2 SELECT 4, 1, 1 FROM t2 WHERE a = 2 AND b = 3;
+ANALYZE TABLE t2;
+INSERT INTO t2 VALUES (1, 2, 2);
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 2
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 4
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 8
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 16
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 32
+INSERT INTO t2 SELECT a, b, c FROM t2 WHERE a=1 AND b=2; # 64
+INSERT INTO t2 VALUES (1, 1, 2);
+
+--echo This must use range over index l, not n.
+EXPLAIN SELECT * FROM t2 WHERE a = 1 AND b >= 2 AND c >= 2;
+
+DROP TABLE t1, t2;
Thread
bk commit into 5.1 tree (mhansson:1.2466) BUG#26106mhansson15 Jun