bzr push into mysql-6.0-opt branch (sergefp:2695)

[Sergey Petrunia]

 2695 Sergey Petrunia	2009-02-12 [merge]
      Merge
modified:
  .bzr-mysql/default.conf
  mysql-test/r/join_cache.result
  mysql-test/t/disabled.def
  mysql-test/t/join_cache.test
  sql/sql_join_cache.cc
  sql/sql_select.cc

=== modified file 'mysql-test/r/partition_hash.result'
--- a/mysql-test/r/partition_hash.result	2008-07-07 20:42:19 +0000
+++ b/mysql-test/r/partition_hash.result	2009-02-05 19:06:44 +0000
@@ -93,7 +93,7 @@ id	select_type	table	partitions	type	pos
 1	SIMPLE	t1	p0,p1,p2,p3	ALL	NULL	NULL	NULL	NULL	9	Using where
 explain partitions select * from t1 where a >= 1 and a <= 5;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t1	p0,p1,p2,p3	ALL	NULL	NULL	NULL	NULL	9	Using where
+1	SIMPLE	t1	p0,p1,p2	ALL	NULL	NULL	NULL	NULL	9	Using where
 drop table t1;
 CREATE TABLE t1 (
 a int not null,

=== modified file 'mysql-test/r/partition_pruning.result'
--- a/mysql-test/r/partition_pruning.result	2009-01-08 10:20:10 +0000
+++ b/mysql-test/r/partition_pruning.result	2009-02-05 19:06:44 +0000
@@ -951,3 +951,22 @@ explain partitions select * from t1 wher
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	2	Using where
 drop table t1;
+# 
+# BUG#33730 Full table scan instead selected partitions for query more than 10 partitions
+# 
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int)
+partition by range(a+0) (
+partition p0 values less than (64),
+partition p1 values less than (128),
+partition p2 values less than (255)
+);
+insert into t1 select A.a + 10*B.a from t0 A, t0 B;
+explain partitions select * from t1 where a between 10 and 13;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0	ALL	NULL	NULL	NULL	NULL	64	Using where
+explain partitions select * from t1 where a between 10 and 10+33;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2	ALL	NULL	NULL	NULL	NULL	100	Using where
+drop table t0, t1;

=== modified file 'mysql-test/t/partition_pruning.test'
--- a/mysql-test/t/partition_pruning.test	2008-12-28 11:33:49 +0000
+++ b/mysql-test/t/partition_pruning.test	2009-02-05 19:06:44 +0000
@@ -808,3 +808,22 @@ INSERT INTO t1 VALUES ('2006-03-01 12:00
 -- echo must use p0 only:
 explain partitions select * from t1 where recdate < '2006-01-01 00:00:00';
 drop table t1;
+
+-- echo # 
+-- echo # BUG#33730 Full table scan instead selected partitions for query more than 10 partitions
+-- echo # 
+create table t0 (a int);
+insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
+create table t1 (a int)
+  partition by range(a+0) (
+  partition p0 values less than (64),
+  partition p1 values less than (128),
+  partition p2 values less than (255)
+);
+insert into t1 select A.a + 10*B.a from t0 A, t0 B;
+
+# this will use interval_via_walking
+explain partitions select * from t1 where a between 10 and 13;
+explain partitions select * from t1 where a between 10 and 10+33;
+
+drop table t0, t1;

=== modified file 'sql/sql_partition.cc'
--- a/sql/sql_partition.cc	2009-02-04 13:08:05 +0000
+++ b/sql/sql_partition.cc	2009-02-12 18:21:57 +0000
@@ -6739,7 +6739,7 @@ int get_part_iter_for_interval_via_mappi
 
 
 /* See get_part_iter_for_interval_via_walking for definition of what this is */
-#define MAX_RANGE_TO_WALK 10
+#define MAX_RANGE_TO_WALK 32
 
 
 /*
@@ -6775,16 +6775,6 @@ int get_part_iter_for_interval_via_mappi
     Intervals with +inf/-inf, and [NULL, c1] interval can be processed but
     that is more tricky and I don't have time to do it right now.
 
-    Additionally we have these requirements:
-    * number of values in the interval must be less then number of
-      [sub]partitions, and 
-    * Number of values in the interval must be less then MAX_RANGE_TO_WALK.
-    
-    The rationale behind these requirements is that if they are not met
-    we're likely to hit most of the partitions and traversing the interval
-    will only add overhead. So it's better return "all partitions used" in
-    that case.
-
   RETURN
     0 - No matching partitions, iterator not initialized
     1 - Some partitions would match, iterator intialized for traversing them
@@ -6880,7 +6870,23 @@ int get_part_iter_for_interval_via_walki
   b += test(!(flags & NEAR_MAX));
   ulonglong n_values= b - a;
   
-  if (n_values > total_parts || n_values > MAX_RANGE_TO_WALK)
+  /*
+    Will it pay off to enumerate all values in the [a..b] range and evaluate
+    the partitioning function for every value? It depends on 
+     1. whether we'll be able to infer that some partitions are not used 
+     2. if time savings from not scanning these partitions will be greater
+        than time spent in enumeration.
+    We will assume that the cost of accessing one extra partition is greater
+    than the cost of evaluating the partitioning function O(#partitions).
+    This means we should jump at any chance to eliminate a partition, which
+    gives us this logic:
+
+    Do the enumeration if
+     - the number of values to enumerate is comparable to the number of
+       partitions, or
+     - there are not many values to enumerate.
+  */
+  if ((n_values > 2*total_parts) && n_values > MAX_RANGE_TO_WALK)
   {
     DBUG_RETURN(-1);
   }