Oceanbase之mysql租户模式order by与limit

最近和同事聊到oceanbase集群数据库mysql租户模式下SQL语句中order by与limit同时使用，分页查询会出现返回重复数据的情况。这个问题在mysql数据库里也遇到过，当order by排序列出现重复值的时候，每一次查询返回的结果集里，这些重复值所在行会出现随机排序的情况。oceanbase集群数据库mysql模式完全兼容mysql数据库，也出现了这个情况。

CREATE  TABLE IF NOT EXISTS  tb04 (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    yw_id BIGINT NOT NULL,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    content VARCHAR(50) NOT NULL,
 PRIMARY KEY (id),
 INDEX i (yw_id, create_time,content)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into tb04 (yw_id,content) 
	values (101,"书屋"),
	(102,"庭院"),
	(103,"教室"),
	(104,"公园"),
	(105,"小河"),
	(105,"商场"),
	(105,"码头"),
	(105,"河岸"),
	(105,"卧室"),
	(115,"阳台"),
	(111,"山顶"),
	(112,"车站"),
	(113,"银行"),
	(114,"社区"),
	(115,"乡村"),
	(116,"医院"),
	(117,"学校"),
	(118,"药店"),
	(119,"超市");

mysql> select * from tb04 order by create_time limit 0,10;
+----+-------+---------------------+---------+
| id | yw_id | create_time         | content |
+----+-------+---------------------+---------+
|  1 |   101 | 2021-08-04 18:36:29 | 书屋    |
|  2 |   102 | 2021-08-04 18:36:29 | 庭院    |
|  3 |   103 | 2021-08-04 18:36:29 | 教室    |
|  4 |   104 | 2021-08-04 18:36:29 | 公园    |
|  5 |   105 | 2021-08-04 18:36:29 | 小河    |
|  6 |   105 | 2021-08-04 18:36:29 | 商场    |
|  7 |   105 | 2021-08-04 18:36:29 | 码头    |
|  8 |   105 | 2021-08-04 18:36:29 | 河岸    |
|  9 |   105 | 2021-08-04 18:36:29 | 卧室    |
| 10 |   115 | 2021-08-04 18:36:29 | 阳台    |
+----+-------+---------------------+---------+
10 rows in set (0.00 sec)

mysql> select * from tb04 order by create_time limit 10,10;
+----+-------+---------------------+---------+
| id | yw_id | create_time         | content |
+----+-------+---------------------+---------+
| 10 |   115 | 2021-08-04 18:36:29 | 阳台    |
|  9 |   105 | 2021-08-04 18:36:29 | 卧室    |
|  8 |   105 | 2021-08-04 18:36:29 | 河岸    |
|  7 |   105 | 2021-08-04 18:36:29 | 码头    |
|  6 |   105 | 2021-08-04 18:36:29 | 商场    |
|  5 |   105 | 2021-08-04 18:36:29 | 小河    |
|  4 |   104 | 2021-08-04 18:36:29 | 公园    |
|  3 |   103 | 2021-08-04 18:36:29 | 教室    |
|  1 |   101 | 2021-08-04 18:36:29 | 书屋    |
| 20 |   101 | 2021-08-04 18:44:19 | 书屋    |
+----+-------+---------------------+---------+
10 rows in set (0.00 sec)

mysql> select * from tb04 order by create_time limit 20,10;
+----+-------+---------------------+---------+
| id | yw_id | create_time         | content |
+----+-------+---------------------+---------+
| 30 |   111 | 2021-08-04 18:44:19 | 山顶    |
| 29 |   115 | 2021-08-04 18:44:19 | 阳台    |
| 28 |   105 | 2021-08-04 18:44:19 | 卧室    |
| 27 |   105 | 2021-08-04 18:44:19 | 河岸    |
| 26 |   105 | 2021-08-04 18:44:19 | 码头    |
| 25 |   105 | 2021-08-04 18:44:19 | 商场    |
| 24 |   105 | 2021-08-04 18:44:19 | 小河    |
| 23 |   104 | 2021-08-04 18:44:19 | 公园    |
| 22 |   103 | 2021-08-04 18:44:19 | 教室    |
| 20 |   101 | 2021-08-04 18:44:19 | 书屋    |
+----+-------+---------------------+---------+
10 rows in set (0.00 sec)