Bug 反馈
清晰准确地描述您发现的问题，提供任何可能复现问题的步骤有助于研发同学及时处理问题
【 TiDB 版本】
【 Bug 的影响】
子查询和limit联合使用，无法返回正确数据
【可能的问题复现步骤】
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);
SELECT *
FROM (SELECT (SELECT NAME
FROM t1
WHERE u.gender_id = t1.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
;
SELECT *
FROM (SELECT (SELECT NAME
FROM t1
WHERE u.gender_id = t1.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;
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;
【看到的非预期行为】
SELECT *
FROM (SELECT (SELECT NAME
FROM t1
WHERE u.gender_id = t1.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
;
查询返回44行数据，加上LIMIT 20, 20后返回0行，使用关联sql也能正常返回20行数据
【期望看到的行为】
使用子查询加limit，正常返回20行数据
【相关组件及具体版本】
tidb v8.5.1
【其他背景信息或者截图】
如集群拓扑，系统和内核版本，应用 app 信息等；如果问题跟 SQL 有关，请提供 SQL 语句和相关表的 Schema 信息；如果节点日志存在关键报错，请提供相关节点的日志内容或文件；如果一些业务敏感信息不便提供，请留下联系方式，我们与您私下沟通。
