Column/table references inside aggregate functions are not validated against the schema

[Jorann]

Version

1.30.0

What happened?

What happened

sqlc does not validate table or column references used inside PostgreSQL aggregate functions (e.g. ARRAY_AGG, JSON_AGG, etc.) against the known schema. A query referencing a completely non-existent relation inside an aggregate compiles without any error, and Go code is generated for it.

For example, whatever is not a table, view, alias, or CTE anywhere in the schema, yet the following query passes validation and produces generated code:

-- name: BugDemo :exec
SELECT id, ARRAY_AGG(whatever.*) FROM worker;

PostgreSQL itself rejects this query at execution time with:

ERROR: missing FROM-clause entry for table "whatever"

Expected behavior

sqlc should report a compile-time error indicating that whatever is not a valid relation, the same way it would if whatever were referenced outside of an aggregate function (e.g. SELECT whatever.* FROM worker does produce an error).

Actual behavior

sqlc silently accepts the query and generates valid-looking Go code:

const bugDemo = `-- name: BugDemo :exec
SELECT id, ARRAY_AGG(whatever.*) FROM worker
`

func (q *Queries) BugDemo(ctx context.Context) error {
	_, err := q.db.Exec(ctx, bugDemo)
	return err
}

The error is only discovered at runtime when the query is executed against the database.

Relevant log output

ERROR: missing FROM-clause entry for table "whatever"

Database schema

CREATE TABLE worker (
    id UUID PRIMARY KEY,
    url TEXT NOT NULL,
    meta JSONB
);

SQL queries

-- name: BugDemo :exec
SELECT id, ARRAY_AGG(whatever.*) FROM worker;

Configuration

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

Playground URL

https://play.sqlc.dev/p/272e3894a5fd33bf0411f8829adcd7b81d2b3dc252f77569ce628b8176763afa

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[nikolayk812]

This is similar I believe: #4291