MySQL Bugs: #75085: Partition pruning on key partioning with ENUM

Bug #75085	Partition pruning on key partioning with ENUM
Submitted:	3 Dec 2014 9:19	Modified:	8 Aug 2022 8:24
Reporter:	Daniël van Eeden (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S4 (Feature request)
Version:	5.6.16	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	enum, partitioning

Description:
When partitioning on KEY(enum_col) no pruning is done (e.g. all partitions are scanned).

As key seems to be the only option for partitioning on enum (Bug #60288 and Bug #39548) for some unknown reason handing key partitioning a bit better would be very welcome.

How to repeat:
mysql> create table t1 (id char(40) not null, revision char(10) not null, `type` enum('one','two','three') not null, val varchar(100), primary key(id,revision,type)) partition by key(`type`) partitions 4;
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2 (id char(40) not null, revision char(10) not null, `type` enum('one','two','three') not null, val varchar(100), primary key(id,revision,type)) partition by key(`revision`) partitions 4;
Query OK, 0 rows affected (0.03 sec)

mysql> create table t3 (id char(40) not null, revision char(10) not null, `type` enum('one','two','three') not null, val varchar(100), primary key(id,revision,type)) partition by key(`id`) partitions 4;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into t1 values('x','y','one','xyz');                                                                                                
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values('x','y','one','xyz');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t3 values('x','y','one','xyz');
Query OK, 1 row affected (0.00 sec)

mysql> explain partitions select val from t1 where id='x' and revision='y' and type='one';
+----+-------------+-------+-------------+-------+---------------+---------+---------+-------------------+------+-------+
| id | select_type | table | partitions  | type  | possible_keys | key     | key_len | ref               | rows | Extra |
+----+-------------+-------+-------------+-------+---------------+---------+---------+-------------------+------+-------+
|  1 | SIMPLE      | t1    | p0,p1,p2,p3 | const | PRIMARY       | PRIMARY | 51      | const,const,const |    1 | NULL  |
+----+-------------+-------+-------------+-------+---------------+---------+---------+-------------------+------+-------+
1 row in set (0.00 sec)

mysql> explain partitions select val from t2 where id='x' and revision='y' and type='one';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------------+------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref               | rows | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------------+------+-------+
|  1 | SIMPLE      | t2    | p0         | const | PRIMARY       | PRIMARY | 51      | const,const,const |    1 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------------+------+-------+
1 row in set (0.01 sec)

mysql> explain partitions select val from t3 where id='x' and revision='y' and type='one';
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------------+------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref               | rows | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------------+------+-------+
|  1 | SIMPLE      | t3    | p1         | const | PRIMARY       | PRIMARY | 51      | const,const,const |    1 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------------------+------+-------+
1 row in set (0.00 sec)

Suggested fix:
1. Make sure pruning works for key partitioning on enum
2. Update documentation to explain partitioning options and limitations for enum.

There is no information about partition selection in both in json explain and the optimizer trace other than the names of the used partitions in the json explain.

It looks like fields_ok_for_partition_index() in sql/opt_range.cc blacklists ENUMs for some unknown reason.

Patching fields_ok_for_partition_index helps:

mysql> select * from t1;
+----+----------+-------+------+
| id | revision | type  | val  |
+----+----------+-------+------+
| x  | y        | one   | xyz  |
| x  | y        | three | xyz  |
| x  | y        | two   | xyz  |
+----+----------+-------+------+
3 rows in set (0.00 sec)

mysql> explain partitions select * from t1 where type='one';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | p0         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from t1 where type='two';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | p3         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> explain partitions select * from t1 where type='three';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | t1    | p2         | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` char(40) NOT NULL,
  `revision` char(10) NOT NULL,
  `type` enum('one','two','three') NOT NULL,
  `val` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`,`revision`,`type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY KEY (`type`)
PARTITIONS 4 */
1 row in set (0.00 sec)

mysql>

Patch to allow partition pruning with ENUM

Attachment: 0001-Allow-partition-pruning-for-ENUM.patch (text/x-diff), 749 bytes.

This is a fully valid feature request, and hence, it is verified.

From https://github.com/mysql/mysql-server/blob/5.6/sql/opt_range.cc#L4048

"It is not known if we could process ENUM fields, so they are disabled to be
on the safe side."

So it seems nobody knows exactly why ENUM fields are disabled for partition pruning.

4 years passed. Is there a chance for this patch to be approved?

Updated link:

https://github.com/mysql/mysql-server/blob/fbdaa4def30d269bc4de5b85de61de34b11c0afc/sql/ra...