Select query generating wrong position for parameters placeholders

[juniorgarcia]

Version

1.27.0

What happened?

I tried to create a quite simple SELECT query and was not sure why the LIMIT and OFFSET params were not working properly. After inspecting the generated code I saw that, for some reason, the order of the LIMIT and OFFSET placeholders were switched and this was causing the odd behavior.

Below, the generated query :

-- name: GetTodosForUser :many
SELECT id, title, description, done
FROM
    todos
WHERE
    user_id = ? AND
    done = COALESCE(?2, done) AND
    title LIKE COALESCE(CONCAT("%", ?3 ,"%"), title) AND
    description LIKE COALESCE(CONCAT("%", ?4, "%"), description)
-- Here, LIMIT be index "5" and OFFSET "6"
LIMIT ?6 OFFSET ?5

Relevant log output

No response

Database schema

CREATE TABLE todos (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    description TEXT,
    done BOOLEAN DEFAULT 0
, user_id INTEGER REFERENCES users(id));
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    activated BOOLEAN DEFAULT 0
);

SQL queries

-- name: GetTodosForUser :many
SELECT id, title, description, done
FROM
    todos
WHERE
    user_id = ? AND
    done = COALESCE(sqlc.narg('done'), done) AND
    title LIKE COALESCE(CONCAT("%", sqlc.narg('title') ,"%"), title) AND
    description LIKE COALESCE(CONCAT("%", sqlc.narg('description'), "%"), description)
LIMIT sqlc.arg(limit) OFFSET sqlc.arg(offset);

Configuration

version: "2"
sql:
  - engine: "sqlite"
    queries: "db/queries/queries.sql"
    schema: "db/schema.sql"
    gen:
      go:
        emit_json_tags: true
        emit_pointers_for_null_types: true
        package: "db"
        out: "internal/db"
        overrides:
          - column: todos.title
            go_struct_tag: validate:"required,min=4"
          - column: todos.user_id
            go_struct_tag: json:"-" validate:"required"
          - column: users.name
            go_struct_tag: validate:"required,min=4"
          - column: users.email
            go_struct_tag: validate:"required,email"
          - column: users.password
            go_struct_tag: validate:"required"

Playground URL

https://play.sqlc.dev/p/031a5a5c121bc1679aea839a3d47c3787d723bb2af4b0b51553d868e2721536e

What operating system are you using?

macOS

What database engines are you using?

SQLite

What type of code are you generating?

Go

[juniorgarcia]

After some testing I saw (by completely guessing) that changing the last line to LIMIT :limit OFFSET :offset works properly, generating the query:

-- name: GetTodosForUser :many
SELECT id, title, description, done
FROM
    todos
WHERE
    user_id = ? AND
    done = COALESCE(?2, done) AND
    title LIKE COALESCE(CONCAT("%", ?3, "%"), title) AND
    description LIKE COALESCE(CONCAT("%", ?4, "%"), description)
LIMIT ? OFFSET ?;

If that's the right way of writing this query, is it in the docs? Couldn't find anything about it.

Edit: add more information