Return out of order results for query `cluster_slow_query` with order by

[Defined2014]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

tiup playground nightly --db 2 --tiflash 0
mysql --comments --host 127.0.0.1 --port 4000 -uroot --local-infile=1 -e "use test;create table t(a int); insert into t values (1);"
mysql --comments --host 127.0.0.1 --port 4000 -uroot --local-infile=1 -e "use test;select sleep(1.1) from t;"
mysql --comments --host 127.0.0.1 --port 4001 -uroot --local-infile=1 -e "use test;select sleep(1.1) from t;"
mysql --comments --host 127.0.0.1 --port 4001 -uroot --local-infile=1 -e "use test;select sleep(1.1) from t;"
mysql --comments --host 127.0.0.1 --port 4000 -uroot --local-infile=1 -e "use test;select sleep(1.1) from t;"
mysql --comments --host 127.0.0.1 --port 4001 -uroot --local-infile=1 -e "select time from information_schema.cluster_slow_query order by time;"

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

return values are ordered.

3. What did you see instead (Required)

sometimes the results are out of order.

4. What is your TiDB version? (Required)

mysql> select tidb_version();
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v8.0.0-alpha-435-gb55c855142
Edition: Community
Git Commit Hash: b55c8551421852bae33af671fa1bfc3f605bb494
Git Branch: HEAD
UTC Build Time: 2024-03-08 07:11:55
GoVersion: go1.21.6
Race Enabled: false
Check Table Before Drop: false
Store: tikv |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[Defined2014]

The plan is show below, I think it should add a Sort executor when reader CLUSTER_SLOW_QUERY table.

mysql> explain select time from information_schema.cluster_slow_query order by time;
+------------------------+----------+-----------+--------------------------+-------------------------------+
| id                     | estRows  | task      | access object            | operator info                 |
+------------------------+----------+-----------+--------------------------+-------------------------------+
| TableReader_11         | 10000.00 | root      |                          | data:TableFullScan_10         |
| └─TableFullScan_10     | 10000.00 | cop[tidb] | table:CLUSTER_SLOW_QUERY | keep order:true, stats:pseudo |
+------------------------+----------+-----------+--------------------------+-------------------------------+
2 rows in set (0.00 sec)

[Defined2014]

A quick fix just like codes shown below. But after such codes, #20236 should reopen. Maybe we could do merge sort between all TiDB nodes.

diff --git a/pkg/planner/core/find_best_task.go b/pkg/planner/core/find_best_task.go
index 62f179491a..c2b17f4d1c 100644
--- a/pkg/planner/core/find_best_task.go
+++ b/pkg/planner/core/find_best_task.go
@@ -2490,6 +2490,10 @@ func (ds *DataSource) convertToTableScan(prop *property.PhysicalProperty, candid
        task = copTask
        if candidate.isMatchProp {
                copTask.keepOrder = true
+               if ds.table.Type().IsClusterTable() {
+                       // TableScan with cluster table can't keep order.
+                       return invalidTask, nil
+               }
                if ds.tableInfo.GetPartitionInfo() != nil {
                        // TableScan on partition table on TiFlash can't keep order.
                        if ts.StoreType == kv.TiFlash {

A better plan shoud be like

TopN
└─TableReader
  └─Limit
    └─TableFullScan(keepOrder = true)