Description:
Hello,
I find that there is a case that partition pruning is not done well, as a result all partitions have to be accessed, which is actually unnecessary. The case is like this:
```
explain select count(*) from bugtest.t1 where (pk,c1) in ((1,1), (2,2));
```
If I exchange the sequence of fields in in clause, the partition pruning problem is not there anymore:
```explain select count(*) from bugtest.t1 where (c1,pk) in ((1,1), (2,2));
```
How to repeat:
# Details are like this:
mysql> create database if not exists bugtest;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> use bugtest;
Database changed
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE t1 (
-> `pk` int primary key,
-> `c1` int
-> ) ENGINE=InnoDB
-> PARTITION BY RANGE COLUMNS(pk)
-> (PARTITION P1 VALUES LESS THAN (10) ENGINE = InnoDB,
-> PARTITION P2 VALUES LESS THAN (20) ENGINE = InnoDB,
-> PARTITION P3 VALUES LESS THAN (30) ENGINE = InnoDB,
-> PARTITION P4 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> # GOOD optimization
mysql> explain select count(*) from bugtest.t1 where (pk,c1) in ((1,1), (2,2));
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | P1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> # BAD optimization:
mysql> explain select count(*) from bugtest.t1 where (c1,pk) in ((1,1), (2,2));
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | P1,P2,P3,P4 | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
Description: Hello, I find that there is a case that partition pruning is not done well, as a result all partitions have to be accessed, which is actually unnecessary. The case is like this: ``` explain select count(*) from bugtest.t1 where (pk,c1) in ((1,1), (2,2)); ``` If I exchange the sequence of fields in in clause, the partition pruning problem is not there anymore: ```explain select count(*) from bugtest.t1 where (c1,pk) in ((1,1), (2,2)); ``` How to repeat: # Details are like this: mysql> create database if not exists bugtest; Query OK, 1 row affected, 1 warning (0.00 sec) mysql> use bugtest; Database changed mysql> drop table if exists t1; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t1 ( -> `pk` int primary key, -> `c1` int -> ) ENGINE=InnoDB -> PARTITION BY RANGE COLUMNS(pk) -> (PARTITION P1 VALUES LESS THAN (10) ENGINE = InnoDB, -> PARTITION P2 VALUES LESS THAN (20) ENGINE = InnoDB, -> PARTITION P3 VALUES LESS THAN (30) ENGINE = InnoDB, -> PARTITION P4 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB -> ); Query OK, 0 rows affected (0.02 sec) mysql> # GOOD optimization mysql> explain select count(*) from bugtest.t1 where (pk,c1) in ((1,1), (2,2)); +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | P1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | 100.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> # BAD optimization: mysql> explain select count(*) from bugtest.t1 where (c1,pk) in ((1,1), (2,2)); +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | t1 | P1,P2,P3,P4 | ALL | PRIMARY | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)