drill-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Khurram Faraaz (JIRA)" <j...@apache.org>
Subject [jira] [Created] (DRILL-3599) Wrong results returned by LEAD(col-name, -1)
Date Tue, 04 Aug 2015 01:14:04 GMT
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)

Mime
View raw message