You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@drill.apache.org by "Khurram Faraaz (JIRA)" <ji...@apache.org> on 2015/10/08 20:06:26 UTC
[jira] [Commented] (DRILL-3599) Wrong results returned by
LEAD(col-name, -1)
[ https://issues.apache.org/jira/browse/DRILL-3599?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14949070#comment-14949070 ]
Khurram Faraaz commented on DRILL-3599:
---------------------------------------
This is fixed on Drill 1.2 master git.commit.id=eafe0a24, we now see a better error message.
{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;
Error: UNSUPPORTED_OPERATION ERROR: Function LEAD only supports (<value expression>) or (<value expression>, 1)
See Apache DRILL JIRA: DRILL-3596
[Error Id: 922e8b5e-b31c-460e-8144-2eaa03bfb62f on centos-04.qa.lab:31010] (state=,code=0)
{code}
> 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: 1.2.0
>
>
> 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)