[SPARK-30219] Support Filter expression reference the outer query

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Jiaan Geng
• Created: 11/Dec/19 10:00
• Updated: 12/Dec/22 18:10

Description

Spark SQL cannot supports a SQL with nested aggregate as below:

 

select (select count(*) filter (where outer_c <> 0)
  from (values (1)) t0(inner_c))
from (values (2),(3)) t1(outer_c);

 

And Spark will throw exception as follows:

org.apache.spark.sql.AnalysisException
Expressions referencing the outer query are not supported outside of WHERE/HAVING clauses:
Aggregate [count(1) AS count(1)#xL]
+- Project [col1#x AS inner_c#x]
 +- SubqueryAlias `t0`
 +- LocalRelation [col1#x]

But PostgreSQL supports this syntax.

 

select (select count(*) filter (where outer_c <> 0)
  from (values (1)) t0(inner_c))
from (values (2),(3)) t1(outer_c); -- outer query is aggregation query
 count 
-------
 2
(1 row)