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

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;

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;

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

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

That's fine! No problem!

docs(changelog): add issue references for variable cast fix

test: simplify variable cast tests and add focused repros for #516 and …

Hi ! I know I'm late, but I have a planned fix in #1397 that you can try !

Stop casting variables to TEXT on PostgreSQL, MySQL and SQL Server (s…

Navigation Menu

Repository navigation

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

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

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

Description

Activity

lovasoa commented on Dec 18, 2025

srcarvalho12 commented on Dec 18, 2025

lovasoa commented on Dec 21, 2025

srcarvalho12 commented on Dec 21, 2025

lovasoa commented on Aug 22, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions