inconsistent results between tikv and tiflash when a 'not in' subquery contains null values

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS tb0;
DROP TABLE IF EXISTS tb1;
DROP TABLE IF EXISTS tb2;

CREATE TABLE tb0 (c0 FLOAT NOT NULL, c1 TEXT, c2 SMALLINT, c3 CHAR(77) NOT NULL);
CREATE TABLE tb1 (c0 DOUBLE, c1 CHAR(30) NOT NULL);
CREATE TABLE tb2 (c0 VARCHAR(237), c1 INT, c2 MEDIUMINT UNSIGNED NOT NULL, c3 VARCHAR(108), c4 MEDIUMINT UNSIGNED NOT NULL);

INSERT INTO tb0 (c0, c1, c2, c3) VALUES (568.7948247820782, '', -2959, '');
INSERT INTO tb1 (c0, c1) VALUES (4990.047273767186, 'lgx');
INSERT INTO tb1 (c0, c1) VALUES (NULL, 'n8');
INSERT INTO tb1 (c0, c1) VALUES (4108.400016626041, 'Sf');
INSERT INTO tb2 (c0, c1, c2, c3, c4) VALUES ('ya', 745145884, 1517240, 'I3', 7969908);
INSERT INTO tb2 (c0, c1, c2, c3, c4) VALUES ('6', -1945830187, 7439543, NULL, 797869);

ALTER TABLE tb0 SET TIFLASH REPLICA 1;
ALTER TABLE tb1 SET TIFLASH REPLICA 1;
ALTER TABLE tb2 SET TIFLASH REPLICA 1;


SELECT /*+ read_from_storage(tiflash[t0,t1,t2,t3]) */ 1
FROM tb1 as t0
         RIGHT JOIN tb2 AS t1 ON (t0.c0 = t1.c4)
         INNER JOIN tb1 AS t2 ON (t1.c4 = t2.c1)
         RIGHT JOIN tb0 AS t3
                    ON (t2.c1 = t3.c3)
WHERE (EXISTS (SELECT /*+ read_from_storage(tiflash[t0]) */ 1
                        FROM tb2 AS t0
                        WHERE (NOT (t1.c4 IN (SELECT /*+ read_from_storage(tiflash[t0]) */ t0.c3 FROM tb2 AS t0)))));


SELECT /*+ read_from_storage(tikv[t0,t1,t2,t3]) */ 1
FROM tb1 as t0
         RIGHT JOIN tb2 AS t1 ON (t0.c0 = t1.c4)
         INNER JOIN tb1 AS t2 ON (t1.c4 = t2.c1)
         RIGHT JOIN tb0 AS t3
                    ON (t2.c1 = t3.c3)
WHERE (EXISTS (SELECT /*+ read_from_storage(tikv[t0]) */ 1
                        FROM tb2 AS t0
                        WHERE (NOT (t1.c4 IN (SELECT /*+ read_from_storage(tikv[t0]) */ t0.c3 FROM tb2 AS t0)))));

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

mysql> SELECT /*+ read_from_storage(tikv[t0,t1,t2,t3]) */ 1
    -> FROM tb1 as t0
    ->          RIGHT JOIN tb2 AS t1 ON (t0.c0 = t1.c4)
    ->          INNER JOIN tb1 AS t2 ON (t1.c4 = t2.c1)
    ->          RIGHT JOIN tb0 AS t3
    ->                     ON (t2.c1 = t3.c3)
    -> WHERE (EXISTS (SELECT /*+ read_from_storage(tikv[t0]) */ 1
    ->                         FROM tb2 AS t0
    ->                         WHERE (NOT (t1.c4 IN (SELECT /*+ read_from_storage(tikv[t0]) */ t0.c3 FROM tb2 AS t0)))));
Empty set, 5 warnings (0.006 sec)

3. What did you see instead (Required)

mysql> SELECT /*+ read_from_storage(tiflash[t0,t1,t2,t3]) */ 1
    -> FROM tb1 as t0
    ->          RIGHT JOIN tb2 AS t1 ON (t0.c0 = t1.c4)
    ->          INNER JOIN tb1 AS t2 ON (t1.c4 = t2.c1)
    ->          RIGHT JOIN tb0 AS t3
    ->                     ON (t2.c1 = t3.c3)
    -> WHERE (EXISTS (SELECT /*+ read_from_storage(tiflash[t0]) */ 1
    ->                         FROM tb2 AS t0
    ->                         WHERE (NOT (t1.c4 IN (SELECT /*+ read_from_storage(tiflash[t0]) */ t0.c3 FROM tb2 AS t0)))));
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.042 sec)

4. What is your TiFlash version? (Required)

mysql> select tidb_version();
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v9.0.0-beta.2.pre-250-ga6d415f24a
Edition: Community
Git Commit Hash: a6d415f24a7d182ebca3d189c457d9044537028f
Git Branch: HEAD
UTC Build Time: 2025-08-03 10:01:12
GoVersion: go1.23.11
Race Enabled: false
Check Table Before Drop: false
Store: tikv
Kernel Type: Classic |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

Start by running the provided SQL reproducer with the read_from_storage hints against TiFlash and TiKV, focusing on the NOT IN subquery containing NULL. Trace the differing results through the TiFlash and TiKV execution paths; done means both queries return the expected empty set and a regression test covers this case.