MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Sergey Petrunia Date:September 10 2007 10:17pm
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-11 02:17:19+04:00, sergefp@stripped +7 -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-11 02:17:10+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-11 02:17:10+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-11 02:17:10+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-11 02:17:10+04:00, sergefp@stripped +35 -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-11 02:17:11+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-11 02:17:11+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/sql_partition.cc@stripped, 2007-09-11 02:17:11+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-11 02:17:10 +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-11 02:17:10 +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-11 02:17:10 +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-11 02:17:10 +04:00
@@ -569,6 +569,40 @@ public:
   virtual enum_monotonicity_info get_monotonicity_info() const
   { return NON_MONOTONIC; }
 
+  /*
+    Convert "x $CMP$ arg" half-interval into "f(x) $CMP2$ $funcval"
+
+    SYNOPSIS
+      val_int_endpoint()
+        left_endp  FALSE  <=> this is "F(x) less[-or-equal] someval
+                   TRUE <=>  this is "someval F(x) less[-or-equal] someval
+
+        incl_endp  IN
+                   OUT
+
+    DESCRIPTION
+      This may be called only for unary monotonic functions. The caller
+      supplies the source half-interval
+
+         "x $CMP$ arg" 
+      (value of arg supplied implicitly as the value of this item's argument),
+      and the callee returns some representation of the result
+         
+         f(x) $CMP2$ f(arg)
+      
+      interval. The value of *incl_endp
+       - before the call specifies whether $CMP$ is "less"
+       - after the call specifies the same for $CMP2$.
+
+      NULL values are handled, and as everywhere, we assume that
+
+    RETURN
+      The output range bound. It is equal to value of val_int().
+  */
+  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 +1435,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-11 02:17:11 +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:
+
+      col < '2007-09-15 00:00:00'  -> TO_DAYS(col) <  TO_DAYS('2007-09-15')
+
+    which is different from the general case:
+
+      col < '2007-09-15 23:00:00'  -> 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:
+
+      col < '2007-01-01 00:00:00'  -> YEAR(col) <  2007
+
+    which is different from the general case:
+
+      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-11 02:17:11 +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/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-11 02:17:11 +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 Petrunia11 Sep