Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-3599

Wrong results returned by LEAD(col-name, -1)

    Details

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

      private-branch-with-new-window-funcs

      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)
      

        Attachments

          Activity

            People

            • Assignee:
              adeneche Deneche A. Hakim
              Reporter:
              khfaraaz Khurram Faraaz
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: