ORDER BY + LIMIT Pagination Query Results Are Duplicated and Do Not Meet Expectations

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: order by + limit分页查询结果数据重复不符合预期

| username: allens

【TiDB Usage Environment】Production Environment / Testing / Poc
【TiDB Version】5.7.25-TiDB-v7.5.0, 8.0.11-TiDB-v7.5.0
【Reproduction Path】Operations performed that led to the issue
Created a test table and inserted data
CREATE TABLE test(idbigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Auto-increment primary key',create_timedatetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT 'Record creation timestamp',update_timedatetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT 'Record update timestamp',task_idbigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'Task ID',user_idbigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'C-end user ID',statustinyint(2) unsigned NOT NULL DEFAULT '0' COMMENT 'Task status 0: unknown, 1: pending push, 2: push completed, 3: push failed, 4: canceled',reason varchar(64) NOT NULL DEFAULT '' COMMENT 'Failure reason', PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */, KEY idx_task (task_id), KEY idx_status (status), UNIQUE KEY uk_task_user (task_id, user_id`)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin AUTO_INCREMENT = 635227 COMMENT = ‘Send task details table’

INSERT INTO imt_coupon_test_db.test (id,create_time,update_time,task_id,user_id,status,reason) VALUES
(593431,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:02.950’,71,1,2,‘’),
(593432,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.030’,71,2452000,2,‘’),
(593433,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.165’,71,3178014,2,‘’),
(593434,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.274’,71,488021,2,‘’),
(593435,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.313’,71,2628001,2,‘’),
(593436,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.356’,71,2452002,2,‘’),
(593437,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.402’,71,2452003,2,‘’),
(593438,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.444’,71,2452004,2,‘’),
(593439,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.483’,71,2452005,2,‘’),
(593440,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.526’,71,2452006,2,‘’);
INSERT INTO imt_coupon_test_db.test (id,create_time,update_time,task_id,user_id,status,reason) VALUES
(593441,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.565’,71,3178015,2,‘’),
(593442,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.607’,71,2452001,2,‘’),
(593443,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.647’,71,3178016,2,‘’),
(593444,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.741’,71,3178018,2,‘’),
(593445,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.782’,71,3238000,2,‘’),
(593446,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.823’,71,3238001,2,‘’),
(593447,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.867’,71,3240003,2,‘’),
(593448,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.923’,71,3240004,2,‘’),
(593449,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.968’,71,108026,2,‘’),
(593450,‘2024-06-04 15:10:20.288’,‘2024-06-04 15:15:03.990’,71,2,3,‘Insufficient stock’);
【Encountered Issue: Problem Phenomenon and Impact】
Duplicate data appears in pagination queries,
【Resource Configuration】Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
【Attachments: Screenshots/Logs/Monitoring】
GitHub issue: https://github.com/pingcap/tidb/issues/53818

2
| username: 小龙虾爱大龙虾 | Original post link

It looks like there is no problem, and the returned data conforms to SQL logic. Since the create_time is the same and TiDB’s underlying operator implementation is parallel, it results in duplicates. MySQL does not have duplicates because its underlying implementation is different.
I suggest modifying your SQL to SELECT * FROM test WHERE (task_id = 71) ORDER BY create_time DESC, id LIMIT 0,2; this way, there will be no duplicates.

3
| username: shuyu_zhihui | Original post link

Xiaolongxia’s reply is good. You can add a unique key to the order by clause to sort it.

4
| username: allens | Original post link

We have also tried this and it indeed achieves the desired effect. The main question is why pagination results in duplicates. The underlying parallel processing should handle pagination correctly across different nodes. It seems to be an issue with operator pushdown, where the query results are returned and aggregated. Additionally, using ORDER BY task_id returns the expected results correctly, which suggests that different types might indeed have different processing logic.

5
| username: allens | Original post link

Understood, we also tried this, but it feels like there shouldn’t be any duplication. Additionally, if the distributed ID is not sequentially incremented, wouldn’t there also be issues?

6
| username: Defined2014 | Original post link

This is not a duplicate; the results of each SQL execution are independent.

7
| username: FutureDB | Original post link

This is because your create_time has duplicates, so sorting only by create_time will result in instability in a distributed database. In your example, assuming create_time is sorted in descending order, the first four entries have the same create_time but different IDs. You can try querying these four entries without pagination, just sorting by create_time in descending order. If you do this multiple times, you might find that the results are not exactly the same each time. This behavior is expected in a distributed database.

8
| username: FutureDB | Original post link

This phenomenon is most easily reproduced in large tables, especially when data with the same create_time is distributed across multiple different TiKV nodes.

9
| username: WinterLiu | Original post link

That makes a lot of sense.

10
| username: 帅的掉渣 | Original post link

The SQL is distributed to TiKV for execution, and then the result set is reordered in the root. The return order is different, and sorting based on duplicate values does indeed have this issue.

11
| username: 帅的掉渣 | Original post link

For a unique key, this issue definitely does not exist.

12
| username: 数据库真NB | Original post link

The inconsistency in data results is most likely not a database issue. The generation time and limit cannot independently distinguish data results, which may cause unstable sorting. If this inconsistency is confirmed, then it’s a bug and should be reported to the TiDB development team.

13
| username: yiduoyunQ | Original post link
  1. Try again without the create_time being the same.
  2. The current create_time is the same, so any order returned conforms to SQL semantics.
14
| username: 小龙虾爱大龙虾 | Original post link

You need to understand the execution method of the underlying operators in a distributed database. If an SQL query does not include an ORDER BY clause, the result order may vary with each execution. Even if you add an ORDER BY clause, if the ORDER BY field has duplicate values, the order of the result set may still vary with each query. Your use of LIMIT 0,2; LIMIT 2,2; involves multiple queries.

15
| username: zhaokede | Original post link

The sorting criteria must be unique.

16
| username: 林夕一指 | Original post link

Directly use the official pagination query :sunglasses:

17
| username: 小于同学 | Original post link

The results of two queries sorted by a duplicate field may be different.

18
| username: allens | Original post link

Thank you all for your replies, I have benefited a lot.
Currently, the solution has been resolved by using “order by create_time, id desc” and it meets expectations.
After checking the TiKV code, this comment also seems to explain this scenario.

image
image1380×794 199 KB

Additionally, I have verified on OceanBase, StarRocks, and MySQL NDB, and the same data duplication issue occurs during pagination.
However, from a business perspective, especially when migrating from traditional single-instance scenarios, there is a certain acceptance and transformation cost.

19
| username: Kongdom | Original post link

:thinking: From this perspective, when writing SQL, it’s still necessary to follow development standards and sort by unique keys during pagination queries.