From: Date: March 28 2006 2:25pm
Subject: bk commit into 5.1 tree (gluh:1.2246) BUG#18070
List-Archive: http://lists.mysql.com/commits/4234
X-Bug: 18070
Message-Id: <20060328122532.8A1315288D9@eagle.intranet.mysql.r18.ru>

Below is the list of changes that have just been committed into a local
5.1 repository of gluh. When gluh 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.2246 06/03/28 17:25:19 gluh@stripped +5 -0
  Bug#18053 Partitions: crash if null
  Bug#18070 Partitions: wrong result on WHERE ... IS NULL
   removed unnecessary code
   added handling of NULL values

  sql/sql_partition.h
    1.6 06/03/28 17:25:13 gluh@stripped +1 -1
    Bug#18053 Partitions: crash if null
    Bug#18070 Partitions: wrong result on WHERE ... IS NULL
     added has_null_value 

  sql/sql_partition.cc
    1.58 06/03/28 17:25:13 gluh@stripped +20 -3
    Bug#18053 Partitions: crash if null
    Bug#18070 Partitions: wrong result on WHERE ... IS NULL
     removed unnecessary code
     added handling of NULL values

  sql/opt_range.cc
    1.207 06/03/28 17:25:12 gluh@stripped +1 -0
    Bug#18053 Partitions: crash if null
    Bug#18070 Partitions: wrong result on WHERE ... IS NULL
     initialisation of part_iter.has_null_value

  mysql-test/t/partition.test
    1.31 06/03/28 17:25:12 gluh@stripped +75 -0
    Bug#18053 Partitions: crash if null
    Bug#18070 Partitions: wrong result on WHERE ... IS NULL
     test case

  mysql-test/r/partition.result
    1.29 06/03/28 17:25:12 gluh@stripped +101 -0
    Bug#18053 Partitions: crash if null
    Bug#18070 Partitions: wrong result on WHERE ... IS NULL
     test case

# 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:	gluh
# Host:	eagle.intranet.mysql.r18.ru
# Root:	/home/gluh/MySQL/Merge/5.1-new

--- 1.206/sql/opt_range.cc	Fri Mar 24 15:33:04 2006
+++ 1.207/sql/opt_range.cc	Tue Mar 28 17:25:12 2006
@@ -2296,6 +2296,7 @@ bool prune_partitions(THD *thd, TABLE *t
   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;

--- 1.28/mysql-test/r/partition.result	Tue Mar 21 06:52:56 2006
+++ 1.29/mysql-test/r/partition.result	Tue Mar 28 17:25:12 2006
@@ -718,4 +718,105 @@ CALL test.p1(13);
 Warnings:
 Warning	1196	Some non-transactional changed tables couldn't be rolled back
 drop table t1;
+create table t1 (f1 integer) partition by range(f1)
+(partition p1 values less than (0), partition p2 values less than (10));
+insert into t1 set f1 = null;
+select * from t1 where f1 is null;
+f1
+NULL
+explain partitions select * from t1 where f1 is null;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p1	system	NULL	NULL	NULL	NULL	1	
+drop table t1;
+create table t1 (f1 integer) partition by list(f1)
+(partition p1 values in (1), partition p2 values in (null));
+insert into t1 set f1 = null;
+insert into t1 set f1 = 1;
+select * from t1 where f1 is null or f1 = 1;
+f1
+1
+NULL
+drop table t1;
+create table t1 (f1 smallint)
+partition by list (f1) (partition p0 values in (null));
+insert into t1 values (null);
+select * from t1 where f1 is null;
+f1
+NULL
+drop table t1;
+create table t1 (f1 smallint)
+partition by range (f1) (partition p0 values less than (0));
+insert into t1 values (null);
+select * from t1 where f1 is null;
+f1
+NULL
+drop table t1;
+create table t1 (f1 integer) partition by list(f1)
+(
+partition p1 values in (1),
+partition p2 values in (NULL),
+partition p3 values in (2),
+partition p4 values in (3),
+partition p5 values in (4)
+);
+insert into t1 values (1),(2),(3),(4),(null);
+select * from t1 where f1 < 3;
+f1
+1
+2
+explain partitions select * from t1 where f1 < 3;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p1,p3	ALL	NULL	NULL	NULL	NULL	2	Using where
+select * from t1 where f1 is null;
+f1
+NULL
+explain partitions select * from t1 where f1 is null;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p2	system	NULL	NULL	NULL	NULL	1	
+drop table t1;
+create table t1 (f1 int) partition by list(f1 div 2)
+(
+partition p1 values in (1),
+partition p2 values in (NULL),
+partition p3 values in (2),
+partition p4 values in (3),
+partition p5 values in (4)
+);
+insert into t1 values (2),(4),(6),(8),(null);
+select * from t1 where f1 < 3;
+f1
+2
+explain partitions select * from t1 where f1 < 3;
+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
+select * from t1 where f1 is null;
+f1
+NULL
+explain partitions select * from t1 where f1 is null;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	p2	system	NULL	NULL	NULL	NULL	1	
+drop table t1;
+create table t1 (a int) partition by LIST(a) (
+partition pn values in (NULL),
+partition p0 values in (0),
+partition p1 values in (1),
+partition p2 values in (2)
+);
+insert into t1 values (NULL),(0),(1),(2);
+select * from t1 where a is null or a < 2;
+a
+NULL
+0
+1
+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	pn,p0,p1	ALL	NULL	NULL	NULL	NULL	3	Using where
+select * from t1 where a is null or a < 0 or a > 1;
+a
+NULL
+2
+explain partitions select * from t1 where a is null or a < 0 or a > 1;
+id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	pn,p2	ALL	NULL	NULL	NULL	NULL	2	Using where
+drop table t1;
 End of 5.1 tests

--- 1.30/mysql-test/t/partition.test	Tue Mar 21 06:52:56 2006
+++ 1.31/mysql-test/t/partition.test	Tue Mar 28 17:25:12 2006
@@ -849,4 +849,79 @@ CALL test.p1(12);
 CALL test.p1(13);
 drop table t1;
 
+#
+# Bug#18053 Partitions: crash if null
+# Bug#18070 Partitions: wrong result on WHERE ... IS NULL
+#
+create table t1 (f1 integer) partition by range(f1)
+(partition p1 values less than (0), partition p2 values less than (10));
+insert into t1 set f1 = null;
+select * from t1 where f1 is null;
+explain partitions select * from t1 where f1 is null;
+drop table t1;
+
+create table t1 (f1 integer) partition by list(f1)
+(partition p1 values in (1), partition p2 values in (null));
+insert into t1 set f1 = null;
+insert into t1 set f1 = 1;
+select * from t1 where f1 is null or f1 = 1;
+drop table t1;
+
+create table t1 (f1 smallint)
+partition by list (f1) (partition p0 values in (null));
+insert into t1 values (null);
+select * from t1 where f1 is null;
+drop table t1;
+
+create table t1 (f1 smallint)
+partition by range (f1) (partition p0 values less than (0));
+insert into t1 values (null);
+select * from t1 where f1 is null;
+drop table t1;
+
+create table t1 (f1 integer) partition by list(f1)
+(
+ partition p1 values in (1),
+ partition p2 values in (NULL),
+ partition p3 values in (2),
+ partition p4 values in (3),
+ partition p5 values in (4)
+);
+
+insert into t1 values (1),(2),(3),(4),(null);
+select * from t1 where f1 < 3;
+explain partitions select * from t1 where f1 < 3;
+select * from t1 where f1 is null;
+explain partitions select * from t1 where f1 is null;
+drop table t1;
+
+create table t1 (f1 int) partition by list(f1 div 2)
+(
+ partition p1 values in (1),
+ partition p2 values in (NULL),
+ partition p3 values in (2),
+ partition p4 values in (3),
+ partition p5 values in (4)
+);
+
+insert into t1 values (2),(4),(6),(8),(null);
+select * from t1 where f1 < 3;
+explain partitions select * from t1 where f1 < 3;
+select * from t1 where f1 is null;
+explain partitions select * from t1 where f1 is null;
+drop table t1;
+
+create table t1 (a int) partition by LIST(a) (
+  partition pn values in (NULL),
+  partition p0 values in (0),
+  partition p1 values in (1),
+  partition p2 values in (2)
+);
+insert into t1 values (NULL),(0),(1),(2);
+select * from t1 where a is null or a < 2;
+explain partitions select * from t1 where a is null or a < 2;
+select * from t1 where a is null or a < 0 or a > 1;
+explain partitions select * from t1 where a is null or a < 0 or a > 1;
+drop table t1;
+
 --echo End of 5.1 tests

--- 1.57/sql/sql_partition.cc	Sat Mar 25 03:21:33 2006
+++ 1.58/sql/sql_partition.cc	Tue Mar 28 17:25:13 2006
@@ -2868,9 +2868,6 @@ uint32 get_partition_id_range_for_endpoi
   /* Get the partitioning function value for the endpoint */
   longlong part_func_value= part_val_int(part_info->part_expr);
 
-  if (part_info->part_expr->null_value)
-    DBUG_RETURN(0);
-
   while (max_part_id > min_part_id)
   {
     loc_part_id= (max_part_id + min_part_id + 1) >> 1;
@@ -5745,6 +5742,19 @@ int get_part_iter_for_interval_via_mappi
   else
     DBUG_ASSERT(0);
 
+  if (field->real_maybe_null() && part_info->has_null_value)
+  {
+    if (*min_value)
+    {
+      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;
+    }
+  }
   /* Find minimum */
   if (flags & NO_MIN_RANGE)
     part_iter->part_nums.start= 0;
@@ -5956,7 +5966,14 @@ uint32 get_next_partition_id_range(PARTI
 uint32 get_next_partition_id_list(PARTITION_ITERATOR *part_iter)
 {
   if (part_iter->part_nums.start == part_iter->part_nums.end)
+  {
+    if (part_iter->has_null_value)
+    {
+      part_iter->has_null_value= FALSE;
+      return part_iter->part_info->has_null_part_id;
+    }
     return NOT_A_PARTITION_ID;
+  }
   else
     return part_iter->part_info->list_array[part_iter->
                                             part_nums.start++].partition_id;

--- 1.5/sql/sql_partition.h	Tue Feb 28 15:29:44 2006
+++ 1.6/sql/sql_partition.h	Tue Mar 28 17:25:13 2006
@@ -124,7 +124,7 @@ typedef uint32 (*partition_iter_func)(st
 typedef struct st_partition_iter
 {
   partition_iter_func get_next;
-  
+  bool has_null_value;
   struct st_part_num_range
   {
     uint32 start;