Bug #18752 Partitions: null becomes zero
Submitted: 3 Apr 2006 17:07 Modified: 16 Apr 2006 14:50
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.9-beta-debug OS:Linux (SUSE 10.0)
Assigned to: Mikael Ronström CPU Architecture:Any

[3 Apr 2006 17:07] Peter Gulutzan
Description:
I can create a range partition with LESS THAN (NULL).
But SHOW CREATE TABLE says that MySQL has interpreted
the definition as LESS THAN (0).

How to repeat:
mysql> create table tp77 (s1 int) partition by range (s1) (partition p2 values less than (null));
Query OK, 0 rows affected (0.01 sec)

mysql> show create table tp77;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                         |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tp77  | CREATE TABLE `tp77` (
  `s1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY RANGE (s1) (PARTITION p2 VALUES LESS THAN (0) ENGINE = MyISAM) |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[4 Apr 2006 2:13] MySQL Verification Team
Thank you for the bug report.
[10 Apr 2006 8:37] Mikael Ronström
This bug has been fixed by earlier patch in 5.1.10 or earlier version
[10 Apr 2006 18:22] Peter Gulutzan
I have re-opened this bug because the situation still occurs on my system.
Please verify that the test case does not fail.
[10 Apr 2006 19:15] Mikael Ronström
It's correct that this bug still exists with RANGE partitioning, it is solved with LIST partitioning.
I'll fix it by reporting error for RANGE partitioning since I consider it a waste to define a
partition that cannot ever have a single record inserted into it.
[15 Apr 2006 14:45] Mikael Ronström
This patch will appear in 5.1.10
The implications of this fix is that for:
1) LIST Partitions
VALUES IN (NULL) is an acceptable clause and will be kept track of also in partition pruning

2) RANGE Partitions
VALUES LESS THAN (NULL) is not an acceptable clause (reason is that it NULL is smaller than all
other values so there exist no value LESS THAN (NULL). Thus this partition will always remain
empty.
[16 Apr 2006 14:50] 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:

Documented bugfix in 5.1.10 changelog. Closed.