[SPARK-29705] Support more expressive forms in GroupingSets/Cube/Rollup

• 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 05:03
• Updated: 01/Nov/19 05:38

Description

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

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, grouping(a,b), sum(c), count(*), max(c) from gstest2 group by rollup ((a,b,c),(c,d));
 a | b | grouping | sum | count | max 
---+---+----------+-----+-------+-----
   |   |        3 |  24 |    18 |   2
 1 | 1 |        0 |   4 |     2 |   2
 1 | 2 |        0 |   4 |     2 |   2
 1 | 1 |        0 |   2 |     2 |   1
 2 | 2 |        0 |   4 |     2 |   2
 1 | 1 |        0 |  10 |    10 |   1
 1 | 2 |        0 |   4 |     2 |   2
 1 | 1 |        0 |  12 |    12 |   1
 1 | 1 |        0 |   4 |     2 |   2
 2 | 2 |        0 |   4 |     2 |   2
(10 rows)

scala> sql("""select a, b, grouping(a,b), sum(c), count(*), max(c) from gstest2 group by rollup ((a,b,c),(c,d))""").show
org.apache.spark.sql.AnalysisException: Invalid number of arguments for function grouping. Expected: 1; Found: 2; line 1 pos 13
  at org.apache.spark.sql.catalyst.analysis.FunctionRegistry$.$anonfun$expression$8(FunctionRegistry.scala:614)
  at scala.Option.getOrElse(Option.scala:189)
  at org.apache.spark.sql.catalyst.analysis.FunctionRegistry$.$anonfun$expression$4(FunctionRegistry.scala:598)
  at org.apache.spark.sql.catalyst.analysis.SimpleFunctionRegistry.lookupFunction(FunctionRegistry.scala:121)
  at org.apache.spark.sql.catalyst.catalog.SessionCatalog.lookupFunction(SessionCatalog.scala:1375)
  at org.apache.spark.sql.hive.HiveSessionCatalog.super$lookupFunction(HiveSessionCatalog.scala:132)
  at org.apache.spark.sql.hive.HiveSessionCatalog.$anonfun$lookupFunction0$2(HiveSessionCatalog.scala:132)
  at scala.util.Try$.apply(Try.scala:213)