MySQL Bugs: #39548: cannot partition on an ENUM numeric expression

Bug #39548	cannot partition on an ENUM numeric expression
Submitted:	19 Sep 2008 20:41	Modified:	15 Dec 2008 11:19
Reporter:	Edmund Mierzwa	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.1.28, 5.1, 6.0 bzr	OS:	Any (Linux, windows XP sp2)
Assigned to:	Mattias Jonsson	CPU Architecture:	Any
Tags:	partitioning enum

Description:
When trying to partition on an ENUM() column I get an error message:
"The PARTITION function returns the wrong type"  #1491

The documentation suggests an expression based on a column value and returning an integer value is a valid partitioning numeric. The standard way to represent an ENUM numerically is to add zero to the enum column value.

21.2.2. LIST Partitioning
This is done by using PARTITION BY LIST(expr) where expr is a column value or an expression based on a column value and returning an integer value, and then defining each partition by means of a VALUES IN (value_list), where value_list is a comma-separated list of integers. 

How to repeat:
DROP TABLE IF EXISTS `events_zone_all`;
CREATE TABLE  `events_zone_all` (
  `event_pos` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event_no` smallint(5) unsigned NOT NULL,
  `zone_id` enum('A','B','C','D') NOT NULL,
  `event_epoc` int(10) unsigned NOT NULL,
  `event_desc` mediumtext NOT NULL,
  PRIMARY KEY (`event_pos`,`zone_id`),
  KEY `by_event` (`event_no`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY LIST( 0+zone_id ) (
    PARTITION pa VALUES IN (1),
    PARTITION pb VALUES IN (2),
    PARTITION pc VALUES IN (3),
    PARTITION pd VALUES IN (4));

Suggested fix:
Allow the partition function to utilize the numeric index of an ENUM() column.

Thank you for the report.

Verified as described.

Yes, strange that the partitioning is not supported by data type ,actually representing a integer .

For what it's worth, you can use KEY partitioning on an ENUM column.

That's correct, Bill, and it is supported. Note that an ENUM can be used as a partitioning key if and only if the table uses KEY or LINEAR KEY partitioning (see BUG#75094). 

Note that pruning of queries on tables partitioned by [LINEAR] KEY works with integer columns only, thus not with ENUM columns because it's not an integer type.