MySQL Bugs: #33856: LIMIT clause doesn't accept any variable or other, just constant numbers

Bug #33856	LIMIT clause doesn't accept any variable or other, just constant numbers
Submitted:	14 Jan 2008 0:35	Modified:	26 Mar 2008 10:18
Reporter:	Stéphane GRAZIANO	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S4 (Feature request)
Version:	Any	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	LIMIT CLAUSE VARIABLE STORED PROCEDURE

Description:
AS you know, LIMIT clause doesn't accept any variable or other, just constant numbers. It is very poor to use prepared statement to do this...so I was looking for a solution. I found one ! It's working but incomplete and could be crashy ^^.

So this solution works BUT it could crash the server if the variable name is unknown. If somebody could help me to resolve this issue, that would be a very great functionnality for mysql developers !

Stéphane from France 

How to repeat:
SET @limit1 := 1;
SET @limit2 := 2;
SELECT * FROM mysql.user LIMIT (@limit1+@limit2);

Suggested fix:
In order to fix, go to the parser file sql_yacc.yy to the option_value line and change like this :

limit_option:
        param_marker
        {
          ((Item_param *) $1)->set_strict_type(INT_RESULT);
        }        
        | simple_expr // this is the fix which allow expressions and variables !
    ;

Many thanks for writing a feature request.

I looked through SQL Standard,  but I couldn't found something contra or pro this.

test

Thanks Susanne ^^ It will be great if this were implemented in the next build... 

Humn, Just a little mistake in my bug description (correction in uppercase), 

Suggested fix:
In order to fix, go to the parser file sql_yacc.yy to the LIMIT_OPTION line and change

Many thanks for writing a feature request. Some others also have had the same wish as you. This bug is a duplicate of bug #6673 and bug #8094.

Please add this. Not having this leads to horrible code in stored procedures.

Idem, add something that works... nothing in 6.0 alpha!

Gert's suggestion earlier works nicely as a workaround. Here it is again with slightly different syntax.

DELIMITER $$
CREATE PROCEDURE get_product_range (
IN _START INTEGER,
IN _LIMIT INTEGER
)
BEGIN
PREPARE STMT FROM
" SELECT *
FROM tblProduct LIMIT ?,? ";
SET @START = _START;
SET @LIMIT = _LIMIT;
EXECUTE STMT USING @START, @LIMIT; 
END $$
DELIMITER ;