Wrong result of topn for generated column

[guo-shaoge]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table t1;
CREATE TABLE `t1` (
  `c1` varchar(100) DEFAULT NULL,
  `c2` varchar(100) GENERATED ALWAYS AS (lower(`c1`)) VIRTUAL
);
alter table t1 set tiflash replica 1;
insert into t1(c1) values('a'), ('e'), ('b'), ('c'), ('d'), ('e'), ('x'), ('y'), ('a'), ('b');

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

MySQL [test]> select * from t1 order by c2 limit 2;
+------+------+
| c1   | c2   |
+------+------+
| a    | a    |
| a    | a    |
+------+------+
2 rows in set (0.001 sec)

3. What did you see instead (Required)

MySQL [test]> set tidb_isolation_read_engines = 'tikv';
Query OK, 0 rows affected (0.000 sec)

MySQL [test]> select * from t1 order by c2 limit 2;
+------+------+
| c1   | c2   |
+------+------+
| a    | a    |
| e    | e    |
+------+------+
2 rows in set (0.001 sec)

MySQL [test]> set tidb_isolation_read_engines = 'tiflash';
Query OK, 0 rows affected (0.000 sec)

MySQL [test]> select * from t1 order by c2 limit 2;
+------+------+
| c1   | c2   |
+------+------+
| a    | a    |
| e    | e    |
+------+------+
2 rows in set (0.010 sec)

4. What is your TiDB version? (Required)

MySQL [test]> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                               |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.5.0
Edition: Community
Git Commit Hash: 77776e17159e9c6faf6aeedf3085a7a2524d3205
Git Branch: heads/refs/tags/v6.5.0
UTC Build Time: 2022-12-05 13:50:22
GoVersion: go1.19.3
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

[guo-shaoge]

MySQL [test]> explain select * from t1 order by c2 limit 2;
+----------------------------+---------+-------------------+---------------+--------------------------------+
| id                         | estRows | task              | access object | operator info                  |
+----------------------------+---------+-------------------+---------------+--------------------------------+
| TopN_7                     | 2.00    | root              |               | test.t1.c2, offset:0, count:2  |
| └─TableReader_16           | 2.00    | root              |               | data:TopN_15                   |
|   └─TopN_15                | 2.00    | batchCop[tiflash] |               | test.t1.c2, offset:0, count:2  |
|     └─TableFullScan_14     | 10.00   | batchCop[tiflash] | table:t1      | keep order:false, stats:pseudo |
+----------------------------+---------+-------------------+---------------+--------------------------------+
4 rows in set (0.001 sec)

TopN should not push down to tiflash/tikv, because c2 is virtual generated column.

[Dousir9]

/assign