v7.5.7集群子查询排序顺序异常

[Hacker_Yv76YjBL]

【TiDB 使用环境】生产环境
【TiDB 版本】v7.5.7
【遇到的问题：问题现象及影响】
将集群从v5.3.3升级到v7.5.7后，子查询的SQL排序出现不一致。v5.3.3集群可以按照order by正常排序，而v7.5.7集群排序为乱序。
SQL

select id,order_no,sub_order_no,biz_type,biz_code,abs(sub_order_status) as sub_order_status,buyer_id,buyer_name,seller_id,pay_amount,discount_amount,create_time_us from trade_order_seller where id in ( select id from trade_order_seller where seller_id = 2627869160  and biz_type in ( "203" ) and create_time_us >= "2025-8-21" and create_time_us <= "2025-9-23" order by create_time_us desc limit 0, 10);

v5.3.3集群
查询结果可以根据create_time_us倒叙排列

执行计划

+-------------------------------------+---------+-----------+---------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                  | estRows | task      | access object                                                                                                       | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-------------------------------------+---------+-----------+---------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_16                       | 0.04    | root      |                                                                                                                     | dw_trade_order_seller_db.trade_order_seller.id, dw_trade_order_seller_db.trade_order_seller.order_no, dw_trade_order_seller_db.trade_order_seller.sub_order_no, dw_trade_order_seller_db.trade_order_seller.biz_type, dw_trade_order_seller_db.trade_order_seller.biz_code, abs(dw_trade_order_seller_db.trade_order_seller.sub_order_status)->Column#170, dw_trade_order_seller_db.trade_order_seller.buyer_id, dw_trade_order_seller_db.trade_order_seller.buyer_name, dw_trade_order_seller_db.trade_order_seller.seller_id, dw_trade_order_seller_db.trade_order_seller.pay_amount, dw_trade_order_seller_db.trade_order_seller.discount_amount, dw_trade_order_seller_db.trade_order_seller.create_time_us |
| └─IndexJoin_22                      | 0.04    | root      |                                                                                                                     | inner join, inner:TableReader_19, outer key:dw_trade_order_seller_db.trade_order_seller.id, inner key:dw_trade_order_seller_db.trade_order_seller.id, equal cond:eq(dw_trade_order_seller_db.trade_order_seller.id, dw_trade_order_seller_db.trade_order_seller.id)                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   ├─TopN_29(Build)                  | 0.04    | root      |                                                                                                                     | dw_trade_order_seller_db.trade_order_seller.create_time_us:desc, offset:0, count:10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   │ └─IndexReader_37                | 0.04    | root      |                                                                                                                     | index:TopN_36                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|   │   └─TopN_36                     | 0.04    | cop[tikv] |                                                                                                                     | dw_trade_order_seller_db.trade_order_seller.create_time_us:desc, offset:0, count:10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   │     └─Selection_35              | 0.04    | cop[tikv] |                                                                                                                     | eq(dw_trade_order_seller_db.trade_order_seller.biz_type, "203"), ge(dw_trade_order_seller_db.trade_order_seller.create_time_us, 2025-08-21 00:00:00.000000), le(dw_trade_order_seller_db.trade_order_seller.create_time_us, 2025-09-23 00:00:00.000000)                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|   │       └─IndexRangeScan_34       | 436.44  | cop[tikv] | table:trade_order_seller, index:idx_selid_status_type_ctimes(seller_id, sub_order_status, biz_type, create_time_us) | range:[2627869160,2627869160], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|   └─TableReader_19(Probe)           | 1.00    | root      |                                                                                                                     | data:TableRangeScan_18                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     └─TableRangeScan_18             | 1.00    | cop[tikv] | table:trade_order_seller                                                                                            | range: decided by [dw_trade_order_seller_db.trade_order_seller.id], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+-------------------------------------+---------+-----------+---------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

v7.5.7集群
查询结果为乱序

执行计划

+-------------------------------------+---------+-----------+---------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                                  | estRows | task      | access object                                                                                                       | operator info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-------------------------------------+---------+-----------+---------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_16                       | 0.03    | root      |                                                                                                                     | dw_trade_order_seller_db.trade_order_seller.id, dw_trade_order_seller_db.trade_order_seller.order_no, dw_trade_order_seller_db.trade_order_seller.sub_order_no, dw_trade_order_seller_db.trade_order_seller.biz_type, dw_trade_order_seller_db.trade_order_seller.biz_code, abs(dw_trade_order_seller_db.trade_order_seller.sub_order_status)->Column#170, dw_trade_order_seller_db.trade_order_seller.buyer_id, dw_trade_order_seller_db.trade_order_seller.buyer_name, dw_trade_order_seller_db.trade_order_seller.seller_id, dw_trade_order_seller_db.trade_order_seller.pay_amount, dw_trade_order_seller_db.trade_order_seller.discount_amount, dw_trade_order_seller_db.trade_order_seller.create_time_us |
| └─IndexHashJoin_24                  | 0.03    | root      |                                                                                                                     | inner join, inner:TableReader_19, outer key:dw_trade_order_seller_db.trade_order_seller.id, inner key:dw_trade_order_seller_db.trade_order_seller.id, equal cond:eq(dw_trade_order_seller_db.trade_order_seller.id, dw_trade_order_seller_db.trade_order_seller.id)                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   ├─TopN_29(Build)                  | 0.03    | root      |                                                                                                                     | dw_trade_order_seller_db.trade_order_seller.create_time_us:desc, offset:0, count:10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   │ └─IndexReader_39                | 0.03    | root      |                                                                                                                     | index:TopN_38                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
|   │   └─TopN_38                     | 0.03    | cop[tikv] |                                                                                                                     | dw_trade_order_seller_db.trade_order_seller.create_time_us:desc, offset:0, count:10                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
|   │     └─Selection_35              | 0.03    | cop[tikv] |                                                                                                                     | eq(dw_trade_order_seller_db.trade_order_seller.biz_type, "203"), ge(dw_trade_order_seller_db.trade_order_seller.create_time_us, 2025-08-21 00:00:00.000000), le(dw_trade_order_seller_db.trade_order_seller.create_time_us, 2025-09-23 00:00:00.000000)                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
|   │       └─IndexRangeScan_34       | 436.36  | cop[tikv] | table:trade_order_seller, index:idx_selid_status_type_ctimes(seller_id, sub_order_status, biz_type, create_time_us) | range:[2627869160,2627869160], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
|   └─TableReader_19(Probe)           | 0.03    | root      |                                                                                                                     | data:TableRangeScan_18                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
|     └─TableRangeScan_18             | 0.03    | cop[tikv] | table:trade_order_seller                                                                                            | range: decided by [dw_trade_order_seller_db.trade_order_seller.id], keep order:false                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+-------------------------------------+---------+-----------+---------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

v7.5.7集群参数

[WalterWj (王军 - PingCAP)]

你这个需要再最外面加个排序，你这个 sql 语义也没有排序的含义吧。

[Hacker_Yv76YjBL]

能在不改变SQL的情况下，尽量按照v5.3.3版本的查询结果显示嘛

[WalterWj (王军 - PingCAP)]

改 SQL 吧，如果需要排序的话，本身 SQL 就少写了排序。

之前能排序估计也是执行计划的原因。

[Hacker_Yv76YjBL]

改SQL的话，子查询一个排序、外层再套一个排序，岂不是很别扭。

[WalterWj (王军 - PingCAP)]

你子查询排序和 sql 结果排序无关。

[WalterWj (王军 - PingCAP)]

发下两个执行计划，你看看是不是低版本的 order by 或者什么并发为 1

[Hacker_Yv76YjBL]

@WalterWj

发下两个执行计划，你看看是不是低版本的 order by 或者什么并发为 1

贴到内容里了

[WalterWj (王军 - PingCAP)]

新版本 join 是 hash join。
Index Hash Join（哈希连接）是先将外表结果全部读出，构建哈希表，然后用内表去 probe 哈希表。
适合JOIN 字段有索引，且能把一侧数据放入内存场景。在大数据量时通常比嵌套循环连接快，join 的顺序不受外表顺序制约。需要消耗更多的内存来构建哈希表，结果顺序通常不保证。

会快一些
低版本 index join 造成了看似排序的结果。

但是SQL 标准里，除非显式加 ORDER BY，否则结果集的返回顺序是不确定的（无排序预期) 所以结果不排序是预期的。

[我是火炎焱燚 (Ti D Ber 9 Dh P An J6)]

试试通过添加LIMIT最大值或外层显式排序解决，同时避免依赖子查询排序的非标准用法。

[tidb菜鸟一只 (小菜一颗)]

旧版本是凑巧了，正常你的sql保证不了排序的

[kiiz (Ti D Ber Ek Uo9 Soh)]

在主查询中也添加order by子句

[yiduoyunQ]

tidb_remove_orderby_in_subquery