syntax error on generated SQL

[AdrienHorgnies]

Version

1.26.0

What happened?

When calling an UPDATE statement through the generated Golang code, it failed with the following error:

near "(": syntax error

Instead, I would have expected the statement to succeed

In the attached SQL query, replacing sqlc.arg(client_id) right below the comment -- owns the new client by an hardcoded value (for example 14) "fixes" the issue. The fact that the hardcoded value works makes me believe the query is valid. Also, I replaced all the arguments with actual values and ran the hardcoded statement in an sqlite terminal and it worked.

Relevant log output

near "(": syntax error

Database schema

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL
);

CREATE TABLE sessions (
    id BLOB PRIMARY KEY,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE clients (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL,
    user_id INTEGER NOT NULL,
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE projects (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    symbol TEXT,
    created_at TIMESTAMP NOT NULL,
    client_id INTEGER NOT NULL,
    FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE
);

CREATE TABLE timesheets (
    id INTEGER PRIMARY KEY,
    period_start TIMESTAMP NOT NULL,
    period_end TIMESTAMP NOT NULL,
    created_at TIMESTAMP NOT NULL,
    user_id INTEGER NOT NULL,
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE activities (
    id INTEGER PRIMARY KEY,
    date TIMESTAMP NOT NULL,
    duration INTEGER NOT NULL,
    timesheet_id INTEGER NOT NULL,
    project_id INTEGER NOT NULL,
    FOREIGN KEY(timesheet_id) REFERENCES timesheets(id) ON DELETE CASCADE,
    FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE CASCADE
);

SQL queries

-- name: UpdateProject :one
UPDATE projects
SET name = sqlc.arg(name),
    client_id = sqlc.arg(client_id)
WHERE projects.id = sqlc.arg(id)
-- owns the project
AND projects.client_id IN (
    SELECT clients.id
    FROM clients
    WHERE clients.user_id = sqlc.arg(user_id)
)
-- owns the new client
AND sqlc.arg(client_id) IN (
    SELECT clients.id
    FROM clients
    WHERE clients.user_id = sqlc.arg(user_id)
)
RETURNING *;

Configuration

version: "2"
sql:
  - engine: "sqlite"
    queries: "queries.sql"
    schema: "migrations"
    gen:
      go:
        package: "db"
        out: "."
        overrides:
          - column: "activities.duration"
            go_type: "time.Duration"

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

SQLite

What type of code are you generating?

Go

[SockworkOrange]

The issue isn't reproduced for me with Mac setup - Uploaded the generated code. example.zip

Additionally, your specific use case is a bit weird - you'd usually won't place parameters in your SQL in that manner, and in the case one of the clients.id values in the subquery is null - the query will return 0 rows.

That update statement can be rewritten like this, handling null values in the subquery correctly:

-- name: UpdateProject :one
UPDATE projects
..........
-- owns the new client
AND EXISTS (
    SELECT 1
    FROM clients
    WHERE clients.user_id = sqlc.arg(user_id)
    AND clients.id = sqlc.arg(client_id)
)

RETURNING *;

[AdrienHorgnies]

Hi,

the query will return 0 rows.

That was my intent.

your specific use case is a bit weird

I'm not sure what's weird. I used the arguments as part of expressions. Using them on the left side of an equal expression or IN expression shouldn't make a difference.

I found out the EXISTS statement worked and I'm currently using it. That's a satisfactory way to write the statement.

I'll try to reproduce the issue in a Github workflow. If I can't, I'll close this issue.

[AdrienHorgnies]

I made a public repository with a minimal configuration to reproduce the issue: https://github.com/AdrienHorgnies/sqlc-issue-3457.

I also added a Github action where the issue occur: https://github.com/AdrienHorgnies/sqlc-issue-3457/actions/runs/9743369472.

The same query works when I run it manually:

$ sql sqlite3://./main.db
SQLite version 3.45.1 2024-01-30 16:01:20
Enter ".help" for usage hints.
sqlite> SELECT * FROM users;
id|name
1|foo
sqlite> UPDATE users SET NAME = "bar" WHERE "bar" IN ("bar");
sqlite> SELECT * FROM users;
id|name
1|bar

Here are the queries:

-- name: InsertUsers :exec
INSERT INTO users (name)
VALUES ("foo");

-- name: UpdateUsers :exec
UPDATE users
SET NAME = sqlc.arg(name)
WHERE sqlc.arg(name) IN ("bar");

Here's the generate code:

// Code generated by sqlc. DO NOT EDIT.
// versions:
//   sqlc v1.26.0
// source: queries.sql

package main

import (
    "context"
)

const insertUsers = `-- name: InsertUsers :exec
INSERT INTO users (name)
VALUES ("foo")
`

func (q *Queries) InsertUsers(ctx context.Context) error {
    _, err := q.db.ExecContext(ctx, insertUsers)
    return err
}

const updateUsers = `-- name: UpdateUsers :exec
UPDATE users
SET NAME = ?1
WHERE sqlc.arg(name) IN ("bar")
`

func (q *Queries) UpdateUsers(ctx context.Context, name string) error {
    _, err := q.db.ExecContext(ctx, updateUsers, name)
    return err
}