merge

5 files changed, 90 insertions, 10 deletions

diff --git a/sql/item.h b/sql/item.h
index 464085cb101..808e2370b1e 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -397,13 +397,20 @@ public:
   from INT_RESULT, may be NULL, or are unsigned.
   It will be possible to address this issue once the related partitioning bugs
   (BUG#16002, BUG#15447, BUG#13436) are fixed.
+
+  The NOT_NULL enums are used in TO_DAYS, since TO_DAYS('2001-00-00') returns
+  NULL which puts those rows into the NULL partition, but
+  '2000-12-31' < '2001-00-00' < '2001-01-01'. So special handling is needed
+  for this (see Bug#20577).
 */
 
 typedef enum monotonicity_info 
 {
    NON_MONOTONIC,              /* none of the below holds */
    MONOTONIC_INCREASING,       /* F() is unary and (x < y) => (F(x) <= F(y)) */
-   MONOTONIC_STRICT_INCREASING /* F() is unary and (x < y) => (F(x) <  F(y)) */
+   MONOTONIC_INCREASING_NOT_NULL,  /* But only for valid/real x and y */
+   MONOTONIC_STRICT_INCREASING,/* F() is unary and (x < y) => (F(x) <  F(y)) */
+   MONOTONIC_STRICT_INCREASING_NOT_NULL  /* But only for valid/real x and y */
 } enum_monotonicity_info;
 
 /*************************************************************************/
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index d79b0b02998..eefe47232ae 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -960,9 +960,9 @@ enum_monotonicity_info Item_func_to_days::get_monotonicity_info() const
   if (args[0]->type() == Item::FIELD_ITEM)
   {
     if (args[0]->field_type() == MYSQL_TYPE_DATE)
-      return MONOTONIC_STRICT_INCREASING;
+      return MONOTONIC_STRICT_INCREASING_NOT_NULL;
     if (args[0]->field_type() == MYSQL_TYPE_DATETIME)
-      return MONOTONIC_INCREASING;
+      return MONOTONIC_INCREASING_NOT_NULL;
   }
   return NON_MONOTONIC;
 }
@@ -973,12 +973,27 @@ 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))
+  int dummy;                                /* unused */
+  if (get_arg0_date(&ltime, TIME_FUZZY_DATE))
   {
     /* got NULL, leave the incl_endp intact */
     return LONGLONG_MIN;
   }
   res=(longlong) calc_daynr(ltime.year,ltime.month,ltime.day);
+  /* Set to NULL if invalid date, but keep the value */
+  null_value= check_date(&ltime,
+                         (ltime.year || ltime.month || ltime.day),
+                         (TIME_NO_ZERO_IN_DATE | TIME_NO_ZERO_DATE),
+                         &dummy);
+  if (null_value)
+  {
+    /*
+      Even if the evaluation return NULL, the calc_daynr is useful for pruning
+    */
+    if (args[0]->field_type() != MYSQL_TYPE_DATE)
+      *incl_endp= TRUE;
+    return res;
+  }
   
   if (args[0]->field_type() == MYSQL_TYPE_DATE)
   {
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 47067c03a85..8dc2f4683dd 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -5826,6 +5826,7 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND *conf_func, Field *field,
       {
         tree= new (alloc) SEL_ARG(field, 0, 0);
         tree->type= SEL_ARG::IMPOSSIBLE;
+        field->table->in_use->variables.sql_mode= orig_sql_mode;
         goto end;
       }
       else
@@ -5859,7 +5860,10 @@ get_mm_leaf(RANGE_OPT_PARAM *param, COND *conf_func, Field *field,
           */
         }
         else
+        {
+          field->table->in_use->variables.sql_mode= orig_sql_mode;
           goto end;
+        }
       }
     }
 
diff --git a/sql/partition_info.h b/sql/partition_info.h
index 415f955d5d4..8832b6ee409 100644
--- a/sql/partition_info.h
+++ b/sql/partition_info.h
@@ -300,6 +300,7 @@ static inline void init_single_partition_iterator(uint32 part_id,
 {
   part_iter->part_nums.start= part_iter->part_nums.cur= part_id;
   part_iter->part_nums.end= part_id+1;
+  part_iter->ret_null_part= part_iter->ret_null_part_orig= FALSE;
   part_iter->get_next= get_next_partition_id_range;
 }
 
diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc
index 61766e5c509..ad24af12087 100644
--- a/sql/sql_partition.cc
+++ b/sql/sql_partition.cc
@@ -2766,8 +2766,24 @@ uint32 get_list_array_idx_for_endpoint(partition_info *part_info,
 
   if (part_info->part_expr->null_value)
   {
-    DBUG_RETURN(0);
+    /*
+      Special handling for MONOTONIC functions that can return NULL for
+      values that are comparable. I.e.
+      '2000-00-00' can be compared to '2000-01-01' but TO_DAYS('2000-00-00')
+      returns NULL which cannot be compared used <, >, <=, >= etc.
+
+      Otherwise, just return the the first index (lowest value).
+    */
+    enum_monotonicity_info monotonic;
+    monotonic= part_info->part_expr->get_monotonicity_info();
+    if (monotonic != MONOTONIC_INCREASING_NOT_NULL && 
+        monotonic != MONOTONIC_STRICT_INCREASING_NOT_NULL)
+    {
+      /* F(col) can not return NULL, return index with lowest value */
+      DBUG_RETURN(0);
+    }
   }
+
   if (unsigned_flag)
     part_func_value-= 0x8000000000000000ULL;
   DBUG_ASSERT(part_info->no_list_values);
@@ -2916,11 +2932,29 @@ uint32 get_partition_id_range_for_endpoint(partition_info *part_info,
 
   if (part_info->part_expr->null_value)
   {
-    uint32 ret_part_id= 0;
-    if (!left_endpoint && include_endpoint)
-      ret_part_id= 1;
-    DBUG_RETURN(ret_part_id);
+    /*
+      Special handling for MONOTONIC functions that can return NULL for
+      values that are comparable. I.e.
+      '2000-00-00' can be compared to '2000-01-01' but TO_DAYS('2000-00-00')
+      returns NULL which cannot be compared used <, >, <=, >= etc.
+
+      Otherwise, just return the first partition
+      (may be included if not left endpoint)
+    */
+    enum_monotonicity_info monotonic;
+    monotonic= part_info->part_expr->get_monotonicity_info();
+    if (monotonic != MONOTONIC_INCREASING_NOT_NULL &&
+        monotonic != MONOTONIC_STRICT_INCREASING_NOT_NULL)
+    {
+      /* F(col) can not return NULL, return partition with lowest value */
+      if (!left_endpoint && include_endpoint)
+        DBUG_RETURN(1);
+      DBUG_RETURN(0);               
+
+    }
   }
+
+
   if (unsigned_flag)
     part_func_value-= 0x8000000000000000ULL;
   if (left_endpoint && !include_endpoint)
@@ -6733,6 +6767,19 @@ int get_part_iter_for_interval_via_mapping(partition_info *part_info,
   }
   else
     assert(0);
+  
+  if (part_info->part_type == RANGE_PARTITION ||
+      part_info->has_null_value)
+  {
+    enum_monotonicity_info monotonic;
+    monotonic= part_info->part_expr->get_monotonicity_info();
+    if (monotonic == MONOTONIC_INCREASING_NOT_NULL ||
+        monotonic == MONOTONIC_STRICT_INCREASING_NOT_NULL)
+    {
+      /* col is NOT NULL, but F(col) can return NULL, add NULL partition */
+      part_iter->ret_null_part= part_iter->ret_null_part_orig= TRUE;
+    }
+  }
 
   /* 
     Find minimum: Do special handling if the interval has left bound in form
@@ -6959,7 +7006,13 @@ uint32 get_next_partition_id_range(PARTITION_ITERATOR* part_iter)
 {
   if (part_iter->part_nums.cur >= part_iter->part_nums.end)
   {
+    if (part_iter->ret_null_part)
+    {
+      part_iter->ret_null_part= FALSE;
+      return 0;                    /* NULL always in first range partition */
+    }
     part_iter->part_nums.cur= part_iter->part_nums.start;
+    part_iter->ret_null_part= part_iter->ret_null_part_orig;
     return NOT_A_PARTITION_ID;
   }
   else
@@ -6987,7 +7040,7 @@ uint32 get_next_partition_id_range(PARTITION_ITERATOR* part_iter)
 
 uint32 get_next_partition_id_list(PARTITION_ITERATOR *part_iter)
 {
-  if (part_iter->part_nums.cur == part_iter->part_nums.end)
+  if (part_iter->part_nums.cur >= part_iter->part_nums.end)
   {
     if (part_iter->ret_null_part)
     {