MySQL Bugs: #92661: SELECT on key partitioned enum reading all partitions instead of 1

Bug #92661	SELECT on key partitioned enum reading all partitions instead of 1
Submitted:	3 Oct 2018 21:42	Modified:	4 Oct 2018 22:05
Reporter:	Frederic Steinfels	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S3 (Non-critical)
Version:	5.7.23	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	enum, key, partition, SELECT

Description:
When using partitioning on an enum (called outdated in this sample) and verifying that the rows are correctly stored in the corresponding partition by the outdated column, the select statement should only read from the corresponding partition if outdated is explicitly given in the where part of the statement. Unfortunately that is not the case, all partitions are read.

How to repeat:
CREATE TABLE `product` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `fullname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `outdated` enum('0','1') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`,`outdated`),
  KEY `fullname` (`fullname`),
  KEY `outdated` (`outdated`)
) ENGINE=InnoDB AUTO_INCREMENT=1568654 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
/*!50100 PARTITION BY KEY (outdated)
PARTITIONS 2 */

alter table product partition by key(outdated) partitions 2;

now inserting a few rows, some with outdated='0' and some with outdated='1'

select table_name, partition_name, table_rows from information_schema.partitions where table_name = 'product';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| product    | p0             |     952593 |
| product    | p1             |          1 |
+------------+----------------+------------+

some rows are in p0, some in p1, as expected

however here comes the crux:

explain select id from product where outdated='0';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
|  1 | SIMPLE      | product | p0,p1      | ref  | outdated      | outdated | 1       | const | 476296 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

the select statement reads from p0 and p1 instead of p0. vice versa,

explain select id from product where outdated='1';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | product | p0,p1      | ref  | outdated      | outdated | 1       | const |    1 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Is also reading from both partitions.

Hi,

I initially wanted to dismiss this as not a bug since there are some limitations around ENUM and partitioning, but we do list explicitly on limitations page that enum can be use for [linear]key partitioning so this definitely is a bug.

If you go with tinyint it works ok

mysql [localhost] {msandbox} (test) > select table_name, partition_name, table_rows from information_schema.partitions where table_name = 'product';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| product    | p0             |       2560 |
| product    | p1             |       1000 |
+------------+----------------+------------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > analyze table product;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| test.product | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (0.10 sec)

mysql [localhost] {msandbox} (test) > select table_name, partition_name, table_rows from information_schema.partitions where table_name = 'product';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| product    | p0             |       2560 |
| product    | p1             |       1000 |
+------------+----------------+------------+
2 rows in set (0.01 sec)

mysql [localhost] {msandbox} (test) > explain select id from product where outdated='0';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | product | p0,p1      | ref  | outdated      | outdated | 1       | const | 2560 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

mysql [localhost] {msandbox} (test) > explain select id from product where outdated='1';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | product | p0,p1      | ref  | outdated      | outdated | 1       | const | 1000 |   100.00 | Using index |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > CREATE TABLE `productx` (
    ->   `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
    ->   `fullname` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
    ->   `outdated` tinyint NOT NULL DEFAULT 0,
    ->   PRIMARY KEY (`id`,`outdated`),
    ->   KEY `fullname` (`fullname`),
    ->   KEY `outdated` (`outdated`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=1568654 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    -> PARTITION BY KEY (outdated)
    -> PARTITIONS 2;
Query OK, 0 rows affected (0.65 sec)

mysql [localhost] {msandbox} (test) > insert into productx select null, 'jedan', 1 from product limit 1000;                       Query OK, 1000 rows affected (0.16 sec)
Records: 1000  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > insert into productx select null, 'nula', 0 from product limit 2000;
Query OK, 2000 rows affected (0.33 sec)
Records: 2000  Duplicates: 0  Warnings: 0

mysql [localhost] {msandbox} (test) > select table_name, partition_name, table_rows from information_schema.partitions where table_name = 'productx';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| productx   | p0             |       1000 |
| productx   | p1             |       2000 |
+------------+----------------+------------+
2 rows in set (0.00 sec)

mysql [localhost] {msandbox} (test) > explain select id from productx where outdated=0;
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | productx | p1         | ref  | outdated      | outdated | 1       | const | 2000 |   100.00 | Using index |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost] {msandbox} (test) > explain select id from productx where outdated=1;
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | productx | p0         | ref  | outdated      | outdated | 1       | const | 1000 |   100.00 | Using index |
+----+-------------+----------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

I understand it can be done with smallint however if it does not take forever to fix, I am staying with enum.

Isn't this fixed by the patch in Bug #75085 ?