MySQL Bugs: #27084: partitioning by list seems failing when using case

Bug #27084	partitioning by list seems failing when using case
Submitted:	13 Mar 2007 14:04	Modified:	27 Jun 2007 22:40
Reporter:	Cyril SCETBON	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.16-beta	OS:	Any (i686 GNU/Linux kernel 2.6.17)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
I cannot use partitioning as long as the case test fails

How to repeat:
CREATE TABLE `test` (
  `a` varchar(1)
) ENGINE=MyISAM
PARTITION BY LIST (CASE a WHEN 'a' THEN 1
WHEN 'b' THEN 2
WHEN 'c' THEN 3
END)  (
PARTITION a VALUES IN (1), 
PARTITION b VALUES IN (2),
PARTITION c VALUES IN (3)
);
insert into test(a) values('c');

/* NO PROBLEM */

drop table test;
CREATE TABLE `test` (
  `a` varchar(10)
) ENGINE=MyISAM
PARTITION BY LIST (CASE a WHEN 'a' THEN 1
WHEN 'b' THEN 2
WHEN 'c' THEN 3
END)  (
PARTITION a VALUES IN (1), 
PARTITION b VALUES IN (2),
PARTITION c VALUES IN (3)
);
insert into partition_test2(a) values('c');

/* ERROR 1514*/

Actually I want to use subpartition and the primary partition has to be done by LIST or RANGE :-( So it must succeed

Thank you for the report.

Verified with different error:
$mysql51 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.17-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

CREATE TABLE `t1` (
    ->   `a` varchar(1)
    -> ) ENGINE=MyISAM
    -> PARTITION BY LIST (CASE a WHEN 'a' THEN 1
    -> WHEN 'b' THEN 2
    -> WHEN 'c' THEN 3
    -> END)  (
    -> PARTITION a VALUES IN (1), 
    -> PARTITION b VALUES IN (2),
    -> PARTITION c VALUES IN (3)
    -> );
ERROR 2013 (HY000): Lost connection to MySQL server during query

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22997

ChangeSet@1.2501, 2007-03-27 16:15:38+05:00, holyfoot@mysql.com +7 -0
  Bug #27084 partitioning by list seems failing when using case
  
  creation of the partitioned table could fail as we created Item-s for
  it's list function in thd->mem_root, and then do Item->fix_fields
  in the context of other table->mem_root (so that memory alloced
  there was alloced in this table->mem_root). As we freed the
  table->mem_root before we do thd->free_items, our Item-s had
  pointers to the freed memory, that caused the crash

I've tried your patch but I encountered the same error :

mysql> CREATE TABLE `test` (   `a` varchar(10) ) ENGINE=MyISAM PARTITION BY LIST (CASE a WHEN 'a' THEN 1 WHEN 'b' THEN 2 WHEN 'c' THEN 3 END)  ( PARTITION a VALUES IN (1),  PARTITION b VALUES IN (2), PARTITION c VALUES IN (3) );
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test(a) values('c');                                                                                      ERROR 1514 (HY000): Table has no partition for value NULL

The method of using CASE here will not be allowed after I push the fix for BUG 18198.
Use ASCII('a') instead.

what's a pity :-(

Pushed into 5.1.21-beta

CASE() is not allowed in partitioning expressions, and this fact has been stated quite clearly in the documentation for quite some time - see http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-functions-disallowed.html

So what does this patch do? If it makes the use of CASE() possible, then it needs to be reverted. If it causes a CREATE TABLE or ALTER TABLE statement to fail gracefully with an error rather than crashing, then that's an acceptable fix.

See also Bug #18198 and Bug #29308

The test case found a generic bug, there was nothing in the bug fix related to CASE
statements. This is why the bug fix was approved. So how you document this is
another matter :)

'Generic bug' covers considerable ground.

What does the fix for this bug actually change?

It changes the memroot before calling fix_fields which I presume doesn't make
you any happier. So just mention that an error in some partition functions was
fixed (CASE is an obvious one but there could be more ones).

Fine, thanks for the info.

Documented bugfix in 5.1.21 changelog.

The submitter of this bug should note carefully that the fact that this CREATE TABLE statement currently succeeds (with latest 5.1.21-bk) is itself a bug, that it will fail with an error (as it should) once the fix for Bug #18198 is pushed, and that the failure will be intentional.

Since 5.1 it seems you can no longer a bitwise function in a list partition expression

Example:
DROP TABLE IF EXISTS test;
CREATE TABLE test (
    id INT NOT NULL
)
PARTITION BY LIST (id << 1) (
    PARTITION p0 VALUES IN (0),
    PARTITION p1 VALUES IN (1)
);

Could this have been caused by the fix for this bug?

From http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html:

"Beginning with MySQL 5.1.12, the bit operators |, &, ^, <<, >>, and ~ are not permitted in partitioning expressions."