Tiflash return inconsistent result in multiple times (single node)

[Dylan0222]

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

[Dylan0222]

/label affects-8.4
/label affects-8.5

[yibin87]

Maybe related to pingcap/tidb#57655

[Dylan0222]

Maybe related to pingcap/tidb#57655

It seems like a different problem since this issue shows that tiflash has an unstable behaviour, rather than tikv.