List partition can't prune access partition with greater or less condition

[Defined2014]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `t` (
  `a` int NOT NULL,
  `b` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY LIST (`a`)
(PARTITION p0 VALUES IN (null, 1,2,3) ENGINE = InnoDB,
 PARTITION p2 VALUES IN (-1,-2,-3) ENGINE = InnoDB,
 PARTITION p1 VALUES IN (100,101,102) ENGINE = InnoDB) */;
explain select * from t where a > 0;  -- access p0,p1
explain select * from t where a < 0;  -- access p2

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

prune success

mysql> explain select * from t where a > 0;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | p0,p1      | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

3. What did you see instead (Required)

access all partitions

mysql> explain select * from t where a > 0;
+------------------------+---------+-----------+---------------+------------------------------------------------+
| id                     | estRows | task      | access object | operator info                                  |
+------------------------+---------+-----------+---------------+------------------------------------------------+
| TableReader_6          | 3333.33 | root      | partition:all | data:TableRangeScan_5                          |
| └─TableRangeScan_5     | 3333.33 | cop[tikv] | table:t       | range:(0,+inf], keep order:false, stats:pseudo |
+------------------------+---------+-----------+---------------+------------------------------------------------+
2 rows in set (0.01 sec)

4. What is your TiDB version? (Required)

mysql> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.4.0-alpha-419-g3df0f2e927-dirty
Edition: Community
Git Commit Hash: 3df0f2e927a4d1d5b9431ab4dc0f5a2a71f04786
Git Branch: master
UTC Build Time: 2024-10-16 02:36:14
GoVersion: go1.23.1
Race Enabled: false
Check Table Before Drop: false
Store: unistore |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

[Defined2014]

Use table schema below could prune unrelated partitions, but the performance is worse.

CREATE TABLE `t` (
  `a` int(11) NOT NULL,
  `b` varchar(255) COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  PRIMARY KEY (`a`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY LIST COLUMNS(`a`)
(PARTITION `p0` VALUES IN (1,2,3),
 PARTITION `p2` VALUES IN (-1,-2,-3),
 PARTITION `p1` VALUES IN (100,101,102));

mysql> explain select * from t where a > 0;
+------------------------+---------+-----------+-----------------+------------------------------------------------+
| id                     | estRows | task      | access object   | operator info                                  |
+------------------------+---------+-----------+-----------------+------------------------------------------------+
| TableReader_6          | 3333.33 | root      | partition:p0,p1 | data:TableRangeScan_5                          |
| └─TableRangeScan_5     | 3333.33 | cop[tikv] | table:t         | range:(0,+inf], keep order:false, stats:pseudo |
+------------------------+---------+-----------+-----------------+------------------------------------------------+
2 rows in set (0.01 sec)