Tiflash return inconsistent result in multiple times (single node)

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS t0;

CREATE TABLE t0 (
    grade DOUBLE
);

INSERT INTO t0 (grade) 
VALUES 
    (1), 
    (129), 
    (2), 
    (130), 
    (3), 
    (131), 
    (25), 
    (153), 
    (1), 
    (129), 
    (2), 
    (130), 
    (3), 
    (131), 
    (25), 
    (153);
    
ALTER TABLE t0 SET TIFLASH REPLICA 1;

SELECT 
    grade,
    score
FROM (
    SELECT /*+ READ_FROM_STORAGE(tikv[t0]) */
        grade,
        ((grade * 3 + 5) % 128) ^ 40 AS score,
        ROW_NUMBER() OVER (ORDER BY ((grade * 3 + 5) % 128) ^ 40) AS rn
    FROM t0
) AS sub
WHERE rn <= 4;

SELECT 
    grade,
    score
FROM (
    SELECT /*+ READ_FROM_STORAGE(tiflash[t0]) */
        grade,
        ((grade * 3 + 5) % 128) ^ 40 AS score,
        ROW_NUMBER() OVER (ORDER BY ((grade * 3 + 5) % 128) ^ 40) AS rn
    FROM t0
) AS sub
WHERE rn <= 4;

INSERT INTO t0 (grade) 
VALUES 
    (1), 
    (129), 
    (2), 
    (130), 
    (3), 
    (131), 
    (25), 
    (153), 
    (1), 
    (129), 
    (2), 
    (130), 
    (3), 
    (131), 
    (25), 
    (153);

SELECT 
    grade,
    score
FROM (
    SELECT /*+ READ_FROM_STORAGE(tikv[t0]) */
        grade,
        ((grade * 3 + 5) % 128) ^ 40 AS score,
        ROW_NUMBER() OVER (ORDER BY ((grade * 3 + 5) % 128) ^ 40) AS rn
    FROM t0
) AS sub
WHERE rn <= 4;

SELECT 
    grade,
    score
FROM (
    SELECT /*+ READ_FROM_STORAGE(tiflash[t0]) */
        grade,
        ((grade * 3 + 5) % 128) ^ 40 AS score,
        ROW_NUMBER() OVER (ORDER BY ((grade * 3 + 5) % 128) ^ 40) AS rn
    FROM t0
) AS sub
WHERE rn <= 4;

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

I have also found that this inconsistency is a probabilistic event. Repeatedly executing the TiFlash query produces different results, while TiKV consistently guarantees fixed results. Moreover, this bug must be triggered strictly by inputting the cases one by one; skipping any SQL query does not necessarily reproduce the issue. I think the issue may lie in the sorting operation inside TiFlash.

3. What did you see instead (Required)

mysql> SELECT
    ->     grade,
    ->     score
    -> FROM (
    ->     SELECT /*+ READ_FROM_STORAGE(tiflash[t0]) */
    ->         grade,
    ->         ((grade * 3 + 5) % 128) ^ 40 AS score,
    ->         ROW_NUMBER() OVER (ORDER BY ((grade * 3 + 5) % 128) ^ 40) AS rn
    ->     FROM t0
    -> ) AS sub
    -> WHERE rn <= 4;
+-------+-------+
| grade | score |
+-------+-------+
|     1 |    32 |
|     1 |    32 |
|   129 |    32 |
|   129 |    32 |
+-------+-------+
4 rows in set (0.04 sec)

mysql> SELECT
    ->     grade,
    ->     score
    -> FROM (
    ->     SELECT /*+ READ_FROM_STORAGE(tiflash[t0]) */
    ->         grade,
    ->         ((grade * 3 + 5) % 128) ^ 40 AS score,
    ->         ROW_NUMBER() OVER (ORDER BY ((grade * 3 + 5) % 128) ^ 40) AS rn
    ->     FROM t0
    -> ) AS sub
    -> WHERE rn <= 4;
+-------+-------+
| grade | score |
+-------+-------+
|     1 |    32 |
|     1 |    32 |
|     1 |    32 |
|   129 |    32 |
+-------+-------+
4 rows in set (0.05 sec)
mysql> SELECT
    ->     grade,
    ->     score
    -> FROM (
    ->     SELECT /*+ READ_FROM_STORAGE(tikv[t0]) */
    ->         grade,
    ->         ((grade * 3 + 5) % 128) ^ 40 AS score,
    ->         ROW_NUMBER() OVER (ORDER BY ((grade * 3 + 5) % 128) ^ 40) AS rn
    ->     FROM t0
    -> ) AS sub
    -> WHERE rn <= 4;
+-------+-------+
| grade | score |
+-------+-------+
|     1 |    32 |
|     1 |    32 |
|   129 |    32 |
|     1 |    32 |
+-------+-------+
4 rows in set (0.04 sec)

4. What is your TiDB version? (Required)

TiDB v8.4.0

Start by reproducing the ordered SQL sequence on TiDB v8.4.0, comparing the TiFlash and TiKV results across repeated runs. Then trace the TiFlash sorting and ROW_NUMBER execution implicated by the report; done means the TiFlash query returns deterministic results consistent with the expected ordering.