Ambiguous column reference in nested select

[jschaf]

In a nested sub-select from a single table, sqlc reports an ambiguous column reference even though the inner query is only from a single table.

Config: sqlc.yaml

version: "1"
packages:
  - name: "db"
    path: "acctdb"
    queries: "./acctdb/sqlc_queries.sql"
    schema: "./acctdb/account_schema.sql"
    engine: "postgresql"

SQL schema

CREATE TABLE test (
  id          bigint NOT NULL,
  update_time bigint NOT NULL,
  count       bigint NOT NULL
);

SQL queries

SELECT latest.id, t.count
FROM (
  SELECT id, max(update_time) AS update_time
  FROM test
  WHERE id = ANY (sqlc.arg('IDs')::bigint[])
    -- ERROR HERE on update_time
    AND update_time >= sqlc.arg('StartTime')
  GROUP BY id
) latest
INNER JOIN test t USING (id, update_time);

Command

$ docker run --rm -v /my/code:/src -w /src -u "1000:1000" kjconroy/sqlc generate

acctdb/sqlc_queries.sql:44:9: column reference "update_time" is ambiguous

Workaround Use a qualified reference, i.e. replace update_time with test.update_time.

Playground

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

[friend0]

This is preventing me from using the new version. Here's a contrived example of how generation fails for me: SELECT , BOOL_AND(myCol) AS myCol FROM myTable as uasf

Results in: column reference "myCol" is ambiguous

[kyleconroy]

This is fixed in v1.23.0 by enabling the database-backed query analyzer. We added a test case for this issue so it won’t break in the future.

You can play around with the working example on the playground