[jira] [Commented] (CALCITE-2147) GroupingSets involving rollup resulting into an incorrect plan

["Julian Hyde (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-2147?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16334925#comment-16334925 ] 

Julian Hyde commented on CALCITE-2147:
--------------------------------------

It seems that SqlToRelConverter is not handling ROLLUP inside GROUPING SETS. It should be able to expand ROLLUP and CUBE fully before generating RelNodes. I was able to reproduce in SqlToRelConverterTest:
{noformat}
diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index d9eb8c52f..0d298a103 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -317,6 +317,13 @@ protected final void check(
     sql(sql).ok();
   }
 
+  @Test public void testGroupingSetsRollup() {
+    final String sql = "select 1\n"
+        + "from (values (0, 1, 2, 3, 4)) as t(a, b, c, x, y)\n"
+        + "group by grouping sets (rollup(a, b), c)";
+    sql(sql).ok();
+  }
+
   /** When the GROUPING function occurs with GROUP BY (effectively just one
    * grouping set), we can translate it directly to 1. */
   @Test public void testGroupingFunctionWithGroupBy() {

{noformat}

> GroupingSets involving rollup resulting into an incorrect plan
> --------------------------------------------------------------
>
>                 Key: CALCITE-2147
>                 URL: https://issues.apache.org/jira/browse/CALCITE-2147
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Vamshi
>            Assignee: Julian Hyde
>            Priority: Major
>
> Problem description:
> When rollup or cube expressions are specified as input to grouping_sets ,then instead of handling the rollup or cube expression in LogicalAggregate operator  they are handled in LogicalProject operator which is incorrect.
>  
> Basically grouping_sets with rollup/cube expressions as input is not flattened into logical aggregate operator.
>  
> Problem Test case:
> create table temp11(a integer, b integer);
> select a,b,count(*) from temp11 group by grouping sets(rollup(a,b),a,b,());
> Test Case output:
> {
>   "rels": [
>     {
>       "id": "0",
>       "relOp": "LogicalTableScan",
>       "table": [
>         "CATALOG",
>         "temp11"
>       ],
>       "inputs": []
>     },
>     {
>       "id": "1",
>       "relOp": "LogicalProject",
>       "fields": [
>         "$f0",
>         "a",
>         "b"
>       ],
>       "exprs": [
>         {
>           "op": "ROLLUP",          <<--- Incorrect expressioin in LogicalProject operator.
>           "operands": [
>             {
>               "input": 0,
>               "name": "$0"
>             },
>             {
>               "input": 1,
>               "name": "$1"
>             }
>           ]
>         },
>         {
>           "input": 0,
>           "name": "$0"
>         },
>         {
>           "input": 1,
>           "name": "$1"
>         }
>       ]
>     },
>     {
>       "id": "2",
>       "relOp": "LogicalAggregate",
>       "group": [
>         0,
>         1,
>         2
>       ],
>       "groups": [
>         [
>           0
>         ],
>         [
>           1
>         ],
>         [
>           2
>         ],
>         []
>       ],
>       "aggs": [
>         {
>           "agg": "COUNT",
>           "type": {
>             "type": "BIGINT",
>             "nullable": false
>           },
>           "distinct": false,
>           "operands": []
>         }
>       ]
>     },
>     {
>       "id": "3",
>       "relOp": "LogicalProject",
>       "fields": [
>         "a",
>         "b",
>         "EXPR$2"
>       ],
>       "exprs": [
>         {
>           "input": 1,
>           "name": "$1"
>         },
>         {
>           "input": 2,
>           "name": "$2"
>         },
>         {
>           "input": 3,
>           "name": "$3"
>         }
>       ]
>     }
>   ]
> }



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)