MySQL Bugs: #13443: Partitions: no error for unlisted values

Bug #13443	Partitions: no error for unlisted values
Submitted:	23 Sep 2005 19:42	Modified:	4 Nov 2005 12:51
Reporter:	Peter Gulutzan
Status:	Closed
Category:	Server: Partition	Severity:	S3 (Non-critical)
Version:	5.1.2-alpha-debug	OS:	Linux (SUSE 9.2)
Assigned to:	Jon Stephens	Target Version:

Description:
If I define a table with a range partition, then I get
an error when I try to insert values that are outside
of any range. If I define a table with a list partition,
then I get no error when I try to insert values that
are outside of any list. I should get an error for
both situations.

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

mysql> insert into tq1 values (null);
ERROR 1030 (HY000): Got error 1 from storage engine
mysql> create table tq2 (s1 int) partition by list(s1) (partition x1 values in (0));
Query OK, 0 rows affected (0.01 sec)

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

The problem here is that a function with NULL as input will actually always return 0
and this is treated as a correct value. The option would be to not allow NULLable fields
in the partition function but it was decided to support NULL and then if any field is
NULL the
value of the partition function is 0.

This is not a bug

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Updated docs to make it clear that this is expected behaviour (and why).