[SPARK-35554] Support outer references in Aggregate

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.2.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Allison Wang
• Created: 28/May/21 04:48
• Updated: 28/May/21 04:48

Description

Support Aggregate to host outer query references. For example:

CREATE VIEW t(c1, c2) AS VALUES (0, 1), (1, 2)

-- scalar subquery example
SELECT (SELECT SUM(c1)) FROM t

-- lateral join example
SELECT * FROM t, LATERAL (SELECT SUM(c1))

Currently Spark throws this error:

org.apache.spark.sql.AnalysisException: Expressions referencing the outer query are not supported outside of WHERE/HAVING clauses:
Aggregate [sum(outer(c1#23)) AS sum(outer(spark_catalog.default.t.c1))#28L]
+- OneRowRelation

Postgres SQL test example (groupingsets.sql)

select * from (values (1),(2)) v(a) left join lateral (select v.a, four, ten, count(*) from onek group by cube(four,ten)) s on true order by v.a,four,ten

org.apache.spark.sql.AnalysisException
Expressions referencing the outer query are not supported outside of WHERE/HAVING clauses:
Aggregate [four#x, ten#x, spark_grouping_id#xL], [outer(a#x), four#x, ten#x, count(1) AS count(1)#xL]