Uploaded image for project: 'Spark'
  1. Spark
  2. SPARK-27764 Feature Parity between PostgreSQL and Spark
  3. SPARK-29703

Support grouping() in GROUP BY without GroupingSets/Cube/Rollup

    XMLWordPrintableJSON

Details

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

    Description

      PgSQL can accept the query below that have grouping() in GROUP BY without GroupingSets/Cube/Rollup;

      postgres=# CREATE TABLE onek (unique1 int, unique2 int, two int, four int, ten int, twenty int, hundred int, thousand int, twothousand int, fivethous int, tenthous int, odd int, even int, textu1 text, textu2 text, text4 text);
      CREATE TABLE
      
      postgres=# select ten, grouping(ten) from onek group by (ten) having grouping(ten) >= 0 order by 2,1;
       ten | grouping 
      -----+----------
      (0 rows)
      
      scala> sql("""select ten, grouping(ten) from onek group by (ten) having grouping(ten) >= 0 order by 2,1""").show()
      org.apache.spark.sql.AnalysisException: grouping()/grouping_id() can only be used with GroupingSets/Cube/Rollup;
        at org.apache.spark.sql.catalyst.analysis.CheckAnalysis.failAnalysis(CheckAnalysis.scala:47)
        at org.apache.spark.sql.catalyst.analysis.CheckAnalysis.failAnalysis$(CheckAnalysis.scala:46)
        at org.apache.spark.sql.catalyst.analysis.Analyzer.failAnalysis(Analyzer.scala:122)
        at org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveGroupingAnalytics$$anonfun$org$apache$spark$sql$catalyst$analysis$Analyzer$ResolveGroupingAnalytics$$findGroupingExprs$1.applyOrElse(Analyzer.scala:503)
        at org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveGroupingAnalytics$$anonfun$org$apache$spark$sql$catalyst$analysis$Analyzer$ResolveGroupingAnalytics$$findGroupingExprs$1.applyOrElse(Analyzer.scala:497)
        at scala.PartialFunction$Lifted.apply(PartialFunction.scala:228)
        at scala.PartialFunction$Lifted.apply(PartialFunction.scala:224)
        at org.apache.spark.sql.catalyst.trees.TreeNode.collectFirst(TreeNode.scala:202)
        at org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveGroupingAnalytics$.org$apache$spark$sql$catalyst$analysis$Analyzer$ResolveGroupingAnalytics$$findGroupingExprs(Analyzer.scala:497)
      

      Attachments

        Activity

          People

            Unassigned Unassigned
            maropu Takeshi Yamamuro
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated: