Bug #6673 Expressions not available as LIMIT parameters
Submitted: 17 Nov 2004 0:13 Modified: 8 Aug 2007 12:54
Reporter: Michael Long Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S4 (Feature request)
Version:4.1.7 OS:Windows (Win XP)
Assigned to: Marc ALFF CPU Architecture:Any

[17 Nov 2004 0:13] Michael Long 
Description:
I was trying to do a single-query median statement using subselects, as in:

SELECT year(now())-birth_year 
FROM customers 
WHERE birth_year is not null 
ORDER BY birth_year 
LIMIT truncate(select count(*)/2 from customers where birth_year is not null),1

Unfortunately, it appears that no expressions, including subselects, can be used as LIMIT clause parameters.

How to repeat:
Try using an expression as a limit parameter.

Suggested fix:
Allow expressions.
[17 Nov 2004 0:29] MySQL Verification Team 
Please see: http://dev.mysql.com/doc/mysql/en/SELECT.html

where you should find that the LIMIT clause can takes 2 arguments
and must be integers constants.

Thank you for the bug report
[17 Nov 2004 2:45] Michael Long 
So? Why should it be limited to integer constants? The fact that it's written up that way doesn't make it the correct, or most useful, behaviour.
[17 Nov 2004 10:29] Sergei Golubchik 
While it make be useful to have expressions in LIMIT, inability to do so is not a bug. I'm chaning this into a feature request
[8 Aug 2007 12:54] Konstantin Osipov 
Thank you for a valid feature request.
[8 Aug 2007 12:55] Konstantin Osipov 
See also Bug#8094 Variables in the LIMIT Clause
[21 Mar 2008 22:18] Anthony Taylor 
Unless I am looking at this wrong, it seems as if the prepared statement approach could create a serious security hole in an application.  In conventional  stored procedures, everything is compiled so a user should not be able to perform basic sql injection attacks.  In the prepared statement approach, a user could pass in anything that may slip past the data validation.
[26 Mar 2008 10:19] Susanne Ebrecht 
Set bug #33856 as duplicate of this bug here.
[2 Jun 2008 7:37] James Low 
I really hope you do this soon, though the quickest way would be to allow a variable in the limit clause: http://bugs.mysql.com/bug.php?id=8094 and then you could do the expression before and asign it to the variable.
[9 Jul 2008 3:10] Ilkka Huotari 
Please add this. Not having this leads to horrible code in stored procedures.
[15 Dec 2008 16:52] MySQL Verification Team 
Bug http://bugs.mysql.com/bug.php?id=41479 marked as duplicate of this one.
[16 Dec 2011 20:32] Valeriy Kravchuk 
Bug #27184 was marked as a duplicate of this one.