syntax error on generated SQL

[AdrienHorgnies]

Please go to comment https://github.com/sqlc-dev/sqlc/issues/3457#issuecomment-2199851963 for a more concise reproduction of the bug.

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
}

[SockworkOrange]

You misunderstood me. If the subquery after the IN clause returns 3 values and of these values is null, the entire query will return 0 rows, even if 14 is one of the other 2 values.

Meaning: 14 IN (NULL, 14) => false

In your case it's definitely an SQL parsing issue, but one that can be solved by using a more standard SQL syntax for this kind of query:)

[AdrienHorgnies]

I'm surprised by your explanation. I tested it, and the result I got contradicts this explanation:

sqlite> SELECT 14 IN (14);
14 IN (14)
1
sqlite> SELECT 14 IN (14, 28);
14 IN (14, 28)
1
sqlite> SELECT 14 IN (14, 28, NULL);
14 IN (14, 28, NULL)
1
sqlite> SELECT 14 IN (15, 28, NULL);
14 IN (15, 28, NULL)
# blank line

I could indeed get the desired result using a different query, but I disagree that it's more standard. I'm not aware of any resource classyfing SQL statements as standard or not standard.

In my opinion, any valid SQL statement should be compiled to go, unless it's really hard to implement in sqlc. In that latter case, I would suggest to make compilation fail.