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

Bug #13443	Partitions: no error for unlisted values
Submitted:	23 Sep 2005 17:42	Modified:	4 Nov 2005 11:51
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.2-alpha-debug	OS:	Linux (SUSE 9.2)
Assigned to:	Jon Stephens	CPU Architecture:	Any

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).