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

AggregateJoinTransposeRule fails to aggregate over unique column

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.15.0
    • Fix Version/s: 1.16.0
    • Component/s: core
    • Labels:
      None

      Description

      The following query, in which "A.sal" is unique,

      select sum(A.sal)
      from (select distinct sal from sales.emp) as A
      join sales.emp as B on A.sal=B.sal
      

       causes AggregateJoinTransposeRule to fail with message

      java.lang.AssertionError: type mismatch:
      aggCall type:
      INTEGER
      inferred type:
      BIGINT
      

      Apparently, this is a bug in the rule when `unique` is true on the A side, in which case the rule does not aggregate on the A side, `leftSubTotal==null`, causing `splitter.topSplit()` to only sum over `count()` coming from the B side.

      A solution would be to introduce `splitter.singleton()` on the A side, so that it can be fed to topSplit() to be multiplied by the count.

      In the case that the `unique` side does not contain the column of an agg call, it seems that we should do `other_singleton()` on this side, and feed it to topSplit(). However, realize that the `other()` expression is necessarily a `count()`, or a scalar function of `count()`, because it does not depend on any column values. In the same way, the proposed `other_singleton()` necessarily returns 1, or some constant value. `topSplit()` would not have any need of that constant value.Therefore in this case, we don't need a split on this side, just leave its subtotal as null.

       

      I'm working on a pull-request based on these understandings. BTW, is there a reference to the algorithm used in the code? I can only find some old papers that don't exactly cover the logic of the code. Thanks.

        Attachments

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              zhong.j.yu Zhong Yu
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: