Description:
In the following case, there are two tables named t1 and t2:
MySQL [test]> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` int DEFAULT NULL,
`b` int DEFAULT NULL,
`c` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
/*!50100 PARTITION BY RANGE (`b`)
SUBPARTITION BY HASH (`c`)
SUBPARTITIONS 3
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB) */
1 row in set (0.01 sec)
MySQL [test]> show create table t2\G
*************************** 1. row ***************************
Table: t2
Create Table: CREATE TABLE `t2` (
`a` int DEFAULT NULL,
`b` int DEFAULT NULL,
`c` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
/*!50100 PARTITION BY RANGE (`b`)
SUBPARTITION BY HASH (`c`)
SUBPARTITIONS 3
(PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB,
PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB) */
1 row in set (0.00 sec)
And there are 7 rows in each table:
MySQL [test]> select * from t1;
+------+------+------+
| a | b | c |
+------+------+------+
| 3 | 1 | 3 |
| 6 | 1 | 6 |
| 1 | 1 | 1 |
| 4 | 1 | 4 |
| 7 | 1 | 7 |
| 2 | 1 | 2 |
| 5 | 1 | 5 |
+------+------+------+
7 rows in set (0.00 sec)
MySQL [test]> select * from t2;
+------+------+------+
| a | b | c |
+------+------+------+
| 3 | 1 | 3 |
| 6 | 1 | 6 |
| 1 | 1 | 1 |
| 4 | 1 | 4 |
| 7 | 1 | 7 |
| 2 | 1 | 2 |
| 5 | 1 | 5 |
+------+------+------+
7 rows in set (0.00 sec)
And the plan of the following SQL statement is:
MySQL [test]> explain select * from t1 left join t2 on t1.b = t2.b and t1.c = t2.c where t1.b = 1 and t2.b = 1 and t1.c = 1;
+----+-------------+-------+----------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+----------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | t1 | p0_p0sp1 | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 1 | SIMPLE | t2 | p0_p0sp0,p0_p0sp1,p0_p0sp2 | ALL | NULL | NULL | NULL | NULL | 7 | 14.29 | Using where; Using join buffer (hash join) |
+----+-------------+-------+----------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.01 sec)
MySQL [test]> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`c` = 1) and (`test`.`t2`.`c` = 1) and (`test`.`t1`.`b` = 1) and (`test`.`t2`.`b` = 1)) |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
As indicated by the warning, the predicate (`test`.`t2`.`c` = 1) was added to the original SQL statement. This predicate is inferred by equal propagation from (`test`.`t1`.`c` = 1) and t1.c = t2.c .
However, the EXPLAIN result indicates that the subpartitions of t2 were not pruned by (`test`.`t2`.`c` = 1), which is unexpected. After all, t1 and t2 share the same conditions (b=1 and c=1) and the same partitioning method. Yet, the EXPLAIN output shows that t2 needs to access three subpartitions (p0_p0sp0, p0_p0sp1, p0_p0sp2), whereas t1 only needs to access one subpartition (p0_p0sp1).
In the code, the prune_partitions function is invoked twice.
The first invocation occurs during the prepare phase (specifically within Sql_cmd_dml::prepare->Sql_cmd_select::prepare_inner->SELECT_LEX::prepare->SELECT_LEX::apply_local_transforms->SELECT_LEX::prune_table_partitions_recursively->SELECT_LEX::prune_table_partitions), before build_equal_items is executed.
The second invocation takes place in optimize_phase1 (via JOIN::optimize_phase1->JOIN::prune_table_partitions), after build_equal_items has completed.
During the first call to prune_partitions, the first-level partitions of table t2 are pruned based on the WHERE clause condition t2.b = 1. Consequently, part_info->is_pruning_completed is set to true.
However, in the second call to prune_partitions, even if build_equal_items has subsequently deduced t2.c = 1, the secondary (second-level) partitions cannot be further pruned because part_info->is_pruning_completed is already set to true.
```
/*
If the prepare stage already have completed pruning successfully,
it is no use of running prune_partitions() again on the same condition.
Since it will not be able to prune anything more than the previous call
from the prepare step.
*/
if (part_info && part_info->is_pruning_completed) return false;
```
```
/*
Decide if the current pruning attempt is the final one.
During the prepare phase, before locking, subqueries and stored programs
are not evaluated. So we need to run prune_partitions() a second time in
the optimize phase to prune partitions for reading, when subqueries and
stored programs may be evaluated.
The upcoming pruning attempt will be the final one when:
- condition is constant, or
- condition may vary for every row (so there is nothing to prune) or
- evaluation is in execution phase.
*/
if (pprune_cond->const_item() || !pprune_cond->const_for_execution() ||
thd->lex->is_query_tables_locked())
part_info->is_pruning_completed = true;
```
How to repeat:
drop table if exists t1;
create table t1 (a int, b int, c int) partition by range (b) subpartition by hash (c) subpartitions 3 (partition p0 values less than(10), partition p1 values less than(20));
insert into t1 values(1,1,1),(2,1,2),(3,1,3),(4,1,4),(5,1,5),(6,1,6),(7,1,7);
drop table if exists t2;
create table t2 (a int, b int, c int) partition by range (b) subpartition by hash (c) subpartitions 3 (partition p0 values less than(10), partition p1 values less than(20));
insert into t2 values(1,1,1),(2,1,2),(3,1,3),(4,1,4),(5,1,5),(6,1,6),(7,1,7);
explain select * from t1 left join t2 on t1.b = t2.b and t1.c = t2.c where t1.b = 1 and t2.b = 1 and t1.c = 1;
Description: In the following case, there are two tables named t1 and t2: MySQL [test]> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `a` int DEFAULT NULL, `b` int DEFAULT NULL, `c` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 /*!50100 PARTITION BY RANGE (`b`) SUBPARTITION BY HASH (`c`) SUBPARTITIONS 3 (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB) */ 1 row in set (0.01 sec) MySQL [test]> show create table t2\G *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `a` int DEFAULT NULL, `b` int DEFAULT NULL, `c` int DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 /*!50100 PARTITION BY RANGE (`b`) SUBPARTITION BY HASH (`c`) SUBPARTITIONS 3 (PARTITION p0 VALUES LESS THAN (10) ENGINE = InnoDB, PARTITION p1 VALUES LESS THAN (20) ENGINE = InnoDB) */ 1 row in set (0.00 sec) And there are 7 rows in each table: MySQL [test]> select * from t1; +------+------+------+ | a | b | c | +------+------+------+ | 3 | 1 | 3 | | 6 | 1 | 6 | | 1 | 1 | 1 | | 4 | 1 | 4 | | 7 | 1 | 7 | | 2 | 1 | 2 | | 5 | 1 | 5 | +------+------+------+ 7 rows in set (0.00 sec) MySQL [test]> select * from t2; +------+------+------+ | a | b | c | +------+------+------+ | 3 | 1 | 3 | | 6 | 1 | 6 | | 1 | 1 | 1 | | 4 | 1 | 4 | | 7 | 1 | 7 | | 2 | 1 | 2 | | 5 | 1 | 5 | +------+------+------+ 7 rows in set (0.00 sec) And the plan of the following SQL statement is: MySQL [test]> explain select * from t1 left join t2 on t1.b = t2.b and t1.c = t2.c where t1.b = 1 and t2.b = 1 and t1.c = 1; +----+-------------+-------+----------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+----------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | t1 | p0_p0sp1 | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | | 1 | SIMPLE | t2 | p0_p0sp0,p0_p0sp1,p0_p0sp2 | ALL | NULL | NULL | NULL | NULL | 7 | 14.29 | Using where; Using join buffer (hash join) | +----+-------------+-------+----------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ 2 rows in set, 1 warning (0.01 sec) MySQL [test]> show warnings; +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t1`.`c` AS `c`,`test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`c` = 1) and (`test`.`t2`.`c` = 1) and (`test`.`t1`.`b` = 1) and (`test`.`t2`.`b` = 1)) | +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) As indicated by the warning, the predicate (`test`.`t2`.`c` = 1) was added to the original SQL statement. This predicate is inferred by equal propagation from (`test`.`t1`.`c` = 1) and t1.c = t2.c . However, the EXPLAIN result indicates that the subpartitions of t2 were not pruned by (`test`.`t2`.`c` = 1), which is unexpected. After all, t1 and t2 share the same conditions (b=1 and c=1) and the same partitioning method. Yet, the EXPLAIN output shows that t2 needs to access three subpartitions (p0_p0sp0, p0_p0sp1, p0_p0sp2), whereas t1 only needs to access one subpartition (p0_p0sp1). In the code, the prune_partitions function is invoked twice. The first invocation occurs during the prepare phase (specifically within Sql_cmd_dml::prepare->Sql_cmd_select::prepare_inner->SELECT_LEX::prepare->SELECT_LEX::apply_local_transforms->SELECT_LEX::prune_table_partitions_recursively->SELECT_LEX::prune_table_partitions), before build_equal_items is executed. The second invocation takes place in optimize_phase1 (via JOIN::optimize_phase1->JOIN::prune_table_partitions), after build_equal_items has completed. During the first call to prune_partitions, the first-level partitions of table t2 are pruned based on the WHERE clause condition t2.b = 1. Consequently, part_info->is_pruning_completed is set to true. However, in the second call to prune_partitions, even if build_equal_items has subsequently deduced t2.c = 1, the secondary (second-level) partitions cannot be further pruned because part_info->is_pruning_completed is already set to true. ``` /* If the prepare stage already have completed pruning successfully, it is no use of running prune_partitions() again on the same condition. Since it will not be able to prune anything more than the previous call from the prepare step. */ if (part_info && part_info->is_pruning_completed) return false; ``` ``` /* Decide if the current pruning attempt is the final one. During the prepare phase, before locking, subqueries and stored programs are not evaluated. So we need to run prune_partitions() a second time in the optimize phase to prune partitions for reading, when subqueries and stored programs may be evaluated. The upcoming pruning attempt will be the final one when: - condition is constant, or - condition may vary for every row (so there is nothing to prune) or - evaluation is in execution phase. */ if (pprune_cond->const_item() || !pprune_cond->const_for_execution() || thd->lex->is_query_tables_locked()) part_info->is_pruning_completed = true; ``` How to repeat: drop table if exists t1; create table t1 (a int, b int, c int) partition by range (b) subpartition by hash (c) subpartitions 3 (partition p0 values less than(10), partition p1 values less than(20)); insert into t1 values(1,1,1),(2,1,2),(3,1,3),(4,1,4),(5,1,5),(6,1,6),(7,1,7); drop table if exists t2; create table t2 (a int, b int, c int) partition by range (b) subpartition by hash (c) subpartitions 3 (partition p0 values less than(10), partition p1 values less than(20)); insert into t2 values(1,1,1),(2,1,2),(3,1,3),(4,1,4),(5,1,5),(6,1,6),(7,1,7); explain select * from t1 left join t2 on t1.b = t2.b and t1.c = t2.c where t1.b = 1 and t2.b = 1 and t1.c = 1;