bk commit into 5.1 tree (gluh:1.2200) BUG#18070

[gluh]

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.2200 06/03/23 14:49:24 gluh@stripped +3 -0
  Bug#18053 Partitions: crash if null
  Bug#18070 Partitions: wrong result on WHERE ... IS NULL
   removed unnecessary code
   added handling of 'WHERE ... IS NULL' condition

  sql/sql_partition.cc
    1.56 06/03/23 14:49:18 gluh@stripped +6 -3
    Bug#18053 Partitions: crash if null
    Bug#18070 Partitions: wrong result on WHERE ... IS NULL
     removed unnecessary code
     added handling of 'WHERE ... IS NULL' condition

  mysql-test/t/partition.test
    1.31 06/03/23 14:49:18 gluh@stripped +62 -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/23 14:49:18 gluh@stripped +78 -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/Bugs/5.1.18070

--- 1.28/mysql-test/r/partition.result	Tue Mar 21 06:52:56 2006
+++ 1.29/mysql-test/r/partition.result	Thu Mar 23 14:49:18 2006
@@ -718,4 +718,82 @@ 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;
 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	Thu Mar 23 14:49:18 2006
@@ -849,4 +849,66 @@ 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;
+
 --echo End of 5.1 tests

--- 1.55/sql/sql_partition.cc	Tue Mar 21 08:55:06 2006
+++ 1.56/sql/sql_partition.cc	Thu Mar 23 14:49:18 2006
@@ -2863,9 +2863,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;
@@ -5722,6 +5719,12 @@ int get_part_iter_for_interval_via_mappi
   else
     DBUG_ASSERT(0);
 
+  if (field->real_maybe_null() && !(flags & (NO_MIN_RANGE | NO_MAX_RANGE))
&&
+      *min_value && *max_value && part_info->has_null_value)
+  {
+    init_single_partition_iterator(part_info->has_null_part_id, part_iter);
+    return 1;
+  }
   /* Find minimum */
   if (flags & NO_MIN_RANGE)
     part_iter->part_nums.start= 0;