[jira] [Created] (CALCITE-6104) Aggregate function that references outer column should be evaluated in outer query

["Julian Hyde (Jira)" <ji...@apache.org>]

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)