Victoria Markman created DRILL-3567: ---------------------------------------
Summary: Wrong result in a query with multiple window functions and different over clauses Key: DRILL-3567 URL: https://issues.apache.org/jira/browse/DRILL-3567 Project: Apache Drill Issue Type: Bug Components: Query Planning & Optimization Affects Versions: 1.1.0 Environment: private-branch-with-multiple-partitions-enabled Reporter: Victoria Markman Assignee: Jinfeng Ni Priority: Critical {code} 0: jdbc:drill:drillbit=localhost> select * from t1; +-------+--------+-------------+ | a1 | b1 | c1 | +-------+--------+-------------+ | 1 | aaaaa | 2015-01-01 | | 2 | bbbbb | 2015-01-02 | | 3 | ccccc | 2015-01-03 | | 4 | null | 2015-01-04 | | 5 | eeeee | 2015-01-05 | | 6 | fffff | 2015-01-06 | | 7 | ggggg | 2015-01-07 | | null | hhhhh | 2015-01-08 | | 9 | iiiii | null | | 10 | jjjjj | 2015-01-10 | +-------+--------+-------------+ 10 rows selected (0.078 seconds) {code} Wrong result, columns are projected in the wrong order: {code} 0: jdbc:drill:drillbit=localhost> select . . . . . . . . . . . . . . . . > count(*) over(partition by b1 order by c1) as count1, . . . . . . . . . . . . . . . . > count(*) over(partition by a1 order by c1) as count2, . . . . . . . . . . . . . . . . > sum(a1) over(partition by b1 order by c1) as sum1 . . . . . . . . . . . . . . . . > from . . . . . . . . . . . . . . . . > t1; +---------+---------+-------+ | count1 | count2 | sum1 | +---------+---------+-------+ | 1 | 1 | 1 | | 1 | 2 | 1 | | 1 | 3 | 1 | | 1 | 4 | 1 | | 1 | 5 | 1 | | 1 | 6 | 1 | | 1 | 7 | 1 | | 1 | 9 | 1 | | 1 | 10 | 1 | | 1 | null | 1 | +---------+---------+-------+ 10 rows selected (0.113 seconds) {code} Explain plan: {code} 0: jdbc:drill:drillbit=localhost> explain plan for select . . . . . . . . . . . . . . . . > count(*) over(partition by b1 order by c1) as count1, . . . . . . . . . . . . . . . . > count(*) over(partition by a1 order by c1) as count2, . . . . . . . . . . . . . . . . > sum(a1) over(partition by b1 order by c1) as sum1 . . . . . . . . . . . . . . . . > from . . . . . . . . . . . . . . . . > t1; +------+------+ | text | json | +------+------+ | 00-00 Screen 00-01 ProjectAllowDup(count1=[$0], count2=[$1], sum1=[$2]) 00-02 Project(w0$o0=[$4], w0$o1=[$5], w1$o0=[$6]) 00-03 Window(window#0=[window(partition {3} order by [2] range between UNBOUNDED PRECEDING and CURRENT ROW aggs [COUNT()])]) 00-04 SelectionVectorRemover 00-05 Sort(sort0=[$3], sort1=[$2], dir0=[ASC], dir1=[ASC]) 00-06 Window(window#0=[window(partition {1} order by [2] range between UNBOUNDED PRECEDING and CURRENT ROW aggs [COUNT(), SUM($3)])]) 00-07 SelectionVectorRemover 00-08 Sort(sort0=[$1], sort1=[$2], dir0=[ASC], dir1=[ASC]) 00-09 Project(T61¦¦*=[$0], b1=[$1], c1=[$2], a1=[$3]) 00-10 Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=file:/Users/vmarkman/drill/testdata/subqueries/t1]], selectionRoot=file:/Users/vmarkman/drill/testdata/subqueries/t1, numFiles=1, columns=[`*`]]]) {code} If you remove frame that is not the same as other two, query works correctly: {code} 0: jdbc:drill:drillbit=localhost> select . . . . . . . . . . . . . . . . > count(*) over(partition by b1 order by c1) as count1, . . . . . . . . . . . . . . . . > sum(a1) over(partition by b1 order by c1) as sum1 . . . . . . . . . . . . . . . . > from . . . . . . . . . . . . . . . . > t1; +---------+-------+ | count1 | sum1 | +---------+-------+ | 1 | 1 | | 1 | 2 | | 1 | 3 | | 1 | 5 | | 1 | 6 | | 1 | 7 | | 1 | null | | 1 | 9 | | 1 | 10 | | 1 | 4 | +---------+-------+ 10 rows selected (0.099 seconds) {code} and in the different order (just for fun) : {code} 0: jdbc:drill:drillbit=localhost> select . . . . . . . . . . . . . . . . > sum(a1) over(partition by b1 order by c1) as sum1, . . . . . . . . . . . . . . . . > count(*) over(partition by b1 order by c1) as count1 . . . . . . . . . . . . . . . . > from . . . . . . . . . . . . . . . . > t1; +-------+---------+ | sum1 | count1 | +-------+---------+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 5 | 1 | | 6 | 1 | | 7 | 1 | | null | 1 | | 9 | 1 | | 10 | 1 | | 4 | 1 | +-------+---------+ 10 rows selected (0.096 seconds) {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)