Unexpected Result in TiFlash 3

[bajinsheng]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 BOOL, PRIMARY KEY(c0));
CREATE TABLE t1(c0 BOOL, PRIMARY KEY(c0));
INSERT INTO t0(c0) VALUES (false);
INSERT INTO t1(c0) VALUES (false);
ALTER TABLE t0 SET TIFLASH REPLICA 1;
SELECT SLEEP(3);

SELECT * FROM  t1 NATURAL JOIN t0 WHERE NOT t0.c0; -- {0}
SELECT /*+ READ_FROM_STORAGE(TIFLASH[t0])*/ * FROM  t1 NATURAL JOIN t0 WHERE NOT t0.c0; -- empty

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

Both queries return the same result.

3. What did you see instead (Required)

Both queries return different results.

4. What is your TiDB version? (Required)

| Release Version: v7.4.0-alpha
Edition: Community
Git Commit Hash: c62fc67cabce7c1cf2ae7fb1a46777d57fedd109
Git Branch: heads/refs/tags/v7.4.0-alpha
UTC Build Time: 2023-09-25 14:34:33
GoVersion: go1.21.1
Race Enabled: false
Check Table Before Drop: false
Store: tikv |

[bajinsheng]

/label fuzz/sqlancer

[seiya-annie]

mysql> SELECT * FROM  t1 NATURAL JOIN t0 WHERE NOT t0.c0; 
+----+
| c0 |
+----+
|  0 |
+----+
1 row in set (0.00 sec)

mysql> SELECT /*+ READ_FROM_STORAGE(TIFLASH[t0])*/ * FROM  t1 NATURAL JOIN t0 WHERE NOT t0.c0;
Empty set (0.00 sec)

mysql> 
mysql> explain  SELECT /*+ READ_FROM_STORAGE(TIFLASH[t0])*/ * FROM  t1 NATURAL JOIN t0 WHERE NOT t0.c0;
+---------------------------+---------+------+---------------+-------------------------------------------------------+
| id                        | estRows | task | access object | operator info                                         |
+---------------------------+---------+------+---------------+-------------------------------------------------------+
| MergeJoin_8               | 1.00    | root |               | inner join, left key:test.t1.c0, right key:test.t0.c0 |
| ├─TableDual_23(Build) | 1.00    | root |               | rows:0                                                |
| └─Point_Get_22(Probe) | 1.00    | root | table:t1      | handle:0                                              |
+---------------------------+---------+------+---------------+-------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> 
mysql> explain SELECT * FROM  t1 NATURAL JOIN t0 WHERE NOT t0.c0;
+---------------------------+---------+------+---------------+-------------------------------------------------------+
| id                        | estRows | task | access object | operator info                                         |
+---------------------------+---------+------+---------------+-------------------------------------------------------+
| MergeJoin_8               | 1.00    | root |               | inner join, left key:test.t1.c0, right key:test.t0.c0 |
| ├─Point_Get_34(Build) | 1.00    | root | table:t0      | handle:0                                              |
| └─Point_Get_33(Probe) | 1.00    | root | table:t1      | handle:0                                              |
+---------------------------+---------+------+---------------+-------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> 

[winoros]

mysql> explain select /*+ READ_FROM_STORAGE(TIFLASH[t0])*/ * from t0 where t0.c0>=1 and t0.c0 <=1;
+-------------+---------+------+---------------+---------------+
| id          | estRows | task | access object | operator info |
+-------------+---------+------+---------------+---------------+
| TableDual_6 | 1.00    | root |               | rows:0        |
+-------------+---------+------+---------------+---------------+
1 row in set (0.00 sec)

The point range converts the wrong table dual for MPP plan.

[winoros]

Move the severity to major since TiDB should choose tikv case for better performance for point range.