tidb 执行查询 LIMIT 不能返回正确的结果

[TiDBer_cJmm6yqg]

【TiDB 使用环境】 测试
【TiDB 版本】v8.5.1
【遇到的问题：问题现象及影响】

create table t1
(
id bigint not null comment ‘Id’
primary key,
name varchar(180) not null comment ‘名称’
)
comment ‘t1’ charset = utf8;

INSERT INTO t1 (id, name) VALUES (1, ‘n1’);
INSERT INTO t1 (id, name) VALUES (2, ‘n2’);

create table t2
(
id bigint not null comment ‘Id’
primary key,
account varchar(32) not null comment ‘账号’,
gender_id bigint not null comment ‘性别Id’
)
comment ‘t2’;

INSERT INTO t2 (id, account, gender_id) VALUES (1, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (2, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (3, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (4, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (5, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (6, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (7, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (8, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (9, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (10, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (11, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (12, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (13, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (14, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (15, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (16, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (17, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (18, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (19, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (20, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (21, ‘a’, 2);
INSERT INTO t2 (id, account, gender_id) VALUES (22, ‘a’, 2);
INSERT INTO t2 (id, account, gender_id) VALUES (23, ‘a’, 2);
INSERT INTO t2 (id, account, gender_id) VALUES (24, ‘a’, 2);
INSERT INTO t2 (id, account, gender_id) VALUES (25, ‘a’, 2);
INSERT INTO t2 (id, account, gender_id) VALUES (26, ‘a’, 2);
INSERT INTO t2 (id, account, gender_id) VALUES (27, ‘a’, 2);
INSERT INTO t2 (id, account, gender_id) VALUES (28, ‘a’, 2);
INSERT INTO t2 (id, account, gender_id) VALUES (29, ‘a’, 2);
INSERT INTO t2 (id, account, gender_id) VALUES (30, ‘a’, 2);
INSERT INTO t2 (id, account, gender_id) VALUES (31, ‘a’, 2);
INSERT INTO t2 (id, account, gender_id) VALUES (32, ‘a’, 2);
INSERT INTO t2 (id, account, gender_id) VALUES (33, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (34, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (35, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (36, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (37, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (38, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (39, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (40, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (41, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (42, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (43, ‘a’, 1);
INSERT INTO t2 (id, account, gender_id) VALUES (44, ‘a’, 1);

explain analyze
SELECT *
FROM (SELECT (SELECT name
FROM t1
WHERE u.gender_id = id) AS GenderName,
u.id AS SugarNav_Id,
u.gender_id AS SugarNav_GenderId,
u.account AS Name,
u.id AS Id
FROM t2 u

  ORDER BY `u`.`id` asc) MergeTable

LIMIT 20, 20

同样的内容在 7.5.0 下正常，在8.5.1下 我应该调整什么参数

[有猫万事足]

描述看不明白到底问题出在哪里。
建议用下面这个方法收集一下sql执行的现场信息。方便复现问题。

https://docs.pingcap.com/zh/tidb/stable/sql-plan-replayer/#使用-plan-replayer-保存和恢复集群现场信息

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

这看着是个bug，子查询结合limit的bug，改成关联就没问题了，SELECT
t1.NAME AS GenderName,
t2.id AS SugarNav_Id,
t2.gender_id AS SugarNav_GenderId,
t2.account AS NAME,
t2.id AS Id
FROM t2
LEFT JOIN t1 ON t2.gender_id = t1.id
ORDER BY t2.id ASC
LIMIT 20, 20;

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

如果类似sql很多的话，只能先禁止topN下推临时解决下吧，但是对性能会有影响：
INSERT INTO mysql.opt_rule_blacklist VALUES(‘topn_push_down’);
ADMIN reload opt_rule_blacklist;

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

部署了8.5.2验证已经修复，可以的话，也可以升级集群到8.5.2

[Billmay表妹 (表妹)]

相关的 bug：

planner: remove duplicate offset when push down pure limit down across outer join by winoros · Pull

在 v8.5.2 的时候已经修复~