[ https://issues.apache.org/jira/browse/SPARK-30219?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
jiaan.geng updated SPARK-30219: ------------------------------- Summary: Support Filter expression reference the outer query (was: Support Filter expression referencing the outer query) > Support Filter expression reference the outer query > --------------------------------------------------- > > Key: SPARK-30219 > URL: https://issues.apache.org/jira/browse/SPARK-30219 > Project: Spark > Issue Type: Sub-task > Components: SQL > Affects Versions: 3.0.0 > Reporter: jiaan.geng > Priority: Major > > Spark SQL cannot supports a SQL with nested aggregate as below: > > {code:java} > select (select count(*) filter (where outer_c <> 0) > from (values (1)) t0(inner_c)) > from (values (2),(3)) t1(outer_c);{code} > > And Spark will throw exception as follows: > {code:java} > 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]{code} > But PostgreSQL supports this syntax. > > {code:java} > 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){code} > -- This message was sent by Atlassian Jira (v8.3.4#803005) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org