Khurram Faraaz created DRILL-3680: ------------------------------------- Summary: window function query returns Incorrect results Key: DRILL-3680 URL: https://issues.apache.org/jira/browse/DRILL-3680 Project: Apache Drill Issue Type: Bug Components: Execution - Flow Affects Versions: 1.2.0 Environment: private-branch https://github.com/adeneche/incubator-drill/tree/new-window-funcs Reporter: Khurram Faraaz Assignee: Chris Westin Priority: Critical
Query plan from Drill for the query that returns wrong results {code} 0: jdbc:drill:schema=dfs.tmp> explain plan for select c1 , c2 , lead(c2) OVER ( PARTITION BY c2 ORDER BY c1) lead_c2 FROM (SELECT c1 , c2, ntile(3) over(PARTITION BY c2 ORDER BY c1) FROM `tblWnulls.parquet`); +------+------+ | text | json | +------+------+ | 00-00 Screen 00-01 Project(c1=[$0], c2=[$1], lead_c2=[$2]) 00-02 Project(c1=[$0], c2=[$1], lead_c2=[$2]) 00-03 Project(c1=[$0], c2=[$1], $2=[$3]) 00-04 Window(window#0=[window(partition {1} order by [0] range between UNBOUNDED PRECEDING and CURRENT ROW aggs [LEAD($1)])]) 00-05 Window(window#0=[window(partition {1} order by [0] range between UNBOUNDED PRECEDING and CURRENT ROW aggs [NTILE($2)])]) 00-06 SelectionVectorRemover 00-07 Sort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC]) 00-08 Project(c1=[$1], c2=[$0]) 00-09 Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=maprfs:///tmp/tblWnulls.parquet]], selectionRoot=maprfs:/tmp/tblWnulls.parquet, numFiles=1, columns=[`c1`, `c2`]]]) {code} Results returned by Drill. {code} 0: jdbc:drill:schema=dfs.tmp> select c1 , c2 , lead(c2) OVER ( PARTITION BY c2 ORDER BY c1) lead_c2 FROM (SELECT c1 , c2, ntile(3) over(PARTITION BY c2 ORDER BY c1) FROM `tblWnulls.parquet`); +-------------+-------+----------+ | c1 | c2 | lead_c2 | +-------------+-------+----------+ | 0 | a | null | | 1 | a | null | | 5 | a | null | | 10 | a | null | | 11 | a | null | | 14 | a | null | | 11111 | a | null | | 2 | b | null | | 9 | b | null | | 13 | b | null | | 17 | b | null | | 4 | c | null | | 6 | c | null | | 8 | c | null | | 12 | c | null | | 13 | c | null | | 13 | c | null | | null | c | null | | 10 | d | null | | 11 | d | null | | 2147483647 | d | null | | 2147483647 | d | null | | null | d | null | | null | d | null | | -1 | e | null | | 15 | e | null | | 19 | null | null | | 65536 | null | null | | 1000000 | null | null | | null | null | null | +-------------+-------+----------+ 30 rows selected (0.339 seconds) {code} Results returned by Postgres {code} postgres=# select c1 , c2 , lead(c2) OVER ( PARTITION BY c2 ORDER BY c1) lead_c2 FROM (SELECT c1 , c2, ntile(3) over(PARTITION BY c2 ORDER BY c1) FROM t222) sub_query; c1 | c2 | lead_c2 ------------+----+--------- 0 | a | a 1 | a | a 5 | a | a 10 | a | a 11 | a | a 14 | a | a 11111 | a | 2 | b | b 9 | b | b 13 | b | b 17 | b | 4 | c | c 6 | c | c 8 | c | c 12 | c | c 13 | c | c 13 | c | c | c | 10 | d | d 11 | d | d 2147483647 | d | d 2147483647 | d | d | d | d | d | -1 | e | e 15 | e | 19 | | 65536 | | 1000000 | | | | (30 rows) {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)