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

Wrong results when filtering the results of windowed aggregation

    Details

    • Type: Bug
    • Status: Open
    • Priority: Critical
    • Resolution: Unresolved
    • 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.

        Activity

        Hide
        vladimirsitnikov Vladimir Sitnikov added a comment -

        Here's still non-merged pull request on the subject: https://github.com/julianhyde/optiq/pull/307.
        The interesting part there is Trim non-required fields before WindowRel

        Show
        vladimirsitnikov Vladimir Sitnikov added a comment - Here's still non-merged pull request on the subject: https://github.com/julianhyde/optiq/pull/307 . The interesting part there is Trim non-required fields before WindowRel
        Hide
        vladimirsitnikov Vladimir Sitnikov added a comment -

        Proper fix should put additional checks in RexProgramBuilder.mergePrograms and may be some other RexProgramBuilder, RexProgram methods to avoid such kind of "optimizations".

        Show
        vladimirsitnikov Vladimir Sitnikov added a comment - Proper fix should put additional checks in RexProgramBuilder.mergePrograms and may be some other RexProgramBuilder , RexProgram methods to avoid such kind of "optimizations".

          People

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

            Dates

            • Created:
              Updated:

              Development