Bug #86167 Local Variables not supported by LIMIT Clause

• Submitted: 3 May 2017 11:29
• Modified: 22 May 2017 16:18
• Reporter: xu michael
• Status: Not a Bug
• Category: MySQL Server: Documentation
• Severity: S3 (Non-critical)
• Version: 5.7
• OS: Any
• CPU Architecture: Any

[3 May 2017 11:29] xu michael

Description:
In MySQL 5.7 Reference Manual:
Within stored programs, LIMIT parameters can be specified using integer-valued routine
parameters or local variables.

Yet, MySQL WorkBench 6.3 Report Error

How to repeat:
create procedure p1()
begin
   declare skip,rowCount int;
   set @skip:=0,@rowCount:=10;
   select * from table1 limit @skip,@rowCount;
end

[3 May 2017 14:44] MySQL Verification Team

https://bugs.mysql.com/bug.php?id=11918

[3 May 2017 16:50] MySQL Verification Team

Hi !

Indeed, I have found that this is an error in the 5.7 manual.

I have tried two variants. First, with local variables that are declared in the stored routine:

DROP PROCEDURE IF EXISTS p1;
\d $$
CREATE PROCEDURE p1()
BEGIN
declare skip,rowCount int;
set @skip:=0,@rowCount:=10;
  select * from sinbas.ps_product_shop limit @skip, @rowCount;
END;
$$
\d ;
CALL p1();
DROP PROCEDURE IF EXISTS p1;

and I have got a syntax error.

Then, I tried with local variables declared in the script itself:

DROP PROCEDURE IF EXISTS p1;
SET @skip=5; 
SET rowCount=10;
\d $$
CREATE PROCEDURE p1()
BEGIN
  select * from sinbas.ps_product_shop limit @skip, @rowCount;
END;
$$
\d ;
CALL p1();
DROP PROCEDURE IF EXISTS p1;

And again I got a syntax error.

The only thing that worked just fine was passing a parameter into the stored routine and using it in LIMIT clause.

Hence, documentation has to be clarified.

[22 May 2017 16:18] Paul DuBois

Posted by developer:
 
The problem is with the test cases, not the documentation.

A local variable is a variable declared within a stored program using DECLARE, as is done in the test case:

create procedure p1()
begin
   declare skip,rowCount int;
   set @skip:=0,@rowCount:=10;
   select * from table1 limit @skip,@rowCount;
end

However, those local variables are never accessed. Instead, following the DECLARE, @skip and @rowCount are used. Variables beginning with @ are user-defined variables, not local variables. See:
https://dev.mysql.com/doc/refman/en/user-variables.html

Solution: Don't use user-defined variables. Instead, use the declared local variables:

create procedure p1()
begin
   declare skip,rowCount int;
   set skip:=0,rowCount:=10;
   select * from table1 limit skip,rowCount;
end