[jira] [Commented] (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:comment-tabpanel&focusedCommentId=14233026#comment-14233026 ] 

Vladimir Sitnikov commented on CALCITE-311:
-------------------------------------------

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|https://github.com/vlsi/optiq/commit/fe7ecd2423bd6cb819d0191675d4a248f0b2712e]

> 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)