Column does not exist using CTE with nested sub-select

[jschaf]

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 test1 (
  env_id         bigint      NOT NULL,
  start_time     timestamptz NOT NULL,
  PRIMARY KEY (env_id, start_time)
);

CREATE TABLE test2 (
  env_id       bigint      NOT NULL,
  update_time  timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (env_id, update_time)
);

SQL queries

-- name: FooBar :many
WITH
  agg1 AS (
    SELECT env_id, sum(1) AS num_sessions
    FROM test1 sm
    -- ERROR HERE:start_time does not exist
    WHERE sm.start_time >= sqlc.arg('StartTime')
    GROUP BY sm.env_id
  ),
  agg2 AS (
    SELECT 2 AS env_id, 2 AS num_shards
    FROM (
      SELECT env_id, 3 AS update_time
      FROM test2
    ) latest
    INNER JOIN test2 sm USING (env_id, update_time)
  )
SELECT a1.env_id               AS env_id,
       a1.num_sessions::bigint AS num_sessions,
       a2.num_shards           AS num_shards
FROM agg1 a1
FULL OUTER JOIN agg2 a2 USING (env_id)
ORDER BY env_id;

Command

docker run --rm -v /p/heap/services/platform/account/pkg/pg:/src -w /src -u "1000:1000" kjconroy/sqlc generate

acctdb/sqlc_queries.sql:6:11: column "start_time" does not exist

I think what's happening is that agg1 and agg2 have different columns. sqlc seems to want agg1 to have the same columns as agg2. Since agg2 has update_column instead of start_column, sqlc errors. This is just a guess.

[jschaf]

I think I found the problem.

sqlc fails if the same table alias is used in multiple CTEs. In the example above, I used sm for the agg1 and agg2 CTE. Changing one of the aliases to a different names fixes the error.

[kyleconroy]

@jschaf thanks for the great bug report. I really appreciate including the config file, schema, and query. Sorry I haven't had a chance to look at this yet.

[kyleconroy]

Fixed in #896