[CALCITE-311] Wrong results when filtering the results of windowed aggregation

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Assignee: Julian Hyde
• Reporter: Vladimir Sitnikov
• Created: 25/Jun/14 19:43
• Updated: 28/Aug/18 09:56
• Resolved: 28/Aug/18 09:56

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.