Skip to content

LIMIT results in Syntax Error #1665

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
rhodee opened this issue Jun 8, 2022 · 4 comments
Closed

LIMIT results in Syntax Error #1665

rhodee opened this issue Jun 8, 2022 · 4 comments
Labels
📚 mysql 💻 darwin upstream Issue is caused by a dependency 🔧 golang

Comments

@rhodee
Copy link

rhodee commented Jun 8, 2022 •
edited by kyleconroy
Loading

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

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
@rhodee rhodee added bug Something isn't working triage New issues that hasn't been reviewed labels Jun 8, 2022
@kyleconroy kyleconroy added 📚 mysql 💻 darwin 🔧 golang upstream Issue is caused by a dependency and removed bug Something isn't working triage New issues that hasn't been reviewed labels Jun 10, 2022
@kyleconroy
Copy link
Collaborator

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
Copy link
Author

rhodee commented Jun 10, 2022

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

@subc
Copy link

subc commented Apr 2, 2023

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 andrewmbenton mentioned this issue Aug 14, 2023
Closed
@andrewmbenton
Copy link
Collaborator

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.

@andrewmbenton andrewmbenton closed this as not planned Won't fix, can't repro, duplicate, stale Aug 14, 2023
@GuillaumeDesforges GuillaumeDesforges mentioned this issue Nov 27, 2024
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
📚 mysql 💻 darwin upstream Issue is caused by a dependency 🔧 golang
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@kyleconroy @andrewmbenton @rhodee @subc