[DRILL-3599] Wrong results returned by LEAD(col-name, -1)

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

Environment

private-branch-with-new-window-funcs

• Assignee: Abdel Hakim Deneche
• Reporter: Khurram Faraaz
• Created: 04/Aug/15 01:13
• Updated: 04/Nov/15 18:59
• Resolved: 08/Oct/15 18:05

Description

Query that uses LEAD(col-name,-1) returns incorrect results.

1. Should we allow this, because an offset -1 does not make sense (offset value must be a non-negative integer)
2. If we should support this, then our current results are different from those returned by Postgres.

Results returned by Drill

0: jdbc:drill:schema=dfs.tmp> select c1, lead(c1,-1) over w from union_01 window w as (partition by c3 order by c1) order by c1;
+---------+---------+
|   c1    | EXPR$1  |
+---------+---------+
| -36559  | -36559  |
| -36559  | 1224    |
| -36559  | -36559  |
| -36559  | -36559  |
| -36559  | -36559  |
| -36559  | -36559  |
| -36559  | -36559  |
| -36559  | -36559  |
| -36559  | -36559  |
| -36559  | -36559  |
| -36559  | -36559  |
| -788    | null    |
| -409    | null    |
| -168    | -121    |
| -150    | 402     |
| -146    | -1      |
| -121    | null    |
| -104    | 848     |
| -104    | -104    |
| -1      | 0       |
| 0       | 10000   |
| 0       | 0       |
| 0       | 0       |
| 160     | 160     |
| 160     | 160     |
| 160     | 160     |
| 160     | null    |
| 160     | 160     |
| 402     | 402     |
| 402     | 402     |
| 402     | 402     |
| 402     | null    |
| 402     | 402     |
| 848     | 848     |
| 848     | 848     |
| 848     | 848     |
| 848     | 848     |
| 848     | 848     |
| 848     | 848     |
| 848     | 848     |
| 848     | null    |
| 848     | 848     |
| 878     | null    |
| 1224    | 1224    |
| 1224    | 1224    |
| 1224    | 1224    |
| 1224    | 1224    |
| 1224    | 1224    |
| 1224    | 1224    |
| 1224    | null    |
| 1234    | null    |
| 1234    | 1234    |
| 1234    | 1234    |
| 1234    | 1234    |
| 1234    | 1234    |
| 1234    | 1234    |
| 1234    | 1234    |
| 1234    | 1234    |
| 10000   | 10000   |
| 10000   | null    |
| 10000   | 10000   |
| null    | null    |
| null    | null    |
| null    | null    |
| null    | null    |
+---------+---------+
65 rows selected (0.621 seconds)

Results returned by Postgres

postgres=# select c1, lead(c1,-1) over w from union_01 window w as (partition by c3 order by c1) order by c1;
   c1   |  lead  
--------+--------
 -36559 | -36559
 -36559 | -36559
 -36559 | -36559
 -36559 | -36559
 -36559 | -36559
 -36559 |       
 -36559 | -36559
 -36559 | -36559
 -36559 | -36559
 -36559 | -36559
 -36559 | -36559
   -788 |       
   -409 |       
   -168 |       
   -150 |       
   -146 |       
   -121 |   -168
   -104 |   -104
   -104 |       
     -1 |   -146
      0 |     -1
      0 |      0
      0 |      0
    160 |    160
    160 |    160
    160 |       
    160 |    160
    160 |    160
    402 |    402
    402 |    402
    402 |   -150
    402 |    402
    402 |    402
    848 |    848
    848 |   -104
    848 |    848
    848 |    848
    848 |    848
    848 |    848
    848 |    848
    848 |    848
    848 |    848
    878 |       
   1224 |   1224
   1224 |   1224
   1224 |   1224
   1224 |   1224
   1224 |   1224
   1224 | -36559
   1224 |   1224
   1234 |   1234
   1234 |       
   1234 |   1234
   1234 |   1234
   1234 |   1234
   1234 |   1234
   1234 |   1234
   1234 |   1234
  10000 |  10000
  10000 |  10000
  10000 |      0
        |       
        |       
        |  10000
        |       
(65 rows)