SQLite UPDATE with RETURNING and ORDER BY / LIMIT clauses fail to parse

[gavincarr]

Version

1.27.0

What happened?

sqlc fails to parse UPDATE statements that include both RETURNING and ORDER BY / LIMIT clauses.

Relevant log output

line 53:13 no viable alternative at input ';'
line 60:0 extraneous input 'order' expecting {<EOF>, ';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
# package sqlc
query.sql:1:1: extraneous input 'order' expecting {<EOF>, ';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
db.go:5: running "sqlc": exit status 1

Database schema

CREATE TABLE queue (
  qid integer primary key,
  domain varchar not null,
  lock_ts varchar not null default '',
  create_ts varchar not null default current_timestamp
);
CREATE UNIQUE INDEX queue_domain on queue(domain);

SQL queries

-- name: LockQueueNext :one
update queue
set lock_ts = current_timestamp
where lock_ts = ''
returning *
order by qid limit 1;

Configuration

version: 2
sql:
  - engine: "sqlite"
    schema: "db/migrations"
    queries: "query.sql"
    gen:
      go:
        package: "sqlc"
        out: "sqlc"

Playground URL

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

What operating system are you using?

Linux

What database engines are you using?

SQLite

What type of code are you generating?

Go

[dimmerz92]

Also having a similar issue, as above, tried aliasing to no avail.

Version

v1.27.0

Relevant log output

line 71:2 no viable alternative at input 'UPDATE\n\tEvents AS e\nSET\n\te.'
line 78:3 no viable alternative at input 'Entries'
# package models
sql/queries/events.sql:1:1: no viable alternative at input 'Entries'

Database schema

-- +goose Up
CREATE TABLE IF NOT EXISTS Events (
    Id INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Description TEXT NOT NULL,
    Visible INTEGER NOT NULL DEFAULT (1) CHECK (Visible IN (0, 1)),
    Location TEXT REFERENCES Locations(Name),
    EventDateTime INTEGER NOT NULL CHECK (EventDateTime > strftime('%s', 'now')),
    CreatedAt INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
    UpdatedAt INTEGER
) STRICT;

-- +goose Up
CREATE TABLE IF NOT EXISTS Entries (
    Id INTEGER PRIMARY KEY,
    Name TEXT NOT NULL,
    Description TEXT,
    UserId TEXT NOT NULL REFERENCES Users(Id) ON DELETE CASCADE,
    EventId INTEGER NOT NULL REFERENCES Events(Id) ON DELETE CASCADE,
    Category TEXT NOT NULL REFERENCES Categories(Name) ON DELETE CASCADE,
    Score INTEGER CHECK (Score BETWEEN 0 AND 10),
    CreatedAt INTEGER NOT NULL DEFAULT (strftime('%s', 'now')),
    UpdatedAt INTEGER
) STRICT;

SQL queries

-- name: ToggleEventVisibility :one
UPDATE
    Events AS e
SET
    e.Visible = 1 - e.Visible
WHERE
    e.Id = ? AND
    e.Id NOT IN (
        SELECT DISTINCT
            t.EventId
        FROM
            Entries AS t
    )
RETURNING
    COUNT(*);

Configuration

version: "2"
sql:
  - engine: "sqlite"
    queries: "./sql/queries/*"
    schema: "./sql/schema"
    gen:
      go:
        package: "models"
        out: "./internal/models"

Playground URL

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

What operating system are you using?

OS 1: Distributor ID: Debian Description: Debian GNU/Linux 12 (bookworm) Release: 12 Codename: bookworm

OS 2: Apple M1 ProductName: macOS ProductVersion: 14.5 BuildVersion: 23F79

What database engines are you using?

SQLite

What type of code are you generating?

Go