bk commit into 5.1 tree (sergefp:1.2281) BUG#18558

[Sergey Petrunia]

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
  1.2281 06/04/05 02:55:03 sergefp@stripped +6 -0
  BUG#18558: 
  * Produce right results for conditions that were transformed to "(partitioning_range)
AND
    (list_of_subpartitioning_ranges)": make each partition id set iterator auto-reset
itself
    after it has returned all partition ids in the sequence 
  * Fix "Range mapping" and "Range mapping" partitioning interval analysis functions to 
    correctly deal with NULL values. 

  sql/sql_partition.h
    1.7 06/04/05 02:54:55 sergefp@stripped +17 -1
    BUG#18558: 
     - Make each partition set iterator auto-reset itself after it has returned all 
       partition ids in the set it enumerates. 
     - Rename PARTITION_ITERATOR::has_null_value to ret_null_part

  sql/sql_partition.cc
    1.64 06/04/05 02:54:55 sergefp@stripped +65 -42
    BUG#18558: 
    - Make each partition set iterator auto-reset itself after it has returned all 
      partition ids in the set it enumerates. 
    - Fix partition interval analysis to correctly handle intervals with one or both
      NULL bounds. 

  sql/partition_info.h
    1.7 06/04/05 02:54:55 sergefp@stripped +2 -2
    BUG#18558: Make each partition set iterator auto-reset itself after it has returned
all 
    partition ids in the set it enumerates. 

  sql/opt_range.cc
    1.210 06/04/05 02:54:55 sergefp@stripped +1 -3
    BUG#18558: Move partition set iterator initialization to sql_partition.cc, comment
fixes

  mysql-test/t/partition_pruning.test
    1.11 06/04/05 02:54:54 sergefp@stripped +59 -0
    Testcase for BUG#18558

  mysql-test/r/partition_pruning.result
    1.12 06/04/05 02:54:54 sergefp@stripped +62 -0
    Testcase for BUG#18558

# 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:	sergefp
# Host:	newbox.mylan
# Root:	/home/psergey/mysql-5.1-bug18558-pnd

--- 1.209/sql/opt_range.cc	2006-03-31 15:19:57 +04:00
+++ 1.210/sql/opt_range.cc	2006-04-05 02:54:55 +04:00
@@ -2296,8 +2296,6 @@
   RANGE_OPT_PARAM  *range_par= &prune_param.range_param;
 
   prune_param.part_info= part_info;
-  prune_param.part_iter.has_null_value= FALSE;
-
   init_sql_alloc(&alloc, thd->variables.range_alloc_block_size, 0);
   range_par->mem_root= &alloc;
   range_par->old_root= thd->mem_root;
@@ -2730,7 +2728,7 @@
                                       key_tree->min_flag | key_tree->max_flag,
                                       &ppar->part_iter);
       if (!res)
-        goto go_right; /* res=0 --> no satisfying partitions */
+        goto go_right; /* res==0 --> no satisfying partitions */
       if (res == -1)
       {
         //get a full range iterator

--- 1.63/sql/sql_partition.cc	2006-04-04 00:52:11 +04:00
+++ 1.64/sql/sql_partition.cc	2006-04-05 02:54:55 +04:00
@@ -5269,7 +5269,7 @@
   }
    
   /*
-    Check get_part_iter_for_interval_via_walking() can be used for
+    Check if get_part_iter_for_interval_via_walking() can be used for
     partitioning
   */
   if (part_info->no_part_fields == 1)
@@ -5291,7 +5291,7 @@
 
 setup_subparts:
   /*
-    Check get_part_iter_for_interval_via_walking() can be used for
+    Check if get_part_iter_for_interval_via_walking() can be used for
     subpartitioning
   */
   if (part_info->no_subpart_fields == 1)
@@ -5374,40 +5374,45 @@
     max_endpoint_val=    part_info->no_list_values;
     part_iter->get_next= get_next_partition_id_list;
     part_iter->part_info= part_info;
+    part_iter->ret_null_part= part_iter->ret_null_part_orig= FALSE;
   }
   else
     DBUG_ASSERT(0);
 
-  if (field->real_maybe_null() && part_info->has_null_value)
+  /* Find minimum */
+  if (field->real_maybe_null() && part_info->has_null_value && 
+      !(flags & (NO_MIN_RANGE | NEAR_MIN)) && *min_value)
   {
-    if (*min_value)
+    /* The interval's left bound is " NULL <= X " */
+    part_iter->ret_null_part= part_iter->ret_null_part_orig= TRUE;
+    part_iter->part_nums.start= part_iter->part_nums.cur= 0;
+    if (*max_value && !(flags & NO_MAX_RANGE))
     {
-      if (*max_value && !(flags & (NO_MIN_RANGE | NO_MAX_RANGE)))
-      {
-        init_single_partition_iterator(part_info->has_null_part_id, part_iter);
-        return 1;
-      }
-      if (!(flags & NEAR_MIN))
-        part_iter->has_null_value= TRUE;
+      /* The right bound is X <= NULL, i.e. it is a "X IS NULL" interval */
+      part_iter->part_nums.end= 0;
+      return 1;
     }
   }
-  /* Find minimum */
-  if (flags & NO_MIN_RANGE)
-    part_iter->part_nums.start= 0;
   else
   {
-    /*
-      Store the interval edge in the record buffer, and call the
-      function that maps the edge in table-field space to an edge
-      in ordered-set-of-partitions (for RANGE partitioning) or 
-      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);
-    part_iter->part_nums.start= get_endpoint(part_info, 1, include_endp);
-    if (part_iter->part_nums.start == max_endpoint_val)
-      return 0; /* No partitions */
+    if (flags & NO_MIN_RANGE)
+      part_iter->part_nums.start= part_iter->part_nums.cur= 0;
+    else
+    {
+      /*
+        Store the interval edge in the record buffer, and call the
+        function that maps the edge in table-field space to an edge
+        in ordered-set-of-partitions (for RANGE partitioning) or 
+        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);
+      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)
+        return 0; /* No partitions */
+    }
   }
 
   /* Find maximum, do the same as above but for right interval bound */
@@ -5419,7 +5424,8 @@
     bool include_endp= part_info->range_analysis_include_bounds ||
                        !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)
+    if (part_iter->part_nums.start == part_iter->part_nums.end &&
+        !part_iter->ret_null_part)
       return 0; /* No partitions */
   }
   return 1; /* Ok, iterator initialized */
@@ -5534,8 +5540,13 @@
     return 0; /* No partitions match */
   }
 
-  if (flags & (NO_MIN_RANGE | NO_MAX_RANGE))
+  if ((field->real_maybe_null() && 
+       ((!(flags & NO_MIN_RANGE) && *min_value) ||  // NULL <? X
+        (!(flags & NO_MAX_RANGE) && *max_value))) ||  // X <? NULL
+      (flags & (NO_MIN_RANGE | NO_MAX_RANGE)))    // -inf at any bound
+  {
     return -1; /* Can't handle this interval, have to use all partitions */
+  }
   
   /* Get integers for left and right interval bound */
   longlong a, b;
@@ -5553,7 +5564,7 @@
   if (n_values > total_parts || n_values > MAX_RANGE_TO_WALK)
     return -1;
 
-  part_iter->field_vals.start= a;
+  part_iter->field_vals.start= part_iter->field_vals.cur= a;
   part_iter->field_vals.end=   b;
   part_iter->part_info= part_info;
   part_iter->get_next=  get_next_func;
@@ -5565,12 +5576,13 @@
   PARTITION_ITERATOR::get_next implementation: enumerate partitions in range
 
   SYNOPSIS
-    get_next_partition_id_list()
+    get_next_partition_id_range()
       part_iter  Partition set iterator structure
 
   DESCRIPTION
     This is implementation of PARTITION_ITERATOR::get_next() that returns
     [sub]partition ids in [min_partition_id, max_partition_id] range.
+    The function conforms to partition_iter_func type.
 
   RETURN
     partition id
@@ -5579,10 +5591,13 @@
 
 uint32 get_next_partition_id_range(PARTITION_ITERATOR* part_iter)
 {
-  if (part_iter->part_nums.start== part_iter->part_nums.end)
+  if (part_iter->part_nums.cur == part_iter->part_nums.end)
+  {
+    part_iter->part_nums.cur= part_iter->part_nums.start;
     return NOT_A_PARTITION_ID;
+  }
   else
-    return part_iter->part_nums.start++;
+    return part_iter->part_nums.cur++;
 }
 
 
@@ -5597,6 +5612,7 @@
     This implementation of PARTITION_ITERATOR::get_next() is special for 
     LIST partitioning: it enumerates partition ids in 
     part_info->list_array[i] where i runs over [min_idx, max_idx] interval.
+    The function conforms to partition_iter_func type.
 
   RETURN 
     partition id
@@ -5605,18 +5621,20 @@
 
 uint32 get_next_partition_id_list(PARTITION_ITERATOR *part_iter)
 {
-  if (part_iter->part_nums.start == part_iter->part_nums.end)
+  if (part_iter->part_nums.cur == part_iter->part_nums.end)
   {
-    if (part_iter->has_null_value)
+    if (part_iter->ret_null_part)
     {
-      part_iter->has_null_value= FALSE;
+      part_iter->ret_null_part= FALSE;
       return part_iter->part_info->has_null_part_id;
     }
+    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
     return part_iter->part_info->list_array[part_iter->
-                                            part_nums.start++].partition_id;
+                                            part_nums.cur++].partition_id;
 }
 
 
@@ -5631,6 +5649,7 @@
     This implementation of PARTITION_ITERATOR::get_next() returns ids of
     partitions that contain records with partitioning field value within
     [start_val, end_val] interval.
+    The function conforms to partition_iter_func type.
 
   RETURN 
     partition id
@@ -5641,11 +5660,10 @@
 {
   uint32 part_id;
   Field *field= part_iter->part_info->part_field_array[0];
-  while (part_iter->field_vals.start != part_iter->field_vals.end)
+  while (part_iter->field_vals.cur != part_iter->field_vals.end)
   {
-    field->store(part_iter->field_vals.start, FALSE);
-    part_iter->field_vals.start++;
     longlong dummy;
+    field->store(part_iter->field_vals.cur++, FALSE);
     if (part_iter->part_info->is_sub_partitioned() &&
         !part_iter->part_info->get_part_partition_id(part_iter->part_info,
                                                      &part_id, &dummy) ||
@@ -5653,6 +5671,9 @@
                                                 &part_id, &dummy))
       return part_id;
   }
+  //psergey-todo: return partition(part_func(NULL)) here...
+  
+  part_iter->field_vals.cur= part_iter->field_vals.start;
   return NOT_A_PARTITION_ID;
 }
 
@@ -5663,10 +5684,12 @@
 {
   uint32 part_id;
   Field *field= part_iter->part_info->subpart_field_array[0];
-  if (part_iter->field_vals.start == part_iter->field_vals.end)
+  if (part_iter->field_vals.cur == part_iter->field_vals.end)
+  {
+    part_iter->field_vals.cur= part_iter->field_vals.start;
     return NOT_A_PARTITION_ID;
-  field->store(part_iter->field_vals.start, FALSE);
-  part_iter->field_vals.start++;
+  }
+  field->store(part_iter->field_vals.cur++, FALSE);
   return part_iter->part_info->get_subpartition_id(part_iter->part_info);
 }
 #endif

--- 1.6/sql/partition_info.h	2006-03-31 21:39:35 +04:00
+++ 1.7/sql/partition_info.h	2006-04-05 02:54:55 +04:00
@@ -267,7 +267,7 @@
 static inline void init_single_partition_iterator(uint32 part_id,
                                            PARTITION_ITERATOR *part_iter)
 {
-  part_iter->part_nums.start= part_id;
+  part_iter->part_nums.start= part_iter->part_nums.cur= part_id;
   part_iter->part_nums.end= part_id+1;
   part_iter->get_next= get_next_partition_id_range;
 }
@@ -277,7 +277,7 @@
 void init_all_partitions_iterator(partition_info *part_info,
                                   PARTITION_ITERATOR *part_iter)
 {
-  part_iter->part_nums.start= 0;
+  part_iter->part_nums.start= part_iter->part_nums.cur= 0;
   part_iter->part_nums.end= part_info->no_parts;
   part_iter->get_next= get_next_partition_id_range;
 }

--- 1.6/sql/sql_partition.h	2006-03-28 16:25:13 +04:00
+++ 1.7/sql/sql_partition.h	2006-04-05 02:54:55 +04:00
@@ -94,10 +94,20 @@
 
 /*
   A "Get next" function for partition iterator.
+
   SYNOPSIS
     partition_iter_func()
       part_iter  Partition iterator, you call only "iter.get_next(&iter)"
 
+  DESCRIPTION
+    Depending on whether partitions or sub-partitions are iterated, the
+    function returns next subpartition id/partition number. The sequence of
+    returned numbers is not ordered and may contain duplicates.
+
+    When the end of sequence is reached, NOT_A_PARTITION_ID is returned, and 
+    the iterator resets itself (so next get_next() call will start to 
+    enumerate the set all over again).
+
   RETURN 
     NOT_A_PARTITION_ID if there are no more partitions.
     [sub]partition_id  of the next partition
@@ -124,16 +134,22 @@
 typedef struct st_partition_iter
 {
   partition_iter_func get_next;
-  bool has_null_value;
+  /* 
+    Valid for "Interval mapping" in LIST partitioning: if true, let the
+    iterator also produce id of the partition that contains NULL value.
+  */
+  bool ret_null_part, ret_null_part_orig;
   struct st_part_num_range
   {
     uint32 start;
+    uint32 cur;
     uint32 end;
   };
 
   struct st_field_value_range
   {
     longlong start;
+    longlong cur;
     longlong end;
   };
 

--- 1.11/mysql-test/r/partition_pruning.result	2006-04-04 00:52:11 +04:00
+++ 1.12/mysql-test/r/partition_pruning.result	2006-04-05 02:54:54 +04:00
@@ -597,3 +597,65 @@
 explain partitions select * from t1 where f_int1 is null;
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	part4_p2sp0	system	NULL	NULL	NULL	NULL	1	
+drop table t1;
+create table t1 (a int not null, b int not null)
+partition by list(a) 
+subpartition by hash(b) subpartitions 4 
+(
+partition p0 values in (1),
+partition p1 values in (2),
+partition p2 values in (3)
+);
+insert into t1 values (1,1),(1,2),(1,3),(1,4),
+(2,1),(2,2),(2,3),(2,4);
+explain partitions select * from t1 where a=1 AND (b=1 OR b=2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0_p0sp1,p0_p0sp2	ALL	NULL	NULL	NULL	NULL	2	Using where
+drop table t1;
+create table t1 (a int, b int not null)
+partition by list(a) 
+subpartition by hash(b) subpartitions 2
+(
+partition p0 values in (1),
+partition p1 values in (2),
+partition p2 values in (3),
+partition pn values in (NULL)
+);
+insert into t1 values (1,1),(1,2),(1,3),(1,4),
+(2,1),(2,2),(2,3),(2,4), (NULL,1);
+explain partitions select * from t1 where a IS NULL AND (b=1 OR b=2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	pn_p3sp0,pn_p3sp1	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t1 where (a IS NULL or a < 1) AND (b=1 OR b=2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	pn_p3sp0,pn_p3sp1	system	NULL	NULL	NULL	NULL	1	
+explain partitions select * from t1 where (a IS NULL or a < 2) AND (b=1 OR b=2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0_p0sp0,p0_p0sp1,pn_p3sp0,pn_p3sp1	ALL	NULL	NULL	NULL	NULL	5	Using where
+explain partitions select * from t1 where (a IS NULL or a <= 1) AND (b=1 OR b=2);
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0_p0sp0,p0_p0sp1,pn_p3sp0,pn_p3sp1	ALL	NULL	NULL	NULL	NULL	5	Using where
+drop table t1;
+create table t1 ( a int)  partition by list (MOD(a, 10))
+( partition p0  values in (0), partition p1 values in (1),
+partition p2 values in (2), partition p3 values in (3),
+partition p4 values in (4), partition p5 values in (5),
+partition p6 values in (6), partition pn values in (NULL)
+);
+insert into t1 values (NULL), (0),(1),(2),(3),(4),(5),(6);
+explain partitions select * from t1 where a is null or a < 2;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p0,p1,p2,p3,p4,p5,p6,pn	ALL	NULL	NULL	NULL	NULL	8	Using where
+drop table t1;
+create table t1 (s1 int) partition by list (s1) 
+(partition p1 values in (0), 
+partition p2 values in (1), 
+partition p3 values in (null));
+insert into t1 values (0),(1),(null);
+select count(*) from t1 where s1 < 0 or s1 is null;
+count(*)
+1
+explain partitions select count(*) from t1 where s1 < 0 or s1 is null;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p3	system	NULL	NULL	NULL	NULL	1	
+drop table t1;

--- 1.10/mysql-test/t/partition_pruning.test	2006-04-04 00:52:11 +04:00
+++ 1.11/mysql-test/t/partition_pruning.test	2006-04-05 02:54:54 +04:00
@@ -492,6 +492,65 @@
 
 select * from t1 where f_int1 is null;
 explain partitions select * from t1 where f_int1 is null;
+drop table t1;
+
+#
+# BUG#18558
+#
+create table t1 (a int not null, b int not null)
+partition by list(a) 
+  subpartition by hash(b) subpartitions 4 
+(
+  partition p0 values in (1),
+  partition p1 values in (2),
+  partition p2 values in (3)
+);
+insert into t1 values (1,1),(1,2),(1,3),(1,4),
+  (2,1),(2,2),(2,3),(2,4);
+explain partitions select * from t1 where a=1 AND (b=1 OR b=2);
+drop table t1;
+
+create table t1 (a int, b int not null)
+partition by list(a) 
+  subpartition by hash(b) subpartitions 2
+(
+  partition p0 values in (1),
+  partition p1 values in (2),
+  partition p2 values in (3),
+  partition pn values in (NULL)
+);
+insert into t1 values (1,1),(1,2),(1,3),(1,4),
+  (2,1),(2,2),(2,3),(2,4), (NULL,1);
+
+explain partitions select * from t1 where a IS NULL AND (b=1 OR b=2);
+
+explain partitions select * from t1 where (a IS NULL or a < 1) AND (b=1 OR b=2);
+explain partitions select * from t1 where (a IS NULL or a < 2) AND (b=1 OR b=2);
+explain partitions select * from t1 where (a IS NULL or a <= 1) AND (b=1 OR b=2);
+
+drop table t1;
+
+create table t1 ( a int)  partition by list (MOD(a, 10))
+( partition p0  values in (0), partition p1 values in (1),
+   partition p2 values in (2), partition p3 values in (3),
+   partition p4 values in (4), partition p5 values in (5),
+   partition p6 values in (6), partition pn values in (NULL)
+);
+insert into t1 values (NULL), (0),(1),(2),(3),(4),(5),(6);
+explain partitions select * from t1 where a is null or a < 2;
+drop table t1;
+
+# Testcase from BUG#18751
+create table t1 (s1 int) partition by list (s1) 
+  (partition p1 values in (0), 
+   partition p2 values in (1), 
+   partition p3 values in (null));
+ 
+insert into t1 values (0),(1),(null);
+ 
+select count(*) from t1 where s1 < 0 or s1 is null;
+explain partitions select count(*) from t1 where s1 < 0 or s1 is null;
+drop table t1;
 
 # No tests for NULLs in RANGE(monotonic_expr()) - they depend on BUG#15447
 # being fixed.