[
https://issues.apache.org/jira/browse/CALCITE-6104?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18091739#comment-18091739
]
Yu Xu commented on CALCITE-6104:
--------------------------------
I take it first.
> 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
> Assignee: Yu Xu
> Priority: Major
>
> 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)
