LIMIT ... OFFSET ... does not work with sqlpage variables in MySQL

[srcarvalho12]

I can't use variables defined with "SET" in offset in MariaDB.

error returned from database: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CAST(? AS CHAR) OFFSET CAST(? AS CHAR)' at line 1

I'm defining the variables like this:

set limite = 10;
set offset = (COALESCE($page, 1) - 1) * $limite;

And then I'm trying to use them in the consultation:

...
order by
    id desc
limit $limite
offset $offset;

[lovasoa]

Most databases allow arbitrary expressions in limit clauses, but unfortunately mysql doesn't

LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:
Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.

https://dev.mysql.com/doc/refman/9.0/en/select.html

What you canndo to work around the problem is use row_number:

with numbered as (
  SELECT *, row_number() over (order by name) as n FROM demo
)
SELECT * FROM numbered where n >= $offset and n < $offset + $limit;

[srcarvalho12]

I understand! Thank you for your reply, I'll look into it!

[lovasoa]

Let's leave this open, it's still a usability issue. If SQLPage were smart enough to omit the cast to a string here the obvious limit ... offset ... syntax would work out of the box

[srcarvalho12]

That's fine! No problem!