Details

    • Sub-task
    • Status: Open
    • Major
    • Resolution: Unresolved
    • 3.1.0
    • None
    • SQL
    • None

    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)

       

      Attachments

        Activity

          gurwls223 Hyukjin Kwon added a comment - - edited

          beliefer check the comments in its parent JIRA.

          gurwls223 Hyukjin Kwon added a comment - - edited beliefer check the comments in its parent JIRA.
          beliefer Jiaan Geng added a comment -

          hyukjin.kwon Thank! I see.

          beliefer Jiaan Geng added a comment - hyukjin.kwon  Thank! I see.

          People

            Unassigned Unassigned
            beliefer Jiaan Geng
            Votes:
            1 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated: