Bug #103954 multiple LIMIT clauses maybe get wrong result

• Submitted: 9 Jun 2021 7:05
• Modified: 17 Jun 2022 0:12
• Reporter: Shenghui Wu
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7.32, 8.0.22, 8.0.25, 5.7.34
• OS: Any
• CPU Architecture: Any

[9 Jun 2021 7:05] Shenghui Wu

Description:
Multiple LIMIT clauses is allowed or not? Is there any docs clearly define this hehavior?  And is this behavior reasonable?

How to repeat:
create table R(id integer, title varchar(100));
insert into R(id, title) values(1, "Hello");
insert into R(id, title) values(2, "Hello");
insert into R(id, title) values(3, "Hello");
insert into R(id, title) values(4, "Hello");
insert into R(id, title) values(5, "Hello");
insert into R(id, title) values(6, "Hello");
insert into R(id, title) values(7, "Hello");
(select * from R limit 1) limit 10;
+------+-------+
| id   | title |
+------+-------+
|    1 | Hello |
|    2 | Hello |
|    3 | Hello |
|    4 | Hello |
|    5 | Hello |
|    6 | Hello |
|    7 | Hello |
+------+-------+

We can see that on MySQL it returns 10 tuples if the table has enough rows.

[9 Jun 2021 7:56] MySQL Verification Team

Hello Shenghui Wu,

Thank you for the report and test case.

regards,
Umesh

[17 Jun 2022 0:12] Jon Stephens

Fixed in MySQL 8.0.31 by WL#11350.

Closed.