Repository navigation
LIMIT results in Syntax Error #1665
Description
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:
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
Activity
kyleconroy commentedon Jun 10, 2022
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 afterLIMITandOFFSEThttps://play.sqlc.dev/p/ac63085049628c6b20fd85479e03ea071df9e42f5224a025863f7b6adba84ab4
rhodee commentedon Jun 10, 2022
Noted. I think this can be closed. I was hoping to use the
sqlc.argmethod but this is fine. Thank you @kyleconroysubc commentedon Apr 2, 2023
This is a rare case. I think fixing this issue is very low priority.
When using
UNION ALLin mysql, a problem occurred whenLIMIT ? OFFSET ?was specified.Specifically, Limit is not added to the
Paramsfield.The version I checked is "sqlc v1.15.0"
andrewmbenton commentedon Aug 14, 2023
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 ALLbug.