[jira] [Created] (OPTIQ-311) Wrong results when filtering the results of windowed aggregation

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

Vladimir Sitnikov created OPTIQ-311:
---------------------------------------

             Summary: Wrong results when filtering the results of windowed aggregation
                 Key: OPTIQ-311
                 URL: https://issues.apache.org/jira/browse/OPTIQ-311
             Project: optiq
          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.2#6252)