Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-3680

window function query returns Incorrect results

    XMLWordPrintableJSON

    Details

      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)
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                jni Jinfeng Ni
                Reporter:
                khfaraaz Khurram Faraaz
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: