反馈一个bug，hash join的情况下，limit分页无法正确分

[TiDBer_jYQINSnf]

https://github.com/pingcap/tidb/issues/40186

官方的回复说是这样正确的，你们作为使用方接受吗？
我认为，别管order by的内容是不是重复的，用limit 0, 10 limit 10, 10 limit 20,10 都应该正确的翻完整个结果集。

我开发过分库分表中间件，涉及到limit的实现是这样的：
limit 0, 10，会从分库中各收集10条，排序后挑出前10条。
如果limit 10, 10, 会从各个分库中收集20条，排序后挑出第二个10条。
这个实现到最后相当于拿了分库中所有数据，是比较笨的方法，但是不出错，结果集稳定。
目前hash join的实现，如果order by是重复的，就随机挑出来一部分出来，那limit 0,10 limit 10,10 就没什么意义了，翻页根本翻不完整整个结果集。对hash join来说，limit 0,10还是limit 10,10 的意思就成了随机挑10条符合条件的了，这样的实现我认为不对。

如果hash join搞不定这种情况，那执行计划就别选hash join啊，结果还默认选了hash join，还得用hint强制指定其他join。这有点不科学。

[Billmay表妹 (表妹)]

我问问产研大佬们~

[Billmay表妹 (表妹)]

来自产研老师的回复：

结果没问题。如果您想要稳定的结果，您应该按列顺序指定 id 或 table1_col3。

[TiDBer_jYQINSnf]

看到了回复，但是我不认可这个回复。
我理解 limit 的作用是把一个结果集按分页一点点吐出来，现在的结果是limit无法把整个结果吐出来，会把一些结果吐出来2遍，一些结果就永远出不来。

他们说的 “create_time有重复” 可以用来解释这个现象，但是不能证明这样就是对的。

排序算法有很多种，有稳定的有不稳定的，我认为这个地方应该用稳定排序算法，limit应该像一个小的滑动窗口一样，从上往下把结果集展示出来，而不是每次去看的时候，结果集就变了，导致无法用limit拿到全部结果。有各种方式可以规避这个bug，但是limit的行为我认为不应该是这样的。

不知道你们内部有没有技术委员会之类的，研究研究SQL标准，看看这种行为是不是真正符合要求。

如果说这就是明确答案，那我就得出一个结论：
TiDB 处理 Order by 有重复数据的结果时，用 limit 分页无法拿到结果集中的全部条目。

这样一个帽子，TiDB 愿意戴吗？

[Billmay表妹 (表妹)]

你可以在该 issue 的链接里继续和产研老师们讨论该问题~

[TiDBer_jYQINSnf]

英语不好，没法用英语准确表达我的意思
这个问题我就让业务方先用 order by create_time desc ,o.id asc 暂时规避下了

[Billmay表妹 (表妹)]

https://docs.pingcap.com/zh/tidb/dev/dev-guide-unstable-result-set#order-by 这里有说明

[TiDBer_jYQINSnf]

结果集的不稳定，在查询所有数据的时候，看似无关紧要，谁先谁后不是那么重要。但是用上 limit 做分页的时候，会发现怎么也无法获取到表中所有数据。这就变成了 bug。你会告诉用户，所有用到 limit 的sql都需要额外加一列唯一列，保证结果的正确性吗？

分布式数据库可能没有一个先例可以照着去做，大家的使用习惯也是从单机数据库转移到分布式数据库，一些因为分布式数据库实现复杂性而导致的用户习惯的变化是不是需要慎重考虑？而不是简单一句：我做不到，那样做太复杂了，那样做太费劲了。

即使所有人能轻松理解为什么结果是这样的，那也不一定意味着这样的结果就是对的。

TiDB 做的很多工作可能就是史无前例的，一个小小的特性可能未来就是事实上的分布式数据库标准，希望做决定时能慎重一些。

[人如其名]

站在使用者的角度，我赞同你的说法，也想让limit稳定。
但是好像看mysql语法limit并不是用来做分页的，也不能保证稳定性（ https://dev.mysql.com/doc/refman/8.0/en/limit-optimization.html ），只是在大多数场景下是稳定的，可能就用惯了把limit当做分页（不稳定的场景比如执行计划的改变，表做了optimize等）。
用row_number()over()来做分页更严谨一些，但是效率相对较差，顺便也说下tidb的分页和传统数据库的分页在生成序号的时候还是有一点不太一样。

mysql> select o_orderkey,row_number()over() as nbr from orders order by o_orderkey limit 10;
+------------+----------+
| o_orderkey | nbr      |
+------------+----------+
|          1 |  4436580 |
|          2 | 13517437 |
|          3 |  5491147 |
|          4 |  9463180 |
|          5 |  2440301 |
|          6 |  9888487 |
|          7 |  4486562 |
|         32 |  8920212 |
|         33 |  5572217 |
|         34 |   564184 |
+------------+----------+
10 rows in set (4.24 sec)
mysql> explain analyze select o_orderkey,row_number()over() as nbr from orders order by o_orderkey limit 10;
+----------------------------+-------------+----------+-----------+----------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+---------+------+
| id                         | estRows     | actRows  | task      | access object                                | execution info                                                                                                                                                                                                                                                                                                       | operator info                                                          | memory  | disk |
+----------------------------+-------------+----------+-----------+----------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+---------+------+
| TopN_12                    | 10.00       | 10       | root      |                                              | time:6.2s, loops:2                                                                                                                                                                                                                                                                                                   | tpch.orders.o_orderkey, offset:0, count:10                             | 26.7 KB | N/A  |
| └─Window_17                | 15000000.00 | 15000000 | root      |                                              | time:5.6s, loops:14661                                                                                                                                                                                                                                                                                               | row_number()->Column#11 over(rows between current row and current row) | N/A     | N/A  |
|   └─IndexReader_21         | 15000000.00 | 15000000 | root      |                                              | time:2.19s, loops:14661, cop_task: {num: 15, max: 4.56s, min: 1.95s, avg: 3.71s, p95: 4.56s, max_proc_keys: 1435897, p95_proc_keys: 1435897, tot_proc: 38.9s, tot_wait: 336ms, rpc_num: 15, rpc_time: 55.6s, copr_cache: disabled, distsql_concurrency: 15}                                                          | index:IndexFullScan_20                                                 | 95.8 MB | N/A  |
|     └─IndexFullScan_20     | 15000000.00 | 15000000 | cop[tikv] | table:orders, index:orders_idx1(O_ORDERDATE) | tikv_task:{proc max:3.22s, min:918ms, avg: 2.05s, p80:2.51s, p95:3.22s, iters:14720, tasks:15}, scan_detail: {total_process_keys: 15000000, total_process_keys_size: 690000000, total_keys: 15000020, rocksdb: {key_skipped_count: 15000005, block: {cache_hit_count: 6117, read_count: 3103, read_byte: 115.9 MB}}} | keep order:false                                                       | N/A     | N/A  |
+----------------------------+-------------+----------+-----------+----------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+---------+------+
4 rows in set (6.21 sec)

这个在传统数据库中不会这样子的，序号是往下排列的，如果tidb需要达到相同效果那么需要如下改写：

select o_orderkey,row_number()over() as nbr from (select o_orderkey from orders order by o_orderkey limit 10) a;

mysql> select o_orderkey,row_number()over() as nbr from (select o_orderkey from orders order by o_orderkey limit 10) a;
+------------+------+
| o_orderkey | nbr  |
+------------+------+
|          1 |    1 |
|          2 |    2 |
|          3 |    3 |
|          4 |    4 |
|          5 |    5 |
|          6 |    6 |
|          7 |    7 |
|         32 |    8 |
|         33 |    9 |
|         34 |   10 |
+------------+------+
10 rows in set (0.02 sec)

mysql> explain analyze select o_orderkey,row_number()over() as nbr from (select o_orderkey from orders order by o_orderkey limit 10) a;
+------------------------------+---------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+-----------+------+
| id                           | estRows | actRows | task      | access object | execution info                                                                                                                                                                   | operator info                                                          | memory    | disk |
+------------------------------+---------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+-----------+------+
| Window_11                    | 10.00   | 10      | root      |               | time:5.73ms, loops:2                                                                                                                                                             | row_number()->Column#11 over(rows between current row and current row) | N/A       | N/A  |
| └─Limit_15                   | 10.00   | 10      | root      |               | time:5.64ms, loops:2                                                                                                                                                             | offset:0, count:10                                                     | N/A       | N/A  |
|   └─TableReader_28           | 10.00   | 10      | root      |               | time:5.6ms, loops:1, cop_task: {num: 1, max: 4.93ms, proc_keys: 10, rpc_num: 1, rpc_time: 4.67ms, copr_cache: disabled, distsql_concurrency: 1}                                  | data:Limit_27                                                          | 297 Bytes | N/A  |
|     └─Limit_27               | 10.00   | 10      | cop[tikv] |               | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 10, total_process_keys_size: 270, total_keys: 11, rocksdb: {key_skipped_count: 10, block: {cache_hit_count: 6}}} | offset:0, count:10                                                     | N/A       | N/A  |
|       └─TableFullScan_26     | 10.00   | 10      | cop[tikv] | table:orders  | tikv_task:{time:0s, loops:1}                                                                                                                                                     | keep order:true                                                        | N/A       | N/A  |
+------------------------------+---------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------+-----------+------+
5 rows in set (0.02 sec)

[TiDBer_jYQINSnf]

limit 内部很多人用来做分页，估计大家也习惯用这个做分页。
有些框架自动生成sql，估计在这种情况下会有bug。尤其是那种列表，点表头自动排序的，下面还有分页，这种可能就翻着翻着就有些数据找不到了。
估计从 mysql 迁过来的业务会踩一遍坑。

[TiDBer_jYQINSnf]

类似这种列表，点下标题就按照标题排序，然后下面还有分页。这种情况下后面的sql会增加一列按唯一列排序吗？

[Hacker_xUwtuKxa]

插一句，但不影响 [Hacker_hnSEntrA]的问题。
对于[人如其名]的例子，可以考虑在开窗函数中也加入排序：
select name2,row_number() over(order by name2) from t2 order by name2 limit 10,10;
输出结果如下：
±---------------------±----------------------------------+
| name | row_number() over(order by name2) |
±---------------------±----------------------------------+
| b2305843009213694473 | 11 |
| b2305843009213694474 | 12 |
| b2305843009213694475 | 13 |
| b2305843009213694476 | 14 |
| b2305843009213694477 | 15 |
| b2305843009213694478 | 16 |
| b2305843009213694479 | 17 |
| b2305843009213694480 | 18 |
| b2305843009213694481 | 19 |
| b2305843009213694482 | 20 |
±---------------------±----------------------------------+

[人如其名]

好的，谢谢。不过这个行为确实和其它数据库（比如8.0的mysql）不太一样，是否可以默认变成一致行为呢？