drill-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Khurram Faraaz (JIRA)" <j...@apache.org>
Subject [jira] [Resolved] (DRILL-3599) Wrong results returned by LEAD(col-name, -1)
Date Thu, 08 Oct 2015 18:06:26 GMT

     [ https://issues.apache.org/jira/browse/DRILL-3599?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Khurram Faraaz resolved DRILL-3599.
-----------------------------------
    Resolution: Fixed

> 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
>             Fix For: Future
>
>
> 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)

Mime
View raw message