Bug #113419 Contribution by Tencent: partition prune ignore subpartition where condition

• Submitted: 14 Dec 2023 12:50
• Modified: 15 Dec 2023 13:58
• Reporter: Xiaodong Huang (OCA)
• Status: Verified
• Category: MySQL Server: Partitions
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any
• Tags: DML, partition prune

[14 Dec 2023 12:50] Xiaodong Huang

Description:
When a columns partition use prefix partial partition fields to prune partition ,the subpartition pruning becomes invalid.

How to repeat:
CREATE TABLE `t1` (
  `a` int,
  `b` int,
   c  datetime,
   d  date,
   e  char(3),
   f binary(3),
   g varbinary(3),
  KEY `k1` (`a`,`b`) global,
  primary key pk(a, b, c, d, e, f, g)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE columns (a, f, g)
SUBPARTITION BY HASH (b) SUBPARTITIONS 2
(PARTITION p0 VALUES LESS THAN (100, '\1\0\0', '\1\0\0') ENGINE = InnoDB,
 PARTITION p1 VALUES LESS THAN (200, '\2\0\0', '\2\0\0') ENGINE = InnoDB);

explain select * from t1 where a < 99 and b = 99;

In this case, "a < 99" is a prefix partial partition field where condition,  and "b = 99" is subpartition field where condition. The partitions of EXPLAIN result should be either "p0_p0sp0" or "p0_p0sp1", not "p0_p0sp0, p0_p0sp1".

Suggested fix:
When “find_used_partitions" use prefix partial partition fields to prune partition, it sets the “ignore_part_fields" flag to ignore the remaining unused partition fields. However it also inadvertently ignores the subpartition fields where condition.

[14 Dec 2023 12:50] Xiaodong Huang

simple fix

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: fix_patch (application/octet-stream, text), 653 bytes.

[14 Dec 2023 13:50] MySQL Verification Team

Hi Mr. Huang,

Thank you for your bug report.

However, we are not able to repeat it.

Since 5.7 is no longer supported, we get the same error on 8.0, 8.1 and 8.2:

ERROR 1064 (42000) at line 3: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'global,  primary key pk(a, b, c, d, e, f, g) ...

Can't repeat.

[15 Dec 2023 12:39] Xiaodong Huang

Need to modify "KEY `k1` (`a`, `b`) global' to "KEY `k1` (`a`, `b`)" . Below are my test results:

(root@localhost) [test]> CREATE TABLE `t1` (
    ->   `a` int,
    ->   `b` int,
    ->    c  datetime,
    ->    d  date,
    ->    e  char(3),
    ->    f binary(3),
    ->    g varbinary(3),
    ->   KEY `k1` (`a`,`b`) ,
    ->   primary key pk(a, b, c, d, e, f, g)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    -> PARTITION BY RANGE columns (a, f, g)
    -> SUBPARTITION BY HASH (b) SUBPARTITIONS 2
    -> (PARTITION p0 VALUES LESS THAN (100, '\1\0\0', '\1\0\0') ENGINE = InnoDB,
    ->  PARTITION p1 VALUES LESS THAN (200, '\2\0\0', '\2\0\0') ENGINE = InnoDB);
Query OK, 0 rows affected (0.56 sec)

(root@localhost) [test]> explain select * from t1 where a < 99 and b = 99;
+----+-------------+-------+-------------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions        | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+-------------------+-------+---------------+------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | p0_p0sp0,p0_p0sp1 | index | PRIMARY,k1    | k1   | 8       | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+-------------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.02 sec)

[15 Dec 2023 13:58] MySQL Verification Team

Hi Mr. Huang,

We were able to repeat the behaviour.

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t1	p0_p0sp0,p0_p0sp1	index	PRIMARY,k1	k1	8	NULL	1	100.00	Using where; Using index

This is now a verified bug.

Thank you for your patch !!!!

Same behaviour in 8.0 and 8.1.