Julian Hyde created CALCITE-6104: ------------------------------------ Summary: Aggregate function that references outer column should be evaluated in outer query Key: CALCITE-6104 URL: https://issues.apache.org/jira/browse/CALCITE-6104 Project: Calcite Issue Type: Improvement Reporter: Julian Hyde
Aggregate function that references outer column should be evaluated in outer query. For example, {code} WITH aa AS (SELECT 1 AS a UNION ALL SELECT 2 UNION ALL SELECT 3), xx AS (SELECT 10 AS x UNION ALL SELECT 20 UNION ALL SELECT 30) SELECT (SELECT sum(a) FROM xx LIMIT 1) AS sa FROM aa; # Should return sa -- 6 # Currently returns sa -- 3 6 9 {code} Because {{sum(a)}} references a column from {{aa}} (and no columns from {{{}xx{}}}), it should be (per the SQL standard) evaluated in the context of the outer query. Postgres, MySQL and DuckDB have behavior consistent with the standard. Calcite and BigQuery do not. A [blog post|https://buttondown.email/jaffray/archive/sql-scoping-is-surprisingly-subtle-and-semantic/] describes in more detail. -- This message was sent by Atlassian Jira (v8.20.10#820010)