Update table with select causes ambiguous column error

[cytown]

Version

1.26.0

What happened?

First time I tried field name "id" in both table, it's very common in realworld, but it said "id" is ambiguous, so I change the id to bid in second table, but still get wrong...

Also tried sqlc.args(xxx), but will failed either.

For your reference, the sql clause should not failed.

Relevant log output

sqlc generate failed.
# package 
query.sql:6:40: column reference "bid" is ambiguous

Database schema

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);


CREATE TABLE authors2 (
  bid   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

SQL queries

-- name: CreateAuthor2 :one
UPDATE authors SET (
  name, bio
) = (
  select name, bio from authors2 where bid=$2
) where id=$1
RETURNING *;

Configuration

{
  "version": "2",
  "sql": [{
    "schema": "schema.sql",
    "queries": "query.sql",
    "engine": "postgresql",
    "gen": {
      "go": {
        "out": "db"
      }
    }
  }]
}

Playground URL

https://play.sqlc.dev/p/3ef93462e0cc926c18e7d79bbdd1ce8239fe3b1c4afed1026cd357502a8c7adf

What operating system are you using?

Linux, macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[ed-henrique]

Similar problem here, but with a different input:

• I have also tried to use an alias, as supported by SQLite in UPDATE statements, but it did not remove the ambiguity issue.

Version

1.26.0

Relevant log output

# package db
queries/queries.sql:5:7: column reference "id" is ambiguous

From playground:

sqlc generate failed.

2024/08/08 15:33:36 sqlite.convertLiteral: Unknown node type *parser.Expr_literalContext
2024/08/08 15:33:36 sqlite.convertLiteral: Unknown node type *parser.Expr_literalContext
2024/08/08 15:33:36 sqlite.convert(case=default): Unknown node type *parser.Expr_unaryContext
# package 
query.sql:5:7: column reference "id" is ambiguous

Database schema

CREATE TABLE IF NOT EXISTS USERS (
  ID INTEGER PRIMARY KEY,
  DOCUMENT INTEGER,
  REMOVED_AT TEXT,
  FOREIGN KEY (DOCUMENT) REFERENCES DOCUMENTS (ID)
);

CREATE TABLE IF NOT EXISTS DOCUMENTS (
  ID INTEGER PRIMARY KEY,
  REMOVED_AT TEXT
);

SQL queries

-- name: RemoveDocument :execrows
UPDATE DOCUMENTS
SET
  removed_at = CURRENT_TIMESTAMP
WHERE id = ?1
  AND removed_at IS NULL
  AND id NOT IN (SELECT document FROM USERS WHERE document IS NOT NULL);

Configuration

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

Playground URL

https://play.sqlc.dev/p/906eac25119af6f519b77983cfe4a67e5aa41465e76dbcdbe08af6c8b5b033e3

What operating system are you using?

Linux

What database engines are you using?

SQLite

What type of code are you generating?

Go