Cannot filter and sort on columns from a subselect

[superpan]

Version

1.26.0

What happened?

Cannot filter and sort on columns from a subselect

Relevant log output

sqlc generate
# package db
query.sql:50:7: column "distance" does not exist

Database schema

Table "public.clusters"
      Column       |           Type           | Collation | Nullable |      Default       | Storage  | Compression | Stats target | Description
-------------------+--------------------------+-----------+----------+--------------------+----------+-------------+--------------+-------------
 id                | uuid                     |           | not null | uuid_generate_v4() | plain    |             |              |
 name              | text                     |           | not null |                    | extended |             |              |
 cluster_embedding | vector(1536)             |           | not null |                    | extended |             |              |
 twin_id           | uuid                     |           | not null |                    | plain    |             |              |
 properties        | jsonb                    |           | not null |                    | extended |             |              |
 documents         | cluster_document[]       |           |          |                    | extended |             |              |
 ctype             | cluster_type             |           | not null |                    | plain    |             |              |
 date_created      | timestamp with time zone |           | not null | CURRENT_TIMESTAMP  | plain    |             |              |
 date_updated      | timestamp with time zone |           |          |                    | plain    |             |              |
Indexes:
    "clusters_pkey" PRIMARY KEY, btree (id)
Access method: heap

SQL queries

SELECT
  c.id,
  c.twin_id,
  c.name,
  c.ctype,
  c.documents,
  c.distance,
  c.properties
FROM (
  SELECT
    id,
    twin_id,
    name,
    ctype,
    documents,
    cluster_embedding <=> $1 AS distance,
    properties
  FROM clusters
  WHERE twin_id = $2
    AND ctype = $3
  ) AS c
WHERE twin_id = $2
  AND ctype = $3
  AND distance < $4
ORDER BY distance
LIMIT $5;

Configuration

version: "2"
sql:
  - engine: "postgresql"
    queries: "query.sql"
    schema: "../../../../ai/schemas/postgres"
    gen:
      go:
        package: "db"
        sql_package: "pgx/v5"
        out: "db"
        emit_db_tags: true
        emit_json_tags: true
        overrides:
          - db_type: "uuid"
            go_type:
              import: "github.com/gofrs/uuid"
              type: "UUID"

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

[superpan]

any ideas?

[superpan]

Hi team, I want to ping again about this. Any thoughts?