[SPARK-29704] Support the combinations of grouping operations

• Type: Sub-task
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.0.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Takeshi Yamamuro
• Created: 01/Nov/19 04:57
• Updated: 01/Nov/19 05:38

Description

PgSQL can accept a query below with the combinations of grouping operations, but Spark cannot;

postgres=# create table gstest2 (a integer, b integer, c integer, d integer, e integer, f integer, g integer, h integer);
postgres=# insert into gstest2 values
postgres-#   (1, 1, 1, 1, 1, 1, 1, 1),
postgres-#   (1, 1, 1, 1, 1, 1, 1, 2),
postgres-#   (1, 1, 1, 1, 1, 1, 2, 2),
postgres-#   (1, 1, 1, 1, 1, 2, 2, 2),
postgres-#   (1, 1, 1, 1, 2, 2, 2, 2),
postgres-#   (1, 1, 1, 2, 2, 2, 2, 2),
postgres-#   (1, 1, 2, 2, 2, 2, 2, 2),
postgres-#   (1, 2, 2, 2, 2, 2, 2, 2),
postgres-#   (2, 2, 2, 2, 2, 2, 2, 2);
INSERT 0 9

postgres=# select a, b, c, d from gstest2 group by rollup(a,b),grouping sets(c,d);
 a | b | c | d 
---+---+---+---
 1 | 2 | 2 |  
 1 | 1 | 2 |  
 1 | 1 | 1 |  
 2 | 2 | 2 |  
 1 |   | 1 |  
 2 |   | 2 |  
 1 |   | 2 |  
   |   | 2 |  
   |   | 1 |  
 1 | 2 |   | 2
 1 | 1 |   | 2
 1 | 1 |   | 1
 2 | 2 |   | 2
 1 |   |   | 1
 2 |   |   | 2
 1 |   |   | 2
   |   |   | 2
   |   |   | 1
(18 rows)

scala> sql("""select a, b, c, d from gstest2 group by rollup(a,b),grouping sets(c,d)""").show
 org.apache.spark.sql.catalyst.parser.ParseException:
 mismatched input 'sets' expecting

{<EOF>, ',', '.', '[', 'AND', 'BETWEEN', 'CLUSTER', 'DISTRIBUTE', 'EXCEPT', 'GROUPING', 'HAVING', 'IN', 'INTERSECT', 'IS', 'LIKE', 'LIMIT', NOT, 'OR', 'ORDER', RLIKE, 'MINUS', 'SORT', 'UNION', 'WINDOW', 'WITH', EQ, '<=>', '<>', '!=', '<', LTE, '>', GTE, '+', '-', '*', '/', '%', 'DIV', '&', '|', '||', '^'}

(line 1, pos 61)

== SQL ==
 select a, b, c, d from gstest2 group by rollup(a,b),grouping sets(c,d)
 -------------------------------------------------------------^^^

at org.apache.spark.sql.catalyst.parser.ParseException.withCommand(ParseDriver.scala:268)
 at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parse(ParseDriver.scala:135)
 at org.apache.spark.sql.execution.SparkSqlParser.parse(SparkSqlParser.scala:48)
 at org.apache.spark.sql.catalyst.parser.AbstractSqlParser.parsePlan(ParseDriver.scala:85)
 at org.apache.spark.sql.SparkSession.$anonfun$sql$1(SparkSession.scala:605)
 at org.apache.spark.sql.catalyst.QueryPlanningTracker.measurePhase(QueryPlanningTracker.scala:111)
 at org.apache.spark.sql.SparkSession.sql(SparkSession.scala:605)
 ... 47 elided
 

See a doc for the form: https://www.postgresql.org/docs/current/sql-select.html