bk commit into 5.1 tree (gluh:1.2087) BUG#15447

[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.2087 06/01/31 12:19:15 gluh@stripped +10 -0
  Fix for bug#15447 Partitions: NULL is treated as zero
    added processing of 'NULL' values

  sql/sql_yacc.yy
    1.447 06/01/31 12:17:42 gluh@stripped +36 -10
    Fix for bug#15447 Partitions: NULL is treated as zero
      added processing of 'NULL' values

  sql/sql_show.cc
    1.294 06/01/31 12:17:42 gluh@stripped +6 -0
    Fix for bug#15447 Partitions: NULL is treated as zero
      show 'NULL' value in partition description

  sql/sql_partition.cc
    1.29 06/01/31 12:17:42 gluh@stripped +44 -2
    Fix for bug#15447 Partitions: NULL is treated as zero
      added processing of 'NULL' values

  sql/handler.h
    1.184 06/01/31 12:17:42 gluh@stripped +7 -2
    Fix for bug#15447 Partitions: NULL is treated as zero
      added processing of 'NULL' values

  sql/ha_partition.cc
    1.27 06/01/31 12:17:42 gluh@stripped +5 -2
    Fix for bug#15447 Partitions: NULL is treated as zero
      send the message with 'NULL' argument in case of null expression 

  sql/ha_ndbcluster.cc
    1.246 06/01/31 12:17:42 gluh@stripped +7 -2
    Fix for bug#15447 Partitions: NULL is treated as zero
      send the message with 'NULL' argument in case of null expression

  mysql-test/t/partition.test
    1.12 06/01/31 12:17:42 gluh@stripped +58 -0
    Fix for bug#15447 Partitions: NULL is treated as zero
      test case

  mysql-test/t/ndb_partition_error.test
    1.4 06/01/31 12:17:42 gluh@stripped +13 -0
    Fix for bug#15447 Partitions: NULL is treated as zero
      test case

  mysql-test/r/partition.result
    1.11 06/01/31 12:17:42 gluh@stripped +49 -0
    Fix for bug#15447 Partitions: NULL is treated as zero
      test result

  mysql-test/r/ndb_partition_error.result
    1.8 06/01/31 12:17:42 gluh@stripped +9 -0
    Fix for bug#15447 Partitions: NULL is treated as zero
      test result

# 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.15447

--- 1.183/sql/handler.h	Sun Jan 29 12:53:09 2006
+++ 1.184/sql/handler.h	Tue Jan 31 12:17:42 2006
@@ -674,13 +674,14 @@ public:
   handlerton *engine_type;
   enum partition_state part_state;
   uint16 nodegroup_id;
+  bool has_null_value;
   
   partition_element()
   : part_max_rows(0), part_min_rows(0), partition_name(NULL),
     tablespace_name(NULL), range_value(0), part_comment(NULL),
     data_file_name(NULL), index_file_name(NULL),
     engine_type(NULL),part_state(PART_NORMAL),
-    nodegroup_id(UNDEF_NODEGROUP)
+    nodegroup_id(UNDEF_NODEGROUP), has_null_value(FALSE)
   {
     subpartitions.empty();
     list_val_list.empty();
@@ -950,6 +951,8 @@ public:
   bool linear_hash_ind;
   bool fixed;
   bool from_openfrm;
+  bool has_null_value;
+  uint has_null_part_id;
 
   partition_info()
   : get_partition_id(NULL), get_part_partition_id(NULL),
@@ -980,7 +983,9 @@ public:
     list_of_part_fields(FALSE), list_of_subpart_fields(FALSE),
     linear_hash_ind(FALSE),
     fixed(FALSE),
-    from_openfrm(FALSE)
+    from_openfrm(FALSE),
+    has_null_value(FALSE),
+    has_null_part_id(0)
   {
     all_fields_in_PF.clear_all();
     all_fields_in_PPF.clear_all();

--- 1.293/sql/sql_show.cc	Thu Jan 19 06:56:01 2006
+++ 1.294/sql/sql_show.cc	Tue Jan 31 12:17:42 2006
@@ -3715,6 +3715,12 @@ static int get_schema_partitions_record(
         uint no_items= part_elem->list_val_list.elements;
         tmp_str.length(0);
         tmp_res.length(0);
+        if (part_elem->has_null_value)
+        {
+          tmp_str.append("NULL");
+          if (no_items > 0)
+            tmp_str.append(",");
+        }
         while ((list_value= list_val_it++))
         {
           tmp_res.set(*list_value, cs);

--- 1.446/sql/sql_yacc.yy	Fri Jan 27 04:07:28 2006
+++ 1.447/sql/sql_yacc.yy	Tue Jan 31 12:17:42 2006
@@ -42,6 +42,12 @@
 #include <myisam.h>
 #include <myisammrg.h>
 
+typedef struct p_elem_val
+{ 
+  longlong value;
+  bool null_value;
+} part_elem_value;
+
 int yylex(void *yylval, void *yythd);
 
 const LEX_STRING null_lex_str={0,0};
@@ -110,6 +116,7 @@ inline Item *is_truth_value(Item *A, boo
   sp_name *spname;
   struct st_lex *lex;
   sp_head *sphead;
+  struct p_elem_val *p_elem_value;
 }
 
 %{
@@ -758,7 +765,7 @@ bool my_yyoverflow(short **a, YYSTYPE **
 %type <ulonglong_number>
 	ulonglong_num size_number
 
-%type <longlong_number>
+%type <p_elem_value>
         part_bit_expr
 
 %type <lock_type>
@@ -3743,7 +3750,7 @@ part_func_max:
 part_range_func:
         '(' part_bit_expr ')' 
         {
-          Lex->part_info->curr_part_elem->range_value= $2;
+          Lex->part_info->curr_part_elem->range_value= $2->value;
         }
         ;
 
@@ -3755,12 +3762,12 @@ part_list_func:
 part_list_item:
         part_bit_expr
         {
-          longlong *value_ptr;
-          if (!(value_ptr= (longlong*)sql_alloc(sizeof(longlong))) ||
-              ((*value_ptr= $1, FALSE) ||
-     Lex->part_info->curr_part_elem->list_val_list.push_back(value_ptr)))
+          part_elem_value *value_ptr= $1;
+          if (!value_ptr->null_value &&
+             Lex->part_info->curr_part_elem->
+              list_val_list.push_back((longlong*) &value_ptr->value))
           {
-            mem_alloc_error(sizeof(longlong));
+            mem_alloc_error(sizeof(part_elem_value));
             YYABORT;
           }
         }
@@ -3780,6 +3787,15 @@ part_bit_expr:
 
           context->table_list= 0;
           thd->where= "partition function";
+
+          part_elem_value *value_ptr= 
+            (part_elem_value*)sql_alloc(sizeof(part_elem_value));
+          if (!value_ptr)
+          {
+            mem_alloc_error(sizeof(part_elem_value));
+            YYABORT;
+          }
+
           if (part_expr->fix_fields(YYTHD, (Item**)0) ||
               ((context->table_list= save_list), FALSE) ||
               (!part_expr->const_item()) ||
@@ -3789,13 +3805,23 @@ part_bit_expr:
             YYABORT;
           }
           thd->where= save_where;
-          if (part_expr->result_type() != INT_RESULT)
+          value_ptr->value= part_expr->val_int();
+          if ((value_ptr->null_value= part_expr->null_value))
+          {
+            if (Lex->part_info->curr_part_elem->has_null_value)
+            {
+              my_error(ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR, MYF(0));
+              YYABORT;
+            }
+            Lex->part_info->curr_part_elem->has_null_value= TRUE;
+          }
+          else if (part_expr->result_type() != INT_RESULT &&
+                   !part_expr->null_value)
           {
             yyerror(ER(ER_INCONSISTENT_TYPE_OF_FUNCTIONS_ERROR));
             YYABORT;
           }
-          item_value= part_expr->val_int();
-          $$= item_value; 
+          $$= value_ptr; 
         }
         ;
 

--- 1.245/sql/ha_ndbcluster.cc	Fri Jan 27 23:26:03 2006
+++ 1.246/sql/ha_ndbcluster.cc	Tue Jan 31 12:17:42 2006
@@ -5909,8 +5909,13 @@ void ha_ndbcluster::print_error(int erro
   DBUG_PRINT("enter", ("error = %d", error));
 
   if (error == HA_ERR_NO_PARTITION_FOUND)
-    my_error(ER_NO_PARTITION_FOR_GIVEN_VALUE, MYF(0),
-             (int)m_part_info->part_expr->val_int());
+  {
+    if (m_part_info->part_expr->null_value)
+      my_error(ER_NO_PARTITION_FOR_GIVEN_VALUE, MYF(0), "NULL");
+    else
+      my_error(ER_NO_PARTITION_FOR_GIVEN_VALUE, MYF(0),
+               (int)m_part_info->part_expr->val_int());
+  }
   else
     handler::print_error(error, errflag);
   DBUG_VOID_RETURN;

--- 1.10/mysql-test/r/partition.result	Wed Jan 18 14:56:17 2006
+++ 1.11/mysql-test/r/partition.result	Tue Jan 31 12:17:42 2006
@@ -278,3 +278,52 @@ partition p1 values in (14)
 insert into t1 values (10,1);
 ERROR HY000: Table has no partition for value 11
 drop table t1;
+create table t1 (a int,b int,c int,key(a,b))
+partition by range (a)
+partitions 3
+(partition x1 values less than (0) tablespace ts1,
+partition x2 values less than (10) tablespace ts2,
+partition x3 values less than maxvalue tablespace ts3);
+insert into t1 values (NULL, 1, 1);
+insert into t1 values (0, 1, 1);
+insert into t1 values (12, 1, 1);
+select partition_name, partition_description, table_rows
+from information_schema.partitions where table_schema ='test';
+partition_name	partition_description	table_rows
+x1	0	1
+x2	10	1
+x3	MAXVALUE	1
+drop table t1;
+create table t1 (a int,b int, c int)
+partition by list(a)
+partitions 2
+(partition x123 values in (11,12),
+partition x234 values in (1 ,NULL, NULL));
+ERROR HY000: Multiple definition of same constant in list partitioning
+create table t1 (a int,b int, c int)
+partition by list(a)
+partitions 2
+(partition x123 values in (11, NULL),
+partition x234 values in (1 ,NULL));
+ERROR HY000: Multiple definition of same constant in list partitioning
+create table t1 (a int,b int, c int)
+partition by list(a)
+partitions 2
+(partition x123 values in (11, 12),
+partition x234 values in (5, 1));
+insert into t1 values (NULL,1,1);
+ERROR HY000: Table has no partition for value NULL
+drop table t1;
+create table t1 (a int,b int, c int)
+partition by list(a)
+partitions 2
+(partition x123 values in (11, 12),
+partition x234 values in (NULL, 1));
+insert into t1 values (11,1,6);
+insert into t1 values (NULL,1,1);
+select partition_name, partition_description, table_rows
+from information_schema.partitions where table_schema ='test';
+partition_name	partition_description	table_rows
+x123	11,12	1
+x234	NULL,1	1
+drop table t1;

--- 1.11/mysql-test/t/partition.test	Wed Jan 18 15:09:00 2006
+++ 1.12/mysql-test/t/partition.test	Tue Jan 31 12:17:42 2006
@@ -353,3 +353,61 @@ insert into t1 values (10,1);
 
 drop table t1;
 
+
+#
+# Bug #15447  Partitions: NULL is treated as zero
+#
+
+# NULL for RANGE partition
+create table t1 (a int,b int,c int,key(a,b))
+partition by range (a)
+partitions 3
+(partition x1 values less than (0) tablespace ts1,
+ partition x2 values less than (10) tablespace ts2,
+ partition x3 values less than maxvalue tablespace ts3);
+
+insert into t1 values (NULL, 1, 1);
+insert into t1 values (0, 1, 1);
+insert into t1 values (12, 1, 1);
+
+select partition_name, partition_description, table_rows
+from information_schema.partitions where table_schema ='test';
+drop table t1;
+
+# NULL for LIST partition
+--error 1473
+create table t1 (a int,b int, c int)
+partition by list(a)
+partitions 2
+(partition x123 values in (11,12),
+ partition x234 values in (1 ,NULL, NULL));
+
+--error 1473
+create table t1 (a int,b int, c int)
+partition by list(a)
+partitions 2
+(partition x123 values in (11, NULL),
+ partition x234 values in (1 ,NULL));
+
+create table t1 (a int,b int, c int)
+partition by list(a)
+partitions 2
+(partition x123 values in (11, 12),
+ partition x234 values in (5, 1));
+--error 1504
+insert into t1 values (NULL,1,1);
+drop table t1;
+
+create table t1 (a int,b int, c int)
+partition by list(a)
+partitions 2
+(partition x123 values in (11, 12),
+ partition x234 values in (NULL, 1));
+ 
+insert into t1 values (11,1,6);
+insert into t1 values (NULL,1,1);
+    
+select partition_name, partition_description, table_rows
+from information_schema.partitions where table_schema ='test';
+drop table t1;
+    
\ No newline at end of file

--- 1.26/sql/ha_partition.cc	Sun Jan 29 04:22:28 2006
+++ 1.27/sql/ha_partition.cc	Tue Jan 31 12:17:42 2006
@@ -5092,8 +5092,11 @@ void ha_partition::print_error(int error
   if (error == HA_ERR_NO_PARTITION_FOUND)
   {
     char buf[100];
-    my_error(ER_NO_PARTITION_FOR_GIVEN_VALUE, MYF(0),
-             llstr(m_part_info->part_expr->val_int(), buf));
+    if (m_part_info->part_expr->null_value)
+      my_error(ER_NO_PARTITION_FOR_GIVEN_VALUE, MYF(0), "NULL");
+    else
+      my_error(ER_NO_PARTITION_FOR_GIVEN_VALUE, MYF(0),
+               llstr(m_part_info->part_expr->val_int(), buf));
   }
   else
     m_file[0]->print_error(error, errflag);

--- 1.28/sql/sql_partition.cc	Sun Jan 29 04:22:28 2006
+++ 1.29/sql/sql_partition.cc	Tue Jan 31 12:17:42 2006
@@ -585,6 +585,7 @@ static bool check_list_constants(partiti
   bool result= TRUE;
   longlong curr_value, prev_value;
   partition_element* part_def;
+  bool found_null= FALSE;
   List_iterator<partition_element> list_func_it(part_info->partitions);
   DBUG_ENTER("check_list_constants");
 
@@ -610,6 +611,17 @@ static bool check_list_constants(partiti
   do
   {
     part_def= list_func_it++;
+    if (part_def->has_null_value)
+    {
+      if (found_null)
+      {
+        my_error(ER_MULTIPLE_DEF_CONST_IN_LIST_PART_ERROR, MYF(0));
+        goto end;
+      }
+      part_info->has_null_value= TRUE;
+      part_info->has_null_part_id= i;
+      found_null= TRUE;
+    }
     List_iterator<longlong> list_val_it1(part_def->list_val_list);
     while (list_val_it1++)
       no_list_values++;
@@ -2326,6 +2338,16 @@ static int add_partition_values(File fpt
     err+= add_string(fptr, "VALUES IN ");
     uint no_items= p_elem->list_val_list.elements;
     err+= add_begin_parenthesis(fptr);
+    if (p_elem->has_null_value)
+    {
+      err+= add_string(fptr, "NULL");
+      if (no_items == 0)
+      {
+        err+= add_end_parenthesis(fptr);
+        goto end;
+      }
+      err+= add_comma(fptr);
+    }
     i= 0;
     do
     {
@@ -2336,6 +2358,7 @@ static int add_partition_values(File fpt
     } while (++i < no_items);
     err+= add_end_parenthesis(fptr);
   }
+end:
   return err + add_space(fptr);
 }
 
@@ -2891,6 +2914,15 @@ int get_partition_id_list(partition_info
   longlong part_func_value= part_info->part_expr->val_int();
   DBUG_ENTER("get_partition_id_list");
 
+  if (part_info->part_expr->null_value)
+  {
+    if (part_info->has_null_value)
+    {
+      *part_id= part_info->has_null_part_id;
+      DBUG_RETURN(0);
+    }
+    goto notfound;
+  }
   *func_value= part_func_value;
   while (max_list_index >= min_list_index)
   {
@@ -3001,6 +3033,11 @@ int get_partition_id_range(partition_inf
   longlong part_func_value= part_info->part_expr->val_int();
   DBUG_ENTER("get_partition_id_int_range");
 
+  if (part_info->part_expr->null_value)
+  {
+    *part_id= 0;
+    DBUG_RETURN(0);
+  }
   while (max_part_id > min_part_id)
   {
     loc_part_id= (max_part_id + min_part_id + 1) >> 1;
@@ -3074,6 +3111,11 @@ uint32 get_partition_id_range_for_endpoi
   uint min_part_id= 0, max_part_id= max_partition, loc_part_id;
   /* Get the partitioning function value for the endpoint */
   longlong part_func_value= part_info->part_expr->val_int();
+
+  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;
@@ -3788,10 +3830,10 @@ void get_partition_set(const TABLE *tabl
    | Forminfo     288 bytes      |
    -------------------------------
    | Screen buffer, to make      |
-   | field names readable        |
+   | field names readable        |
    -------------------------------
    | Packed field info           |
-   | 17 + 1 + strlen(field_name) |
+   | 17 + 1 + strlen(field_name) |
    | + 1 end of file character   |
    -------------------------------
    | Partition info              |

--- 1.7/mysql-test/r/ndb_partition_error.result	Thu Nov 24 00:44:53 2005
+++ 1.8/mysql-test/r/ndb_partition_error.result	Tue Jan 31 12:17:42 2006
@@ -28,3 +28,12 @@ partitions 3
 partition x2 values less than (10),
 partition x3 values less than (20));
 drop table t1;
+create table t1 (a int,b int, c int)
+engine = ndb
+partition by list(a)
+partitions 2
+(partition x123 values in (11, 12),
+partition x234 values in (5, 1));
+insert into t1 values (NULL,1,1);
+ERROR HY000: Table has no partition for value NULL
+drop table t1;

--- 1.3/mysql-test/t/ndb_partition_error.test	Sun Nov  6 03:20:22 2005
+++ 1.4/mysql-test/t/ndb_partition_error.test	Tue Jan 31 12:17:42 2006
@@ -44,3 +44,16 @@ partitions 3
  partition x3 values less than (20));
 
 drop table t1;
+
+#
+# NULL for LIST partition
+#
+create table t1 (a int,b int, c int)
+engine = ndb
+partition by list(a)
+partitions 2
+(partition x123 values in (11, 12),
+ partition x234 values in (5, 1));
+--error 1504
+insert into t1 values (NULL,1,1);
+drop table t1;