[
https://issues.apache.org/jira/browse/CALCITE-311?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14233201#comment-14233201
]
Vladimir Sitnikov commented on CALCITE-311:
-------------------------------------------
Proper fix should put additional checks in {{RexProgramBuilder.mergePrograms}}
and may be some other {{RexProgramBuilder}}, {{RexProgram}} methods to avoid
such kind of "optimizations".
> Wrong results when filtering the results of windowed aggregation
> ----------------------------------------------------------------
>
> Key: CALCITE-311
> URL: https://issues.apache.org/jira/browse/CALCITE-311
> Project: Calcite
> Issue Type: Bug
> Reporter: Vladimir Sitnikov
> Assignee: Julian Hyde
> Priority: Critical
>
> Optiq incorrectly pushes filter down window aggregation, thus window
> aggregates get wrong input data resulting in incorrect result.
> Here's example:
> {code:java}
> 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
> {code}
> The actual result is: {{empid=100; C=1}}
> The plan is as follows:
> {noformat}
> 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]])
> {noformat}
> 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}}.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
