How does LIMIT accept a parameter

43 views Asked by At

Using DBFiddle, I create a table

create table t(x int);
insert into t values(1),(2),(3);

LIMIT works

select * from t limit 1,1;

returns x|2

It is documented that user-defined variables are not allowed.

User variables may be used in most contexts where expressions are permitted. This does not currently include contexts that explicitly require a literal value, such as in the LIMIT clause of a SELECT statement, or the IGNORE N LINES clause of a LOAD DATA statement

so

set @s=1;
select * from t limit @s,1;

produces an error:

Query Error: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@s,1' at line 1

However, if I create a procedure

delimiter //;
create procedure lmt(s int)
begin
select * from t limit s,1;
end;

call lmt(@s);

it appears that parameters are accepted, so what is the difference between a user-variable and a procedure parameter that allows this?

0

There are 0 answers