[jira] [Closed] (CALCITE-311) Wrong results when filtering the results of windowed aggregation

["Vladimir Sitnikov (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-311?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Vladimir Sitnikov closed CALCITE-311.
-------------------------------------
    Resolution: Fixed

Does not reproduce on current master.

I've added a test just in case https://git-wip-us.apache.org/repos/asf?p=calcite.git;a=commit;h=9f7e565ffcadd161e8a92906a493f551b9c02f2b

> 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
(v7.6.3#76005)