Unexpected Results by the hint USE_INDEX in TiFlash

[bajinsheng]

Bug Report

The query returns a different result under the hint USE_INDEX, which should not affect the result.

1. Minimal reproduce step (Required)

CREATE TABLE t0(c0 CHAR, PRIMARY KEY(c0));
INSERT INTO t0(c0) VALUES ('a');
ALTER TABLE t0 SET TIFLASH REPLICA 1;
set @@tidb_enforce_mpp=1;
SELECT SLEEP(5);

SELECT t0.c0 FROM t0 WHERE LN(t0.c0); -- {a}
SELECT /*+ USE_INDEX(t0, i0, PRIMARY)*/t0.c0 FROM t0 WHERE LN(t0.c0); -- empty

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

Two queries return the same result.

3. What did you see instead (Required)

4. What is your TiDB version? (Required)

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

[aytrack]

The result of LN('a') from tikv is different from tiflash.

mysql> set @@tidb_enforce_mpp=1;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT t0.c0 FROM t0 WHERE LN(t0.c0);
+----+
| c0 |
+----+
| a  |
+----+
1 row in set (0.02 sec)

mysql> EXPLAIN SELECT t0.c0 FROM t0 WHERE LN(t0.c0);
+----------------------------+---------+--------------+---------------+-----------------------------------------------------------------------------+
| id                         | estRows | task         | access object | operator info                                                               |
+----------------------------+---------+--------------+---------------+-----------------------------------------------------------------------------+
| TableReader_16             | 0.80    | root         |               | MppVersion: 2, data:ExchangeSender_15                                       |
| └─ExchangeSender_15        | 0.80    | mpp[tiflash] |               | ExchangeType: PassThrough                                                   |
|   └─Selection_14           | 0.80    | mpp[tiflash] |               | ln(cast(test.t0.c0, double BINARY))                                         |
|     └─TableFullScan_13     | 1.00    | mpp[tiflash] | table:t0      | pushed down filter:empty, keep order:false, stats:partial[c0:unInitialized] |
+----------------------------+---------+--------------+---------------+-----------------------------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select LN(t0.c0) from t0;
+-----------+
| LN(t0.c0) |
+-----------+
|         0 |
+-----------+
1 row in set (0.02 sec)

mysql> set @@tidb_enforce_mpp=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT t0.c0 FROM t0 WHERE LN(t0.c0);
Empty set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT t0.c0 FROM t0 WHERE LN(t0.c0);
+-------------------------+---------+-----------+---------------+---------------------------------------------------+
| id                      | estRows | task      | access object | operator info                                     |
+-------------------------+---------+-----------+---------------+---------------------------------------------------+
| TableReader_8           | 0.80    | root      |               | data:Selection_7                                  |
| └─Selection_7           | 0.80    | cop[tikv] |               | ln(cast(test.t0.c0, double BINARY))               |
|   └─TableFullScan_6     | 1.00    | cop[tikv] | table:t0      | keep order:false, stats:partial[c0:unInitialized] |
+-------------------------+---------+-----------+---------------+---------------------------------------------------+
3 rows in set (0.00 sec)

mysql> select LN(t0.c0) from t0;
+-----------+
| LN(t0.c0) |
+-----------+
|      NULL |
+-----------+
1 row in set, 2 warnings (0.00 sec)

[bajinsheng]

Is it a duplicate of #46601?

[zanmato1984]

Is it a duplicate of #46601?

Yes. So I'm closing this issue. Feel free to reopen if you observe any further issue.