planner: wrong empty results when joining two partitioning tables

[qw4990]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE `tx1`  (
  `ID` varchar(13),
  `a` varchar(13),
  `b` varchar(4000),
  `ltype` int(5) NOT NULL
);

CREATE TABLE `tx2`  (
  `ID` varchar(13),
  `rid` varchar(12),
  `a` varchar(9),
  `b` varchar(8),
  `c` longtext,
  `d` varchar(12),
  `ltype` int(5) NOT NULL
) PARTITION BY LIST (`ltype`)
(PARTITION `p1` VALUES IN (501),
PARTITION `p2` VALUES IN (502));

insert into tx1 values(1,1,1,501);
insert into tx2 values(1,1,1,1,1,1,501);

select * from tx1 inner join tx2 on tx1.ID=tx2.ID and tx1.ltype=tx2.ltype where tx2.rid='1'; 

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

The query should return some rows.

3. What did you see instead (Required)

The query returns an empty result.

4. What is your TiDB version? (Required)

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                               |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.7.0-alpha-246-g145b7cdf72-dirty
Edition: Community
Git Commit Hash: 145b7cdf72feeecf7f2b0b3e01693c603712dce5
Git Branch: master
UTC Build Time: 2023-03-13 05:15:10
GoVersion: go1.19.1
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: unistore |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[qw4990]

The partition pruning result for tx2 is wrong. As below, the planner thinks there is no partition left after pruning, so it uses a TableDual for tx2:

mysql> explain select * from tx1 inner join tx2 on tx1.ID=tx2.ID and tx1.ltype=tx2.ltype where tx2.rid='1';
+-------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| id                            | estRows | task      | access object | operator info                                                                                                                                  |
+-------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_11                 | 0.00    | root      |               | test.tx1.id, test.tx1.a, test.tx1.b, test.tx1.ltype, test.tx2.id, test.tx2.rid, test.tx2.a, test.tx2.b, test.tx2.c, test.tx2.d, test.tx2.ltype |
| └─HashJoin_13                 | 0.00    | root      |               | inner join, equal:[eq(test.tx2.id, test.tx1.id) eq(test.tx2.ltype, test.tx1.ltype)]                                                            |
|   ├─TableDual_18(Build)       | 0.00    | root      |               | rows:0                                                                                                                                         |
|   └─TableReader_17(Probe)     | 1.00    | root      |               | data:Selection_16                                                                                                                              |
|     └─Selection_16            | 1.00    | cop[tikv] |               | not(isnull(test.tx1.id))                                                                                                                       |
|       └─TableFullScan_15      | 1.00    | cop[tikv] | table:tx1     | keep order:false, stats:pseudo                                                                                                                 |
+-------------------------------+---------+-----------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------+

This is caused by the planner assigning a wrong unique ID for the partitioning col ltype. The wrong unique ID is 7 which conflicts with rid, and so then the planner applies partition pruning for ltype on the filter tx2.rid='1' wrongly.

[qw4990]

I'm not sure how to fix this. Please take a look at this @winoros @tiancaiamao @mjonss

[qw4990]

Unique IDs here is not correct:

[mjonss]

I think the issue may be in how the unique IDs are updated during the pruning, like in newListPartitionPruner, not that they are stored during generating the newPartitionExpr, which is cached in the table and reused during multiple queries.

Then (l *listPartitionPruner) detachCondAndBuildRange does the wrong thing.

[qw4990]

fixed by #42193