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)
