Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-4665

Allow Aggregate.groupSet to contain columns not in any of the groupSets

    XMLWordPrintableJSON

Details

    Description

      Allow Aggregate.groupSet to contain columns not in any of the groupSets.

      Rationale: even though this is not valid SQL (except in Hive SQL, which has a strange GROUPING SETS syntax) this pattern crops up when applying rules (e.g. pushing a Filter into an Aggregate with grouping sets) and it is best to handle it explicitly.

      When group key is a strict superset of the union of the groupKeys, RelToSqlConverter currently generates the wrong SQL.

      The UT:

      private RelBuilder example5(RelBuilder builder) {
        return builder
            .scan("EMP")
            .aggregate(builder.groupKey(ImmutableBitSet.of(0, 1 ,2),
                (Iterable<ImmutableBitSet>)
                    ImmutableList.of(ImmutableBitSet.of(0,1),ImmutableBitSet.of(0))),
                builder.count(false, "C"),
                builder.sum(false, "S", builder.field("SAL"))).filter(
                builder.call(SqlStdOperatorTable.GREATER_THAN, builder.field("C"),
                    builder.literal(10)))
            .filter(builder.call(SqlStdOperatorTable.EQUALS,builder.field("JOB"),builder.literal("DEVELOP")))
            .project(builder.field("JOB"));
      }
      

      The RelNode will generate the wrong Sql:

      SELECT "JOB"
      FROM (SELECT "EMPNO", "ENAME", "JOB", COUNT(*) AS "C", SUM("SAL") AS "S"
      FROM "scott"."EMP"
      GROUP BY GROUPING SETS(("EMPNO", "ENAME"), "EMPNO")
      HAVING COUNT(*) > 10) AS "t0"
      WHERE "JOB" = 'DEVELOP'
      

       

      Attachments

        1. 捕获.PNG
          29 kB
          xiong duan

        Issue Links

          Activity

            People

              nobigo xiong duan
              jiajunbernoulli Jiajun Xie
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0h
                  0h
                  Logged:
                  Time Spent - 5h
                  5h