SQL generation error with `where` clause

[carlineng]

Reported on this Slack thread:

Model:

source: postal_location is table('RFU:rfu_owner.postal_location') extend {
    primary_key: id
}

source: scenario is table('RFU:rfu_owner.scenario') extend {
    primary_key: id
    join_one: postal_location with postal_location_id
    where: 
        delivery_date = now::date
}

source: round_ is table('RFU:rfu_owner.round') extend {
    primary_key: id
    join_one: scenario with scenario_id
    where: 
        delivery_date = now::date
}

Query:

query: round_ -> {
    where:
        --scenario.roma_scenario_name ~ '%7060'
        scenario.postal_location.name ~ '%ROESELARE%'
    group_by: 
        round_type
    nest: round_details is {
        project:
            round_name
            operational_round_start_time
            operational_round_end_time
            user_matricule
        order_by:
            round_name
    }    
    order_by: round_type
}

Generated SQL:

WITH __stage0 AS (
  SELECT
    group_set,
    round_."round_type" as "round_type__0",
    COALESCE(TO_JSONB((ARRAY_AGG((SELECT TO_JSONB(__x) FROM (SELECT 
      round_."round_name" as "round_name", 
      round_."operational_round_start_time" as "operational_round_start_time", 
      round_."operational_round_end_time" as "operational_round_end_time", 
      round_."user_matricule" as "user_matricule"
      ) as __x)  ORDER BY  round_."round_name" ASC NULLS LAST ) FILTER (WHERE group_set=0))),'[]'::JSONB) as "round_details__0"
  FROM "rfu_owner"."round" as round_
  LEFT JOIN (
    SELECT scenario_0.*, ROW(postal_location_0) AS postal_location_0
    FROM "rfu_owner"."scenario" AS scenario_0
    LEFT JOIN "rfu_owner"."postal_location" AS postal_location_0
      ON postal_location_0."id"=scenario_0."postal_location_id"
    
    WHERE scenario_0."delivery_date"=CAST(LOCALTIMESTAMP  AS date)
  ) AS scenario_0
    ON scenario_0."id"=round_."scenario_id"
  CROSS JOIN GENERATE_SERIES(0,0,1) as group_set
  WHERE (postal_location_0."name" LIKE '%ROESELARE%')
  AND (round_."delivery_date"=CAST(LOCALTIMESTAMP  AS date))
  GROUP BY 1,2
)
, __stage1 AS (
  SELECT
    "round_type__0" as "round_type",
    (ARRAY_AGG("round_details__0") FILTER (WHERE group_set=0 AND "round_details__0" IS NOT NULL))[1] as "round_details"
  FROM __stage0
  GROUP BY 1
  ORDER BY 1 ASC NULLS LAST
)
SELECT row_to_json(finalStage) as row FROM __stage1 AS finalStage

The WHERE (postal_location_0."name" LIKE '%ROESELARE%') filter is attempting to access postal_location_0, but I don't think that tablename is in scope; it should be accessed via scenario_0

[szawadski]

Actually, it seems not linked to the nest. I get the same error message when I am using a where clause with elements 2 joins away. This does not work:

query: round_ -> {
    where: scenario.postal_location.name ~ '%ROESELARE%'
    project: round_name

While this works fine:

query: round_ -> {
    where: scenario.roma_scenario_name ~ '%7060'
    project: round_name
}

[lloydtabb]

This looks like it has to do with filtered sources somehow. In order to filter a nested source we recombine into a nested table.. Maybe doesn't work right on postgres somehow.