A subquery in the select list can't select a column of the outer query: column does not exist #4696

Version

1.31.1

What happened?

A subquery in an expression can read the columns of the statement around it. When its own select list names one, sqlc fails with column "name" does not exist: it looks the column up in the subquery's own FROM clause and nowhere else. A reference in the subquery's WHERE is fine. Only the select list fails.

With a database the failure is silent, as in #3156: the error is discarded and the database's description of the statement is used, which takes each column's NOT NULL from its table. In the query below, t.plate comes from the nullable side of a LEFT JOIN.

• With no database: query.sql:2:31: column "name" does not exist.
• With analyzer.database: true and a database.uri, it generates, and Plate is string:

type ListPlatesRow struct {
	ID    int64
	Plate string
	Name  pgtype.Text
}

I expected Plate pgtype.Text, as without the subquery column, and Name string.

It is the same on main (3ad8b20). #4182 looks like the same failure where the subquery is LATERAL.

I have a fix on a fork for the qualified form, d.name: Bockit@9386c64. The reference goes to the nearest enclosing statement that has a FROM item of that name, which is how PostgreSQL decides it, and the column takes its nullability from that statement's joins. It leaves an unqualified name and LATERAL failing as they do today, because sqlc's list of a FROM clause's columns isn't complete enough to place those safely. I can open a pull request if a fix to this path is wanted.

The analysis was done with AI help and checked by me.

Relevant log output

# package db
query.sql:2:31: column "name" does not exist

Database schema

CREATE TABLE depots (id BIGSERIAL PRIMARY KEY, name text NOT NULL);
CREATE TABLE trucks (id BIGSERIAL PRIMARY KEY, depot_id bigint NOT NULL, plate text NOT NULL);

SQL queries

-- name: ListPlates :many
SELECT d.id, t.plate, (SELECT d.name) AS name
FROM depots d
LEFT JOIN trucks t ON t.depot_id = d.id;

Configuration

version: "2"
sql:
  - engine: postgresql
    schema: schema.sql
    queries: query.sql
    gen:
      go:
        package: db
        out: db
        sql_package: pgx/v5

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go