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

[24 Apr 2006 19:49] Peter Gulutzan
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
[24 Apr 2006 20:28] Miguel Solorzano
Thank you for the bug reported. Verified as reported.
[29 May 2006 15:05] Mikael Ronström
This is a "feature" of the CSV engine that it doesn't treat NULL's properly. So
has nothing to do with partitioning
[29 May 2006 20:27] Peter Gulutzan
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
[30 May 2006 12:01] Peter Gulutzan
This is a verified bug. There is a test case which crashes.
[13 Jun 2006 10:39] Mikael Ronström
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.
[13 Jun 2006 21:29] Mikael Ronström
Patch will appear in 5.1.12
CSV engine is now disabled for partitioning
[14 Jun 2006 1:15] 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 in 5.1.12 changelog and Partitioning Limitations/CSV Storage Engine sections of 5.1 Manual. Closed.