MySQL Bugs: #18752: Partitions: null becomes zero

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

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)

Thank you for the bug report.

This bug has been fixed by earlier patch in 5.1.10 or earlier version

I have re-opened this bug because the situation still occurs on my system.
Please verify that the test case does not fail.

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.

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.

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.