Khurram Faraaz created DRILL-3599:
-------------------------------------
Summary: Wrong results returned by LEAD(col-name, -1)
Key: DRILL-3599
URL: https://issues.apache.org/jira/browse/DRILL-3599
Project: Apache Drill
Issue Type: Bug
Components: Execution - Flow
Affects Versions: 1.2.0
Environment: private-branch-with-new-window-funcs
Reporter: Khurram Faraaz
Assignee: Chris Westin
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
{code}
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)
{code}
Results returned by Postgres
{code}
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)
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
