MySQL Bugs: #82560: nested limit produces counter intuitive result

Bug #82560	nested limit produces counter intuitive result
Submitted:	12 Aug 2016 3:32	Modified:	12 Aug 2016 11:55
Reporter:	帅 Bang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.5.51, 5.6.32, 5.7.14	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	limit

Description:
mysql> drop table if exists t1;
mysql> create table t1(c1 int primary key);
mysql> insert into t1 values(1), (2),(3), (4),(5);
mysql> (select * from t1 limit 1) limit 5;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

IMHO, this is so counter intuitive. 1 row rather than 5 rows  is desired and expected.

How to repeat:
drop table if exists t1;
create table t1(c1 int primary key);
insert into t1 values(1), (2),(3), (4),(5);
(select * from t1 limit 1) limit 5;

Suggested fix:
In terms of (select * from t1 limit n) limit m :

if m > n  (select * from t1 limit n) limit m should be equivalent to select * from t1 limit n

if m <=n (select * from t1 limit n) limit m should be equivalent to select * from t1 limit m

In other words, (select * from t1 limit n) limit m should be equivalent to select * from t1 limit min(n,m)

Hello Bang,

Thank you for the report and test case.
Imho this expected and documented behavior i.e 

If LIMIT occurs within a subquery and also is applied in the outer query, the outermost LIMIT takes precedence. For example, the following statement produces two rows, not one:

(SELECT ... LIMIT 1) LIMIT 2;

Please see https://dev.mysql.com/doc/refman/5.6/en/select.html

Thanks,
Umesh

Hello, Umesh.

It is really kind of you to tell me that. 

But, when we take the fact in the following into consideration,  it is really tough for us to say this is not a bug.

drop table if exists t1;
create table t1(c1 int primary key);
insert into t1 values(1), (2),(3), (4),(5);
(select * from t1 limit 1) order by c1 desc limit 3;

Compared with '(select * from t1 limit 1) limit 3;',  this will produce 1 row rather than 3 rows in mysql.

The inconsistent result makes it really counter intuitive to us.

Thanks a lot. Looking forward to your reply, sincerely.

Thank you for the feedback!
Observed this with 5.5.51, 5.6.32 and  5.7.14 builds.

Thanks a lot.  Umesh. Really appreciate it.

BTW, just as we have seen that the result of 
(select * from t1 limit 1) order by c1 desc limit 3 is not consistent with (select * from t1 limit 1) limit 3;

So, could you tell me that , which one do you think is more reasonable and acceptable ? 

Thanks a lot.

Posted by developer:
 
Isn't this simply a case of wrong syntax? If you want a subquery, you need to actually create a subquery. Just putting parens around parts of the query does not create a subquery, you actually need to have a SELECT and give it an alias.

mysql> select * from (select * from t1 limit 1) t1 limit 5;

works as expected, returning only one row.

Posted by developer:
 
A little bird told me that it's valid syntax; it just isn't a subquery, it's a “parenthesized query expression”.