LIMIT results in Syntax Error

[rhodee]

Version

1.13.0

What happened?

Hello!

I tried to create a query with dynamic query/offset. Unfortunately, it returned an error.

Tickets I discovered:

https://github.com/kyleconroy/sqlc/issues?q=is%3Aissue+is%3Aopen+limit+label%3A%22%3Abooks%3A+mysql%22

Things I've tried:

#1439 - it appears changing the reserve words did not change the error.

Comparing the output of PG and MySQL

---

This works for PG and not for MySQL

• PG: https://play.sqlc.dev/p/a5a03961f9a447cf433773e462ef1508f677ebc263e533fcf53f481b9224a650
• MySQL: https://play.sqlc.dev/p/d6232b25b11799f71f0bae2951b41ab359c0c299c6a1287a54567696f1a5e650

Relevant log output

sqlc generate failed.
# package db
query.sql:16:11: syntax error near "sqlc.arg(row_offset), sqlc.arg(row_limit);"

Database schema

-- Example queries for sqlc
CREATE TABLE authors (
  id  varchar(255) NOT NULL,
  name text      NOT NULL,
  bio  text
);

SQL queries

-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name
LIMIT sqlc.arg(row_offset), sqlc.arg(row_limit);

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "db",
      "engine": "mysql",
      "schema": "query.sql",
      "queries": "query.sql"
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/d6232b25b11799f71f0bae2951b41ab359c0c299c6a1287a54567696f1a5e650

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

[kyleconroy]

I got this to work by using regular ? parameters inside of named parameters. The MySQL parser we're using is not happy with function calls after LIMIT and OFFSET

https://play.sqlc.dev/p/ac63085049628c6b20fd85479e03ea071df9e42f5224a025863f7b6adba84ab4

-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name
LIMIT ? OFFSET ?;

[rhodee]

Noted. I think this can be closed. I was hoping to use the sqlc.arg method but this is fine. Thank you @kyleconroy

[subc]

This is a rare case. I think fixing this issue is very low priority.
When using UNION ALL in mysql, a problem occurred when LIMIT ? OFFSET ? was specified.
Specifically, Limit is not added to the Params field.
The version I checked is "sqlc v1.15.0"

-- name: ListAuthors :many
SELECT * FROM authors
WHERE country = 'DE'
UNION ALL
SELECT * FROM authors2
WHERE country = 'US'
LIMIT ? OFFSET ?;

[andrewmbenton]

Closing since this is very old and the workaround seems acceptable until and unless the upstream parser is changed.

I created a new issue (#2611) for the UNION ALL bug.