MySQL Bugs: #15447: Partitions: NULL is treated as zero

Bug #15447	Partitions: NULL is treated as zero
Submitted:	2 Dec 2005 20:58	Modified:	10 Mar 2006 2:10
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.4-alpha-debug	OS:	Linux (linux)
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
MySQL should not treat NULL as zero, for partitioning.
In sorting,, MySQL treats NULL as less than other values.
Therefore, if there are RANGE partitions, NULLs would
fit in the first partition (because they're less than any
value that can be listed in the LESS THAN clause).
If there are LIST partitions, "VALUES IN (NULL)" should
be legal.
If there are HASH or KEY partitions, this bug report
doesn't apply.

How to repeat:
mysql> create table tpx (s1 int) partition by list (s1) (partition p1 values in (0));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tpx values (null);
Query OK, 1 row affected (0.01 sec)

verified against latest 5.1 from 5.1-new bk tree (5.1.4-alfha-debug)

I have another example, which might be of interest:
CREATE TABLE t1 ( f1 INTEGER, f2 char(20))
PARTITION BY LIST(MOD(f1,2))
( PARTITION part1 VALUES IN (CAST(NULL AS SIGNED INTEGER)),
PARTITION part2 VALUES IN (0),
PARTITION part3 VALUES IN (1));
ERROR HY000: Multiple definition of same constant in list partitioning

Simple experiments with other combinations of values within the 
VALUES IN clauses clear reveal, that 
  "IN (CAST(NULL AS SIGNED INTEGER))"
is treated like 
  "IN (0)"

BTW: In the moment (16.01.2006) VALUES IN (NULL)  fails with
         ERROR 42000: VALUES value must be of same type as partition function near

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/1920

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/3540

Fixed in 5.1.8

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Noted incompatible change in 5.1.8 changelog.

Rewrote 5.1 Manual section "How MySQL Partitioning Handles NULL Values" to reflect new behaviour.

Closed bug report.