Bug #105056 tables in subquery is not pruned-partition well
Submitted: 28 Sep 2:33 Modified: 28 Sep 4:46
Reporter: Brian Yue (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:8.0.25, 8.0.26, 5.7.35 OS:Any (rhel-7.4)
Assigned to: CPU Architecture:Any (x86-64)

[28 Sep 2:33] Brian Yue
Description:
Hello, dear verification team.

  We find that the tables in a subquery is not optimized well with `prune_partitions` that all partitiones are finally accessed, which is actualy unnecessary. If there is too many partitions for this table, it will introduce a lot of unnecessary cost.

How to repeat:
[yxx_git1@localhost build]$ mysql -uroot -p'XXXXX'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.25-debug Source distribution

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database cca;
Query OK, 1 row affected (0.01 sec)

mysql> use cca
Database changed
mysql> create table t1(a  varchar(8) not null default '') ;
Query OK, 0 rows affected (0.37 sec)

mysql> create table t2(b  varchar(8) not null default '') partition by range columns (b)
    -> (PARTITION P23 VALUES LESS THAN ('20210924'),
    -> PARTITION P24 VALUES LESS THAN ('20210925'),
    -> PARTITION P25 VALUES LESS THAN ('20210926'),
    -> PARTITION P26 VALUES LESS THAN ('20210927'),
    -> PARTITION P27 VALUES LESS THAN ('20210928'),
    -> PARTITION P28 VALUES LESS THAN ('20210929'),
    -> PARTITION PMAX VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (2.57 sec)

mysql> insert into t1 values ('20210924');
Query OK, 1 row affected (0.02 sec)

mysql> insert into t2 values ('20210924');
Query OK, 1 row affected (0.01 sec)

# In this case, we access only partition `P24` of table `t2`, but as a result all partitions are accessed

mysql> explain select * from t1 left join (select * from t2 where b = '20210924') dt on a = b;
+----+-------------+-------+------------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions                   | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | t1    | NULL                         | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL                                       |
|  1 | SIMPLE      | t2    | P23,P24,P25,P26,P27,P28,PMAX | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------------------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                       |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `cca`.`t1`.`a` AS `a`,`cca`.`t2`.`b` AS `b` from `cca`.`t1` left join (`cca`.`t2`) on(((`cca`.`t2`.`b` = `cca`.`t1`.`a`) and (`cca`.`t1`.`a` = '20210924'))) where true |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t1 left join (select * from t2 where b = '20210924') dt on a = b;
+----------+----------+
| a        | b        |
+----------+----------+
| 20210924 | 20210924 |
+----------+----------+
1 row in set (0.00 sec)

mysql>

Suggested fix:
consider execute `prune_partitions` optimization for inner tables of subqueries.
[28 Sep 4:46] MySQL Verification Team
Hello Brian Yue,

Thank you for the report and feedback.
Observed that 8.0.26 release build affected.

regards,
Umesh