MySQL Bugs: #60288: ENUM should be allowed to partition on

Bug #60288	ENUM should be allowed to partition on
Submitted:	1 Mar 2011 9:54	Modified:	1 Mar 2011 10:25
Reporter:	- -	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.5.8	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	columns, enum, list, partitioning

Description:
ENUM is easily mapped to integer (it is stored internally using integer indices) and to string (using its string representation shown to client), so it should be allowed to partition on. 

How to repeat:
mysql> CREATE TABLE t(f ENUM('N', 'Y')) 
     > PARTITION BY LIST(f) (PARTITION pd VALUES IN('N'));
ERROR 1697 (HY000): VALUES value for partition 'pd' must have type INT

mysql> CREATE TABLE t(f ENUM('N', 'Y'))
     > PARTITION BY LIST COLUMNS(f) (PARTITION pd VALUES IN('N'));
ERROR 1659 (HY000): Field 'f' is of a not allowed type for this type of partitioning

Suggested fix:
Allow ENUM to be used for partitioning.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

http://dev.mysql.com/doc/refman/5.5/en/create-table.html

"...Columns using BLOB, TEXT, SET, ENUM, BIT, or spatial data types are not permitted; columns that use floating-point number types are also not permitted. You also may not use functions or arithmetic expressions in the COLUMNS clause. "

I've read manual, but didn't understand the cause ENUM cannot be used to partition on. 

BLOB, TEXT, BIT, Spatial cannot be mapped to integer. SET is rather useless to partition on, but can be easily mapped to integer. ENUM is not useless to partition on and can be easily mapped to integer, as it is stored as integer. 

What is the problem with ENUM and partitioning on it?

You can use ENUM as a partitioning key if the table is partitioned by KEY or LINEAR KEY. Note that there's no pruning on ENUMs. See BUG#75094.

I'm aware of no plans to extend ENUM support any further at this time.