bk commit into 5.1 tree (sergefp:1.2260) 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.2260 06/03/31 14:26:49 sergefp@stripped +6 -0
  Fix for BUG#18558: Wrong behavior of partition pruning: 
  The bug was that for conditions converted to form 
    "part_range AND (subpart_range1 OR subpart_range2)",
  only partitions that match "part_range AND subpart_range1" were marked. 
  This was caused by how partition pruning worked:
   - create iterator I1 that enumerates partitions that cover "part_range" condition;
  ...
   - create iterator I2 that enumerates subpartitions that cover "subpart_range1"
condition;
   - for each p in I1 for each sp in I2 { mark subpartition sp within partition p as used
};
  ...
   - create iterator I3 that enumerates subpartitions that cover "subpart_range2"
condition;
   - for each p in I1 for each sp in I3  (*)
      { mark subpartition sp within partition p as used }; (**)
  ...
  At location (*) iterator I1 was already at the end-of-set position, so line (**) was
never
  executed. The fix is that now partition set iterators auto-reset themselves when they 
  return end-of-partition-set constant(NOT_A_PARTITION).

  sql/sql_partition.h
    1.7 06/03/31 14:26:43 sergefp@stripped +17 -1
    Fix for BUG#18558: Make each partition set iterator auto-reset itself after it has
returned all 
     partition ids in the set it enumerates. 

  sql/sql_partition.cc
    1.60 06/03/31 14:26:43 sergefp@stripped +54 -39
    Fix for BUG#18558: Make each partition set iterator auto-reset itself after it has
returned all 
     partition ids in the set it enumerates. 

  sql/partition_info.h
    1.6 06/03/31 14:26:43 sergefp@stripped +2 -2
    Fix for 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.209 06/03/31 14:26:43 sergefp@stripped +1 -3
    Fix for BUG#18558: Move partition iterator initialization to sql_partition.cc, comment
fixes

  mysql-test/t/partition_pruning.test
    1.10 06/03/31 14:26:43 sergefp@stripped +36 -0
    Testcase for BUG#18558

  mysql-test/r/partition_pruning.result
    1.11 06/03/31 14:26:43 sergefp@stripped +38 -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-18025-look

--- 1.208/sql/opt_range.cc	2006-03-31 03:45:06 +04:00
+++ 1.209/sql/opt_range.cc	2006-03-31 14:26:43 +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.59/sql/sql_partition.cc	2006-03-31 03:45:06 +04:00
+++ 1.60/sql/sql_partition.cc	2006-03-31 14:26:43 +04:00
@@ -5739,40 +5739,44 @@
     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)
+    //starting from NULL: 
+    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;
+      part_iter->part_nums.end= 0;
+      return 1; /* just NULL */
     }
   }
-  /* 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 */
@@ -5784,7 +5788,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 */
@@ -5914,7 +5919,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;
@@ -5926,12 +5931,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
@@ -5940,10 +5946,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++;
 }
 
 
@@ -5958,6 +5967,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
@@ -5966,18 +5976,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;
 }
 
 
@@ -5992,6 +6004,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
@@ -6002,11 +6015,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) ||
@@ -6014,6 +6026,7 @@
                                                 &part_id, &dummy))
       return part_id;
   }
+  part_iter->field_vals.cur= part_iter->field_vals.start;
   return NOT_A_PARTITION_ID;
 }
 
@@ -6024,10 +6037,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.5/sql/partition_info.h	2006-03-18 17:46:34 +03:00
+++ 1.6/sql/partition_info.h	2006-03-31 14:26:43 +04:00
@@ -261,7 +261,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;
 }
@@ -271,7 +271,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-03-31 14:26:43 +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.10/mysql-test/r/partition_pruning.result	2006-03-31 03:45:06 +04:00
+++ 1.11/mysql-test/r/partition_pruning.result	2006-03-31 14:26:43 +04:00
@@ -583,3 +583,41 @@
 id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t1	p1,p2,p3,p4,p5	ALL	NULL	NULL	NULL	NULL	5	Using where
 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;

--- 1.9/mysql-test/t/partition_pruning.test	2006-03-31 03:45:06 +04:00
+++ 1.10/mysql-test/t/partition_pruning.test	2006-03-31 14:26:43 +04:00
@@ -479,5 +479,41 @@
 explain partitions select * from t1 where a>='a' and a <= 'dddd';
 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;
+
 # No tests for NULLs in RANGE(monotonic_expr()) - they depend on BUG#15447
 # being fixed.