MySQL Bugs: #19307: Partitions: csv delete failure

Bug #19307	Partitions: csv delete failure
Submitted:	24 Apr 2006 19:49	Modified:	14 Jun 2006 1:15
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.10-beta-debug	OS:	Linux (SUSE 10.0)
Assigned to:	Mikael Ronström	CPU Architecture:	Any

Description:
After I've altered a table so that it contains a value that
is not allowed in any partition, I try to DELETE, and fail.

How to repeat:
mysql> create table tp (s1 int) engine=myisam partition by list (s1) (partition p1 values in (null));
Query OK, 0 rows affected (0.09 sec)

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

mysql> alter table tp engine=csv;
Query OK, 1 row affected (0.25 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> delete from tp;
ERROR 1505 (HY000): Table has no partition for value 0

Thank you for the bug reported. Verified as reported.

This is a "feature" of the CSV engine that it doesn't treat NULL's properly. So
has nothing to do with partitioning

If engine=csv and partitioning allows NULLs, then DELETE still doesn't work.
And UPDATE crashes. For example:

mysql> create table tq (s1 int) engine=csv partition by list (s1) (partition p1 values in (null));
Query OK, 0 rows affected (0.01 sec)

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

mysql> update tq set s1 = null;
ERROR 2013 (HY000): Lost connection to MySQL server during query

This is a verified bug. There is a test case which crashes.

I have now analysed the matter. The problem comes with NULL values. The CSV engine
accepts NULL values but silently converts them to zeroes. This doesn't play very well with
the partition engine that uses NULL to derive in which partition to put the value into. Then
CSV engine converts it into a 0, the 0 can possibly be located in a different partition which
makes no sense and even worse, 0 might be a value which is not even allowed in the table.
Also when updating to set a NULL value from a NULL value we even come across a case where
we calculate the old partition id and this is either wrong or even worse not existing (in which
case a crash occurs).

Since CSV isn't an important engine for partitioning the suggested solution is to disallow
CSV engines as part of partitioned tables. If they are to be allowed they must not allow
NULL values in partition functions.

In general there is a problem with having engines that silently convert values, this is
causing problems for the partition engine. There is also similar problem when to different
values are supposed to be equal when compared but when functions are applied they
deliver different results, this causes problem for partition pruning optimiser.

Patch will appear in 5.1.12
CSV engine is now disabled for partitioning

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 in 5.1.12 changelog and Partitioning Limitations/CSV Storage Engine sections of 5.1 Manual. Closed.