NOT IN and != is not handled in partition pruning

[mjonss]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t (a int, b varchar(255))
PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (1),
PARTITION p1 VALUES LESS THAN (2));
INSERT INTO t VALUES (0, "0"), (1,"1");
EXPLAIN SELECT * FROM t WHERE a != 1;
EXPLAIN SELECT * FROM t WHERE a NOT IN (-1,1);

2. What did you expect to see? (Required)

EXPLAIN SELECT * FROM t WHERE a != 1
+------------------------------+----------+-----------+-----------------------+--------------------------------+
| id                           | estRows  | task      | access object         | operator info                  |
+------------------------------+----------+-----------+-----------------------+--------------------------------+
| PartitionUnion_9             | 13313.33 | root      |                       |                                |
| ├─TableReader_12             | 6656.67  | root      |                       | data:Selection_11              |
| │ └─Selection_11             | 6656.67  | cop[tikv] |                       | ne(test.t.a, 1)                |
| │   └─TableFullScan_10       | 10000.00 | cop[tikv] | table:t, partition:p0 | keep order:false, stats:pseudo |
+------------------------------+----------+-----------+-----------------------+--------------------------------+
4 rows in set (0.00 sec)

EXPLAIN SELECT * FROM t WHERE a NOT IN (-1,1)

+------------------------------+----------+-----------+-----------------------+--------------------------------+
| id                           | estRows  | task      | access object         | operator info                  |
+------------------------------+----------+-----------+-----------------------+--------------------------------+
| PartitionUnion_9             | 7666.67  | root      |                       |                                |
| ├─TableReader_12             | 3833.33  | root      |                       | data:Selection_11              |
| │ └─Selection_11             | 3833.33  | cop[tikv] |                       | not(in(test.t.a, -1, 1))       |
| │   └─TableFullScan_10       | 10000.00 | cop[tikv] | table:t, partition:p0 | keep order:false, stats:pseudo |
+------------------------------+----------+-----------+-----------------------+--------------------------------+
4 rows in set (0.00 sec)

3. What did you see instead (Required)

EXPLAIN SELECT * FROM t WHERE a != 1
+------------------------------+----------+-----------+-----------------------+--------------------------------+
| id                           | estRows  | task      | access object         | operator info                  |
+------------------------------+----------+-----------+-----------------------+--------------------------------+
| PartitionUnion_9             | 13313.33 | root      |                       |                                |
| ├─TableReader_12             | 6656.67  | root      |                       | data:Selection_11              |
| │ └─Selection_11             | 6656.67  | cop[tikv] |                       | ne(test.t.a, 1)                |
| │   └─TableFullScan_10       | 10000.00 | cop[tikv] | table:t, partition:p0 | keep order:false, stats:pseudo |
| └─TableReader_15             | 6656.67  | root      |                       | data:Selection_14              |
|   └─Selection_14             | 6656.67  | cop[tikv] |                       | ne(test.t.a, 1)                |
|     └─TableFullScan_13       | 10000.00 | cop[tikv] | table:t, partition:p1 | keep order:false, stats:pseudo |
+------------------------------+----------+-----------+-----------------------+--------------------------------+
7 rows in set (0.00 sec)

+------------------------------+----------+-----------+-----------------------+--------------------------------+
| id                           | estRows  | task      | access object         | operator info                  |
+------------------------------+----------+-----------+-----------------------+--------------------------------+
| PartitionUnion_9             | 7666.67  | root      |                       |                                |
| ├─TableReader_12             | 3833.33  | root      |                       | data:Selection_11              |
| │ └─Selection_11             | 3833.33  | cop[tikv] |                       | not(in(test.t.a, -1, 1))       |
| │   └─TableFullScan_10       | 10000.00 | cop[tikv] | table:t, partition:p0 | keep order:false, stats:pseudo |
| └─TableReader_15             | 3833.33  | root      |                       | data:Selection_14              |
|   └─Selection_14             | 3833.33  | cop[tikv] |                       | not(in(test.t.a, -1, 1))       |
|     └─TableFullScan_13       | 10000.00 | cop[tikv] | table:t, partition:p1 | keep order:false, stats:pseudo |
+------------------------------+----------+-----------+-----------------------+--------------------------------+
7 rows in set (0.00 sec)

4. What is your TiDB version? (Required)

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tidb_version() | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Release Version: v4.0.0-beta.2-2748-g8b0305aa2 Edition: Community Git Commit Hash: 8b0305aa2af38f201c893fe9ce8c9af4201ebbc8 Git Branch: master UTC Build Time: 2021-04-27 15:34:22 GoVersion: go1.16.3 Race Enabled: false TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 Check Table Before Drop: false | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

[mjonss]

I consider this only to be a minor bug, since MySQL 8.0.24 seems to prune this. But normally ranges should be large, so I think the benefit is more for non common use cases.

[mjonss]

/type enhancement

[mjonss]

/remove-type bug

[mjonss]

/component tablepartition

[ymkzpx]

> set @@tidb_partition_prune_mode = 'dynamic';

latest execution result of tidb.

> tidb> EXPLAIN SELECT * FROM t WHERE a != 1;
+-------------------------+---------+-----------+---------------+------------------+
| id                      | estRows | task      | access object | operator info    |
+-------------------------+---------+-----------+---------------+------------------+
| TableReader_7           | 1.00    | root      | partition:all | data:Selection_6 |
| └─Selection_6           | 1.00    | cop[tikv] |               | ne(test.t.a, 1)  |
|   └─TableFullScan_5     | 2.00    | cop[tikv] | table:t       | keep order:false |
+-------------------------+---------+-----------+---------------+------------------+

I think the result is correct in the case above, but NOT IN and != is not handled in partition pruning that is exist.

CREATE TABLE t (a int, b varchar(255)) PARTITION BY list (a) (PARTITION p0 VALUES IN  (1), PARTITION p1 VALUES IN  (2));
Query OK, 0 rows affected (0.01 sec)
INSERT INTO t VALUES (2, "2"), (1,"1");
tidb> EXPLAIN SELECT * FROM t WHERE a != 1;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 6656.67  | root      | partition:all | data:Selection_6               |
| └─Selection_6           | 6656.67  | cop[tikv] |               | ne(test.t.a, 1)                |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+

tidb> EXPLAIN SELECT * FROM t WHERE a = 1;
+-------------------------+----------+-----------+---------------+--------------------------------+
| id                      | estRows  | task      | access object | operator info                  |
+-------------------------+----------+-----------+---------------+--------------------------------+
| TableReader_7           | 10.00    | root      | partition:p0  | data:Selection_6               |
| └─Selection_6           | 10.00    | cop[tikv] |               | eq(test.t.a, 1)                |
|   └─TableFullScan_5     | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+--------------------------------+
3 rows in set (0.01 sec)