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

Wrong results when filtering the results of windowed aggregation

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      Optiq incorrectly pushes filter down window aggregation, thus window aggregates get wrong input data resulting in incorrect result.
      Here's example:

      OptiqAssert.that()
          .with(OptiqAssert.Config.REGULAR)
          .query(
              "select * from (select \"empid\", count(*) over () c\n"
              + "from \"hr\".\"emps\"\n"
              + ") where \"empid\"=100")
          .returns("empid=100; C=4\n"); // It should count(*) first, and filter later
      

      The actual result is: empid=100; C=1
      The plan is as follows:

      EnumerableCalcRel(expr#0..5=[{inputs}], empid=[$t0], $1=[$t5])
        EnumerableWindowRel(window#0=[window(partition {} order by [] range between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING aggs [COUNT()])])
          EnumerableCalcRel(expr#0..4=[{inputs}], expr#5=[CAST($t0):INTEGER NOT NULL], expr#6=[100], expr#7=[=($t5, $t6)], proj#0..4=[{exprs}], $condition=[$t7])
            EnumerableTableAccessRel(table=[[hr, emps]])
      

      I have no idea why PushFilterPastProjectRule is executed before WindowedAggSplitterRule.PROJECT.

      At best we should allow pushing filters that use expressions matching PARTITION BY expressions.
      I am not sure that is easy to do in ProjectRel_with_RexOvers form.
      I guess it would be easier if we create WindowRel first, and then have dedicated PushFilterPastWindowRel rule.

      At least we should deny PushFilterPastProjectRule when Project contains RexOver.

        Attachments

          Activity

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              vladimirsitnikov Vladimir Sitnikov
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: