List:Commits« Previous MessageNext Message »
From:Sergey Petrunia Date:September 14 2007 10:18am
Subject:bk commit into 5.1 tree (sergefp:1.2603) BUG#27927
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 repository of psergey. When psergey 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-09-14 14:18:42+04:00, sergefp@stripped +8 -0
  BUG#27927:Partition pruning not optimal with TO_DAYS and YEAR functions
  - Introduced val_int_endpoint() function which converts between func 
    argument intervals and func value intervals for monotonic functions.
  - Made partition interval analyzer use part_expr->val_int_endpoint()
    to check if the edge values should be included.

  mysql-test/r/partition_pruning.result@stripped, 2007-09-14 14:17:38+04:00, sergefp@stripped +28 -0
    BUG#27927: Partition pruning not optimal with TO_DAYS and YEAR functions
    - Testcase

  mysql-test/t/partition_pruning.test@stripped, 2007-09-14 14:17:39+04:00, sergefp@stripped +31 -0
    BUG#27927: Partition pruning not optimal with TO_DAYS and YEAR functions
    - Testcase

  sql/item.cc@stripped, 2007-09-14 14:17:39+04:00, sergefp@stripped +5 -0
    BUG#27927: Partition pruning not optimal with TO_DAYS and YEAR functions
    - Added Item_field::val_int_endpoint() implementation

  sql/item.h@stripped, 2007-09-14 14:17:40+04:00, sergefp@stripped +38 -0
    BUG#27927: Partition pruning not optimal with TO_DAYS and YEAR functions
    - Added Item::val_int_endpoint() which converts intervals from argument
      space to function value space for unary monotonic functions.

  sql/item_timefunc.cc@stripped, 2007-09-14 14:17:40+04:00, sergefp@stripped +70 -1
    BUG#27927: Partition pruning not optimal with TO_DAYS and YEAR functions
    - Added val_int_endpoint() for TO_DAYS and YEAR functions.

  sql/item_timefunc.h@stripped, 2007-09-14 14:17:40+04:00, sergefp@stripped +2 -0
    BUG#27927: Partition pruning not optimal with TO_DAYS and YEAR functions
    - Added val_int_endpoint() for TO_DAYS and YEAR functions.

  sql/partition_info.h@stripped, 2007-09-14 14:17:40+04:00, sergefp@stripped +0 -14
    BUG#27927: Partition pruning not optimal with TO_DAYS and YEAR functions
    - Removed partition_info::range_analysis_include_bounds as it is no longer 
      needed.

  sql/sql_partition.cc@stripped, 2007-09-14 14:17:40+04:00, sergefp@stripped +8 -12
    BUG#27927: Partition pruning not optimal with TO_DAYS and YEAR functions
    - Make partition interval analyzer use part_expr->val_int_endpoint() to 
      check if the edge values should be included.

diff -Nrup a/mysql-test/r/partition_pruning.result b/mysql-test/r/partition_pruning.result
--- a/mysql-test/r/partition_pruning.result	2007-08-28 04:33:52 +04:00
+++ b/mysql-test/r/partition_pruning.result	2007-09-14 14:17:38 +04:00
@@ -911,3 +911,31 @@ explain partitions select * from t1 wher
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	p3	ALL	NULL	NULL	NULL	NULL	4	Using where
 drop table t1;
+CREATE TABLE t1 ( recdate  DATETIME NOT NULL )
+PARTITION BY RANGE( TO_DAYS(recdate) ) (
+PARTITION p0 VALUES LESS THAN ( TO_DAYS('2007-03-08') ),
+PARTITION p1 VALUES LESS THAN ( TO_DAYS('2007-04-01') )
+);
+INSERT INTO t1 VALUES ('2007-03-01 12:00:00');
+INSERT INTO t1 VALUES ('2007-03-07 12:00:00');
+INSERT INTO t1 VALUES ('2007-03-08 12:00:00');
+INSERT INTO t1 VALUES ('2007-03-15 12:00:00');
+must use p0 only:
+explain partitions select * from t1 where recdate < '2007-03-08 00:00:00';
+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;
+CREATE TABLE t1 ( recdate  DATETIME NOT NULL )
+PARTITION BY RANGE( YEAR(recdate) ) (
+PARTITION p0 VALUES LESS THAN (2006),
+PARTITION p1 VALUES LESS THAN (2007)
+);
+INSERT INTO t1 VALUES ('2005-03-01 12:00:00');
+INSERT INTO t1 VALUES ('2005-03-01 12:00:00');
+INSERT INTO t1 VALUES ('2006-03-01 12:00:00');
+INSERT INTO t1 VALUES ('2006-03-01 12:00:00');
+must use p0 only:
+explain partitions select * from t1 where recdate < '2006-01-01 00:00:00';
+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;
diff -Nrup a/mysql-test/t/partition_pruning.test b/mysql-test/t/partition_pruning.test
--- a/mysql-test/t/partition_pruning.test	2007-06-13 19:28:57 +04:00
+++ b/mysql-test/t/partition_pruning.test	2007-09-14 14:17:39 +04:00
@@ -761,3 +761,34 @@ insert into t1 values (-15),(-5),(5),(15
 explain partitions select * from t1 where a>-2 and a <=0;
 drop table t1;
 
+
+#
+# BUG#27927 Partition pruning not optimal with TO_DAYS function
+# 
+
+CREATE TABLE t1 ( recdate  DATETIME NOT NULL )
+PARTITION BY RANGE( TO_DAYS(recdate) ) (
+  PARTITION p0 VALUES LESS THAN ( TO_DAYS('2007-03-08') ),
+  PARTITION p1 VALUES LESS THAN ( TO_DAYS('2007-04-01') )
+);
+INSERT INTO t1 VALUES ('2007-03-01 12:00:00');
+INSERT INTO t1 VALUES ('2007-03-07 12:00:00');
+INSERT INTO t1 VALUES ('2007-03-08 12:00:00');
+INSERT INTO t1 VALUES ('2007-03-15 12:00:00');
+-- echo must use p0 only:
+explain partitions select * from t1 where recdate < '2007-03-08 00:00:00';
+
+drop table t1;
+CREATE TABLE t1 ( recdate  DATETIME NOT NULL )
+PARTITION BY RANGE( YEAR(recdate) ) (
+  PARTITION p0 VALUES LESS THAN (2006),
+  PARTITION p1 VALUES LESS THAN (2007)
+);
+INSERT INTO t1 VALUES ('2005-03-01 12:00:00');
+INSERT INTO t1 VALUES ('2005-03-01 12:00:00');
+INSERT INTO t1 VALUES ('2006-03-01 12:00:00');
+INSERT INTO t1 VALUES ('2006-03-01 12:00:00');
+
+-- echo must use p0 only:
+explain partitions select * from t1 where recdate < '2006-01-01 00:00:00';
+drop table t1;
diff -Nrup a/sql/item.cc b/sql/item.cc
--- a/sql/item.cc	2007-08-28 04:33:53 +04:00
+++ b/sql/item.cc	2007-09-14 14:17:39 +04:00
@@ -2069,6 +2069,11 @@ Item *Item_field::get_tmp_table_item(THD
   return new_item;
 }
 
+longlong Item_field::val_int_endpoint(bool left_endp, bool *incl_endp)
+{
+  longlong res= val_int();
+  return null_value? LONGLONG_MIN : res;
+}
 
 /*
   Create an item from a string we KNOW points to a valid longlong
diff -Nrup a/sql/item.h b/sql/item.h
--- a/sql/item.h	2007-08-04 13:08:07 +04:00
+++ b/sql/item.h	2007-09-14 14:17:40 +04:00
@@ -569,6 +569,43 @@ public:
   virtual enum_monotonicity_info get_monotonicity_info() const
   { return NON_MONOTONIC; }
 
+  /*
+    Convert "func_arg $CMP$ const" half-interval into "FUNC(func_arg) $CMP2$ const2"
+
+    SYNOPSIS
+      val_int_endpoint()
+        left_endp  FALSE  <=> The interval is "x < const" or "x <= const"
+                   TRUE   <=> The interval is "x > const" or "x >= const"
+
+        incl_endp  IN   TRUE <=> the comparison is '<' or '>'
+                        FALSE <=> the comparison is '<=' or '>='
+                   OUT  The same but for the "F(x) $CMP$ F(const)" comparison
+
+    DESCRIPTION
+      This function is defined only for unary monotonic functions. The caller
+      supplies the source half-interval
+
+         x $CMP$ const
+
+      The value of const is supplied implicitly as the value this item's
+      argument, the form of $CMP$ comparison is specified through the
+      function's arguments. The calle returns the result interval
+         
+         F(x) $CMP2$ F(const)
+      
+      passing back F(const) as the return value, and the form of $CMP2$ 
+      through the out parameter. NULL values are assumed to be comparable and
+      be less than any non-NULL values.
+
+    RETURN
+      The output range bound, which equal to the value of val_int()
+        - If the value of the function is NULL then the bound is the 
+          smallest possible value of LONGLONG_MIN 
+  */
+  virtual longlong val_int_endpoint(bool left_endp, bool *incl_endp)
+  { DBUG_ASSERT(0); }
+
+
   /* valXXX methods must return NULL or 0 or 0.0 if null_value is set. */
   /*
     Return double precision floating point representation of item.
@@ -1401,6 +1438,7 @@ public:
   {
     return MONOTONIC_STRICT_INCREASING;
   }
+  longlong val_int_endpoint(bool left_endp, bool *incl_endp);
   Field *get_tmp_table_field() { return result_field; }
   Field *tmp_table_field(TABLE *t_arg) { return result_field; }
   bool get_date(MYSQL_TIME *ltime,uint fuzzydate);
diff -Nrup a/sql/item_timefunc.cc b/sql/item_timefunc.cc
--- a/sql/item_timefunc.cc	2007-08-31 09:57:35 +04:00
+++ b/sql/item_timefunc.cc	2007-09-14 14:17:40 +04:00
@@ -962,6 +962,44 @@ enum_monotonicity_info Item_func_to_days
 }
 
 
+longlong Item_func_to_days::val_int_endpoint(bool left_endp, bool *incl_endp)
+{
+  DBUG_ASSERT(fixed == 1);
+  MYSQL_TIME ltime;
+  longlong res;
+  if (get_arg0_date(&ltime, TIME_NO_ZERO_DATE))
+  {
+    /* got NULL, leave the incl_endp intact */
+    return LONGLONG_MIN;
+  }
+  res=(longlong) calc_daynr(ltime.year,ltime.month,ltime.day);
+  
+  if (args[0]->field_type() == MYSQL_TYPE_DATE)
+  {
+    // TO_DAYS() is strictly monotonic for dates, leave incl_endp intact
+    return res;
+  }
+ 
+  /*
+    Handle the special but practically useful case of datetime values that
+    point to day bound ("strictly less" comparison stays intact):
+
+      col < '2007-09-15 00:00:00'  -> TO_DAYS(col) <  TO_DAYS('2007-09-15')
+
+    which is different from the general case ("strictly less" changes to
+    "less or equal"):
+
+      col < '2007-09-15 12:34:56'  -> TO_DAYS(col) <= TO_DAYS('2007-09-15')
+  */
+  if (!left_endp && !(ltime.hour || ltime.minute || ltime.second ||
+                      ltime.second_part))
+    ; /* do nothing */
+  else
+    *incl_endp= TRUE;
+  return res;
+}
+
+
 longlong Item_func_dayofyear::val_int()
 {
   DBUG_ASSERT(fixed == 1);
@@ -1152,7 +1190,7 @@ longlong Item_func_year::val_int()
   Get information about this Item tree monotonicity
 
   SYNOPSIS
-    Item_func_to_days::get_monotonicity_info()
+    Item_func_year::get_monotonicity_info()
 
   DESCRIPTION
   Get information about monotonicity of the function represented by this item
@@ -1170,6 +1208,37 @@ enum_monotonicity_info Item_func_year::g
     return MONOTONIC_INCREASING;
   return NON_MONOTONIC;
 }
+
+
+longlong Item_func_year::val_int_endpoint(bool left_endp, bool *incl_endp)
+{
+  DBUG_ASSERT(fixed == 1);
+  MYSQL_TIME ltime;
+  if (get_arg0_date(&ltime, TIME_FUZZY_DATE))
+  {
+    /* got NULL, leave the incl_endp intact */
+    return LONGLONG_MIN;
+  }
+
+  /*
+    Handle the special but practically useful case of datetime values that
+    point to year bound ("strictly less" comparison stays intact) :
+
+      col < '2007-01-01 00:00:00'  -> YEAR(col) <  2007
+
+    which is different from the general case ("strictly less" changes to
+    "less or equal"):
+
+      col < '2007-09-15 23:00:00'  -> YEAR(col) <= 2007
+  */
+  if (!left_endp && ltime.day == 1 && ltime.month == 1 && 
+      !(ltime.hour || ltime.minute || ltime.second || ltime.second_part))
+    ; /* do nothing */
+  else
+    *incl_endp= TRUE;
+  return ltime.year;
+}
+
 
 longlong Item_func_unix_timestamp::val_int()
 {
diff -Nrup a/sql/item_timefunc.h b/sql/item_timefunc.h
--- a/sql/item_timefunc.h	2007-06-13 18:12:00 +04:00
+++ b/sql/item_timefunc.h	2007-09-14 14:17:40 +04:00
@@ -68,6 +68,7 @@ public:
     maybe_null=1; 
   }
   enum_monotonicity_info get_monotonicity_info() const;
+  longlong val_int_endpoint(bool left_endp, bool *incl_endp);
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
 };
 
@@ -248,6 +249,7 @@ public:
   longlong val_int();
   const char *func_name() const { return "year"; }
   enum_monotonicity_info get_monotonicity_info() const;
+  longlong val_int_endpoint(bool left_endp, bool *incl_endp);
   void fix_length_and_dec()
   { 
     decimals=0;
diff -Nrup a/sql/partition_info.h b/sql/partition_info.h
--- a/sql/partition_info.h	2007-07-04 23:55:21 +04:00
+++ b/sql/partition_info.h	2007-09-14 14:17:40 +04:00
@@ -139,20 +139,6 @@ public:
   */
   get_partitions_in_range_iter get_subpart_iter_for_interval;
   
-  /*
-    Valid iff
-    get_part_iter_for_interval=get_part_iter_for_interval_via_walking:
-      controls how we'll process "field < C" and "field > C" intervals.
-      If the partitioning function F is strictly increasing, then for any x, y
-      "x < y" => "F(x) < F(y)" (*), i.e. when we get interval "field < C" 
-      we can perform partition pruning on the equivalent "F(field) < F(C)".
-
-      If the partitioning function not strictly increasing (it is simply
-      increasing), then instead of (*) we get "x < y" => "F(x) <= F(y)"
-      i.e. for interval "field < C" we can perform partition pruning for
-      "F(field) <= F(C)".
-  */
-  bool range_analysis_include_bounds;
   /********************************************
    * INTERVAL ANALYSIS ENDS 
    ********************************************/
diff -Nrup a/sql/sql_partition.cc b/sql/sql_partition.cc
--- a/sql/sql_partition.cc	2007-07-19 20:05:49 +04:00
+++ b/sql/sql_partition.cc	2007-09-14 14:17:40 +04:00
@@ -2743,7 +2743,8 @@ uint32 get_list_array_idx_for_endpoint(p
   uint min_list_index= 0, max_list_index= part_info->no_list_values - 1;
   longlong list_value;
   /* Get the partitioning function value for the endpoint */
-  longlong part_func_value= part_val_int(part_info->part_expr);
+  longlong part_func_value= 
+    part_info->part_expr->val_int_endpoint(left_endpoint, &include_endpoint);
   bool unsigned_flag= part_info->part_expr->unsigned_flag;
   DBUG_ENTER("get_list_array_idx_for_endpoint");
 
@@ -2887,7 +2888,9 @@ uint32 get_partition_id_range_for_endpoi
   uint max_partition= part_info->no_parts - 1;
   uint min_part_id= 0, max_part_id= max_partition, loc_part_id;
   /* Get the partitioning function value for the endpoint */
-  longlong part_func_value= part_val_int(part_info->part_expr);
+  longlong part_func_value= 
+    part_info->part_expr->val_int_endpoint(left_endpoint, &include_endpoint);
+
   bool unsigned_flag= part_info->part_expr->unsigned_flag;
   DBUG_ENTER("get_partition_id_range_for_endpoint");
 
@@ -6590,8 +6593,6 @@ void make_used_partitions_str(partition_
 #ifdef WITH_PARTITION_STORAGE_ENGINE
 static void set_up_range_analysis_info(partition_info *part_info)
 {
-  enum_monotonicity_info minfo;
-
   /* Set the catch-all default */
   part_info->get_part_iter_for_interval= NULL;
   part_info->get_subpart_iter_for_interval= NULL;
@@ -6603,11 +6604,8 @@ static void set_up_range_analysis_info(p
   switch (part_info->part_type) {
   case RANGE_PARTITION:
   case LIST_PARTITION:
-    minfo= part_info->part_expr->get_monotonicity_info();
-    if (minfo != NON_MONOTONIC)
+    if (part_info->part_expr->get_monotonicity_info() != NON_MONOTONIC)
     {
-      part_info->range_analysis_include_bounds=
-        test(minfo == MONOTONIC_INCREASING);
       part_info->get_part_iter_for_interval=
         get_part_iter_for_interval_via_mapping;
       goto setup_subparts;
@@ -6775,8 +6773,7 @@ int get_part_iter_for_interval_via_mappi
         index-in-ordered-array-of-list-constants (for LIST) space.
       */
       store_key_image_to_rec(field, min_value, field_len);
-      bool include_endp= part_info->range_analysis_include_bounds ||
-                         !test(flags & NEAR_MIN);
+      bool include_endp= !test(flags & NEAR_MIN);
       part_iter->part_nums.start= get_endpoint(part_info, 1, include_endp);
       part_iter->part_nums.cur= part_iter->part_nums.start;
       if (part_iter->part_nums.start == max_endpoint_val)
@@ -6790,8 +6787,7 @@ int get_part_iter_for_interval_via_mappi
   else
   {
     store_key_image_to_rec(field, max_value, field_len);
-    bool include_endp= part_info->range_analysis_include_bounds ||
-                       !test(flags & NEAR_MAX);
+    bool include_endp= !test(flags & NEAR_MAX);
     part_iter->part_nums.end= get_endpoint(part_info, 0, include_endp);
     if (part_iter->part_nums.start == part_iter->part_nums.end &&
         !part_iter->ret_null_part)
Thread
bk commit into 5.1 tree (sergefp:1.2603) BUG#27927Sergey Petrunia14 Sep