Uploaded image for project: 'IMPALA'
  1. IMPALA
  2. IMPALA-4518

behavior with LEAD/LAG on string column/expression where offset goes beyond row bounds differs from postgres

    Details

      Description

      When a LEAD or LAG on an int, timestamp, or bool column or expression has an offset that exceeds the row index, the evaluated expression returns NULL. This matches what happens with Postgres.

      However, on a string column, whereas Postgres still returns NULL, Impala returns the empty string.

      [localhost:21000] > use functional;
      Query: use functional
      [localhost:21000] > select
                        > id,
                        > lead(id, 3) over (order by id) lead_id,
                        > string_col,
                        > lead(string_col, 3) over (order by id) lead_string_col
                        > from alltypestiny;
      Query: select id,
      lead(id, 3) over (order by id) lead_id,
      string_col,
      lead(string_col, 3) over (order by id) lead_string_col
      from alltypestiny
      +----+---------+------------+-----------------+
      | id | lead_id | string_col | lead_string_col |
      +----+---------+------------+-----------------+
      | 0  | 3       | 0          | 1               |
      | 1  | 4       | 1          | 0               |
      | 2  | 5       | 0          | 1               |
      | 3  | 6       | 1          | 0               |
      | 4  | 7       | 0          | 1               |
      | 5  | NULL    | 1          |                 |
      | 6  | NULL    | 0          |                 |
      | 7  | NULL    | 1          |                 |
      +----+---------+------------+-----------------+
      Fetched 8 row(s) in 0.12s
      [localhost:21000] >
      

      This happens even if the optional "default value" parameter is strictly set to NULL:

      [localhost:21000] > select lead(string_col, 99999999, null) over (order by id) col from alltypestiny;
      +-----+
      | col |
      +-----+
      |     |
      |     |
      |     |
      |     |
      |     |
      |     |
      |     |
      |     |
      +-----+
      Fetched 8 row(s) in 0.12s
      [localhost:21000] >
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                kwho Michael Ho
                Reporter:
                mikesbrown Michael Brown
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: