Skip to content
  • gatorsmile's avatar
    adce5ee7
    [SPARK-12720][SQL] SQL Generation Support for Cube, Rollup, and Grouping Sets · adce5ee7
    gatorsmile authored
    #### What changes were proposed in this pull request?
    
    This PR is for supporting SQL generation for cube, rollup and grouping sets.
    
    For example, a query using rollup:
    ```SQL
    SELECT count(*) as cnt, key % 5, grouping_id() FROM t1 GROUP BY key % 5 WITH ROLLUP
    ```
    Original logical plan:
    ```
      Aggregate [(key#17L % cast(5 as bigint))#47L,grouping__id#46],
                [(count(1),mode=Complete,isDistinct=false) AS cnt#43L,
                 (key#17L % cast(5 as bigint))#47L AS _c1#45L,
                 grouping__id#46 AS _c2#44]
      +- Expand [List(key#17L, value#18, (key#17L % cast(5 as bigint))#47L, 0),
                 List(key#17L, value#18, null, 1)],
                [key#17L,value#18,(key#17L % cast(5 as bigint))#47L,grouping__id#46]
         +- Project [key#17L,
                     value#18,
                     (key#17L % cast(5 as bigint)) AS (key#17L % cast(5 as bigint))#47L]
            +- Subquery t1
               +- Relation[key#17L,value#18] ParquetRelation
    ```
    Converted SQL:
    ```SQL
      SELECT count( 1) AS `cnt`,
             (`t1`.`key` % CAST(5 AS BIGINT)),
             grouping_id() AS `_c2`
      FROM `default`.`t1`
      GROUP BY (`t1`.`key` % CAST(5 AS BIGINT))
      GROUPING SETS (((`t1`.`key` % CAST(5 AS BIGINT))), ())
    ```
    
    #### How was the this patch tested?
    
    Added eight test cases in `LogicalPlanToSQLSuite`.
    
    Author: gatorsmile <gatorsmile@gmail.com>
    Author: xiaoli <lixiao1983@gmail.com>
    Author: Xiao Li <xiaoli@Xiaos-MacBook-Pro.local>
    
    Closes #11283 from gatorsmile/groupingSetsToSQL.
    adce5ee7
    [SPARK-12720][SQL] SQL Generation Support for Cube, Rollup, and Grouping Sets
    gatorsmile authored
    #### What changes were proposed in this pull request?
    
    This PR is for supporting SQL generation for cube, rollup and grouping sets.
    
    For example, a query using rollup:
    ```SQL
    SELECT count(*) as cnt, key % 5, grouping_id() FROM t1 GROUP BY key % 5 WITH ROLLUP
    ```
    Original logical plan:
    ```
      Aggregate [(key#17L % cast(5 as bigint))#47L,grouping__id#46],
                [(count(1),mode=Complete,isDistinct=false) AS cnt#43L,
                 (key#17L % cast(5 as bigint))#47L AS _c1#45L,
                 grouping__id#46 AS _c2#44]
      +- Expand [List(key#17L, value#18, (key#17L % cast(5 as bigint))#47L, 0),
                 List(key#17L, value#18, null, 1)],
                [key#17L,value#18,(key#17L % cast(5 as bigint))#47L,grouping__id#46]
         +- Project [key#17L,
                     value#18,
                     (key#17L % cast(5 as bigint)) AS (key#17L % cast(5 as bigint))#47L]
            +- Subquery t1
               +- Relation[key#17L,value#18] ParquetRelation
    ```
    Converted SQL:
    ```SQL
      SELECT count( 1) AS `cnt`,
             (`t1`.`key` % CAST(5 AS BIGINT)),
             grouping_id() AS `_c2`
      FROM `default`.`t1`
      GROUP BY (`t1`.`key` % CAST(5 AS BIGINT))
      GROUPING SETS (((`t1`.`key` % CAST(5 AS BIGINT))), ())
    ```
    
    #### How was the this patch tested?
    
    Added eight test cases in `LogicalPlanToSQLSuite`.
    
    Author: gatorsmile <gatorsmile@gmail.com>
    Author: xiaoli <lixiao1983@gmail.com>
    Author: Xiao Li <xiaoli@Xiaos-MacBook-Pro.local>
    
    Closes #11283 from gatorsmile/groupingSetsToSQL.
Loading