tiflash return wrong results when enabling dynamic mode in partition table

[ChenPeng2013]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

use test;
drop table if exists IDT_RP24833;
CREATE TABLE `IDT_RP24833` (
  `COL1` bigint(16) DEFAULT '15' COMMENT 'NUMERIC UNIQUE INDEX',
  `COL2` varchar(20) DEFAULT NULL,
  `COL4` datetime DEFAULT NULL,
  `COL3` bigint(20) DEFAULT NULL,
  `COL5` float DEFAULT NULL,
  KEY `UK_COL1` (`COL1`) /*!80000 INVISIBLE */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
PARTITION BY RANGE ((`COL1`-57))
(PARTITION `P0` VALUES LESS THAN (-3503857335115112215),
 PARTITION `P1` VALUES LESS THAN (-2987877108151063747),
 PARTITION `P2` VALUES LESS THAN (-1981049919102122710),
 PARTITION `P3` VALUES LESS THAN (-1635802972727465681),
 PARTITION `P4` VALUES LESS THAN (1186020639986357714),
 PARTITION `P5` VALUES LESS THAN (1220018677454711359),
 PARTITION `PMX` VALUES LESS THAN (MAXVALUE));
 alter table IDT_RP24833 set tiflash replica 1;
 select sleep(5);
 insert into IDT_RP24833 values(-8448770111093677011, "郇鋺篤堯擈斥鍮啸赠璭饱磟朅闑傒聎疫ᛄ怖霃", "8781-05-02 04:23:03", -27252736532807028, -1.34554e38);
 
 
select /*+ read_from_storage(tikv[t1, t2]) */  * from IDT_RP24833 partition(p3, p4) t1 join IDT_RP24833 partition(p2) t2 on t1.col1 = t2.col1 where t1. col1 between -8448770111093677011 and -8448770111093677011 and t2. col1 <= -8448770111093677011;
select /*+ read_from_storage(tiflash[t1, t2]) */  * from IDT_RP24833 partition(p3, p4) t1 join IDT_RP24833 partition(p2) t2 on t1.col1 = t2.col1 where t1. col1 between -8448770111093677011 and -8448770111093677011 and t2. col1 <= -8448770111093677011;

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

MySQL [test]> select /*+ read_from_storage(tikv[t1, t2]) */  * from IDT_RP24833 partition(p3, p4) t1 join IDT_RP24833 partition(p2) t2 on t1.col1 = t2.col1 where t1. col1 between -8448770111093677011 and -8448770111093677011 and t2. col1 <= -8448770111093677011;
Empty set (0.00 sec)

MySQL [test]> select /*+ read_from_storage(tiflash[t1, t2]) */  * from IDT_RP24833 partition(p3, p4) t1 join IDT_RP24833 partition(p2) t2 on t1.col1 = t2.col1 where t1. col1 between -8448770111093677011 and -8448770111093677011 and t2. col1 <= -8448770111093677011;
Empty set (0.00 sec)

3. What did you see instead (Required)

MySQL [test]> select /*+ read_from_storage(tikv[t1, t2]) */  * from IDT_RP24833 partition(p3, p4) t1 join IDT_RP24833 partition(p2) t2 on t1.col1 = t2.col1 where t1. col1 between -8448770111093677011 and -8448770111093677011 and t2. col1 <= -8448770111093677011;
Empty set (0.00 sec)

MySQL [test]> select /*+ read_from_storage(tiflash[t1, t2]) */  * from IDT_RP24833 partition(p3, p4) t1 join IDT_RP24833 partition(p2) t2 on t1.col1 = t2.col1 where t1. col1 between -8448770111093677011 and -8448770111093677011 and t2. col1 <= -8448770111093677011;
+----------------------+--------------------------------------------------------------+---------------------+--------------------+-------------+----------------------+--------------------------------------------------------------+---------------------+--------------------+-------------+
| COL1                 | COL2                                                         | COL4                | COL3               | COL5        | COL1                 | COL2                                                         | COL4                | COL3               | COL5        |
+----------------------+--------------------------------------------------------------+---------------------+--------------------+-------------+----------------------+--------------------------------------------------------------+---------------------+--------------------+-------------+
| -8448770111093677011 | 郇鋺篤堯擈斥鍮啸赠璭饱磟朅闑傒聎疫ᛄ怖霃                      | 8781-05-02 04:23:03 | -27252736532807028 | -1.34554e38 | -8448770111093677011 | 郇鋺篤堯擈斥鍮啸赠璭饱磟朅闑傒聎疫ᛄ怖霃                      | 8781-05-02 04:23:03 | -27252736532807028 | -1.34554e38 |
+----------------------+--------------------------------------------------------------+---------------------+--------------------+-------------+----------------------+--------------------------------------------------------------+---------------------+--------------------+-------------+
1 row in set (0.02 sec)

4. What is your TiDB version? (Required)

Release Version: v6.3.0-alpha
Edition: Community
Git Commit Hash: 8b5b724d8a932239303a1d0ba547323eb0e5161b
Git Branch: heads/refs/tags/v6.3.0-alpha
UTC Build Time: 2022-08-19 14:31:05
GoVersion: go1.18.5
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

[ymkzpx]

tikv and tiflash explain result comparison

tikv explain result

mysql> explain select /*+ read_from_storage(tikv[t1, t2]) */  * from IDT_RP24833 partition(p3, p4) t1 join IDT_RP24833 partition(p2) t2 on t1.col1 = t2.col1 where t1. col1 between -8448770111093677011 and -8448770111093677011 and t2.col1 <= -8448770111093677011;
+------------------------------+----------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------+
| id                           | estRows  | task      | access object  | operator info                                                                                                                        |
+------------------------------+----------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------+
| HashJoin_10                  | 12.50    | root      |                | inner join, equal:[eq(test.idt_rp24833.col1, test.idt_rp24833.col1)]                                                                 |
| ├─TableReader_17(Build)      | 10.00    | root      | partition:dual | data:Selection_16                                                                                                                    |
| │ └─Selection_16             | 10.00    | cop[tikv] |                | ge(test.idt_rp24833.col1, -8448770111093677011), le(test.idt_rp24833.col1, -8448770111093677011), not(isnull(test.idt_rp24833.col1)) |
| │   └─TableFullScan_15       | 10000.00 | cop[tikv] | table:t2       | keep order:false, stats:pseudo                                                                                                       |
| └─TableReader_14(Probe)      | 10.00    | root      | partition:dual | data:Selection_13                                                                                                                    |
|   └─Selection_13             | 10.00    | cop[tikv] |                | ge(test.idt_rp24833.col1, -8448770111093677011), le(test.idt_rp24833.col1, -8448770111093677011), not(isnull(test.idt_rp24833.col1)) |
|     └─TableFullScan_12       | 10000.00 | cop[tikv] | table:t1       | keep order:false, stats:pseudo                                                                                                       |
+------------------------------+----------+-----------+----------------+--------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

tiflash explain result

mysql> explain select * from IDT_RP24833 partition(p3, p4) t1 join IDT_RP24833 partition(p2) t2 on t1.col1 = t2.col1 where t1. col1 between -8448770111093677011 and -8448770111093677011 and t2. col1 <= -8448770111093677011;
+--------------------------------------+----------+--------------+--------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+
| id                                   | estRows  | task         | access object                              | operator info                                                                                                                        |
+--------------------------------------+----------+--------------+--------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+
| TableReader_19                       | 12.50    | root         | partition:dual of t1, partition:dual of t2 | data:ExchangeSender_18                                                                                                               |
| └─ExchangeSender_18                  | 12.50    | mpp[tiflash] |                                            | ExchangeType: PassThrough                                                                                                            |
|   └─HashJoin_9                       | 12.50    | mpp[tiflash] |                                            | inner join, equal:[eq(test.idt_rp24833.col1, test.idt_rp24833.col1)]                                                                 |
|     ├─ExchangeReceiver_15(Build)     | 10.00    | mpp[tiflash] |                                            |                                                                                                                                      |
|     │ └─ExchangeSender_14            | 10.00    | mpp[tiflash] |                                            | ExchangeType: Broadcast                                                                                                              |
|     │   └─Selection_13               | 10.00    | mpp[tiflash] |                                            | ge(test.idt_rp24833.col1, -8448770111093677011), le(test.idt_rp24833.col1, -8448770111093677011), not(isnull(test.idt_rp24833.col1)) |
|     │     └─TableFullScan_12         | 10000.00 | mpp[tiflash] | table:t1                                   | keep order:false, stats:pseudo, PartitionTableScan:true                                                                              |
|     └─Selection_17(Probe)            | 10.00    | mpp[tiflash] |                                            | ge(test.idt_rp24833.col1, -8448770111093677011), le(test.idt_rp24833.col1, -8448770111093677011), not(isnull(test.idt_rp24833.col1)) |
|       └─TableFullScan_16             | 10000.00 | mpp[tiflash] | table:t2                                   | keep order:false, stats:pseudo, PartitionTableScan:true                                                                              |
+--------------------------------------+----------+--------------+--------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.01 sec)

[bb7133]

Reassign this to "sig-execution", /cc @windtalker @zanmato1984