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

    XMLWordPrintableJSON

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

              kwho Michael Ho
              mikeb Michael Brown
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: