[IMPALA-4518] behavior with LEAD/LAG on string column/expression where offset goes beyond row bounds differs from postgres

• Type: Bug
• Status: Resolved
• Priority: Blocker
• Resolution: Fixed
• Affects Version/s: Impala 2.8.0
• Fix Version/s: Impala 2.8.0
• Component/s: Backend
• Labels: correctness query_generator regression
• Target Version: Impala 2.8.0
• Assignee: Michael Ho
• Reporter: Michael Brown
• Created: 22/Nov/16 18:13
• Updated: 04/Apr/17 22:41
• Resolved: 24/Nov/16 00:38

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] >

Issue Links

• relates to: IMPALA-4120 Incorrect results with LEAD() analytic function