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: Sergey Glukhov

[2 Dec 2005 20:58] Peter Gulutzan
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)
[5 Dec 2005 12:20] Aleksey Kishkin
verified against latest 5.1 from 5.1-new bk tree (5.1.4-alfha-debug)
[16 Jan 2006 17:24] Matthias Leich
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
[31 Jan 2006 8:13] Bugs System
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
[7 Mar 2006 11:21] Bugs System
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
[7 Mar 2006 12:32] Sergey Glukhov
Fixed in 5.1.8
[10 Mar 2006 2:10] Jon Stephens
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.