[DRILL-3680] window function query returns Incorrect results

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 1.2.0
• Fix Version/s: 1.2.0
• Component/s: Execution - Flow
• Labels: window_function

Environment

private-branch https://github.com/adeneche/incubator-drill/tree/new-window-funcs

• Assignee: Jinfeng Ni
• Reporter: Khurram Faraaz
• Created: 20/Aug/15 22:03
• Updated: 16/Sep/15 00:35
• Resolved: 16/Sep/15 00:20

Description

Query plan from Drill for the query that returns wrong results

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

Results returned by Drill.

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)

Results returned by Postgres

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)

Issue Links

• duplicates: DRILL-3679 IOB Exception : when window functions used in outer and inner query