Dolt `LAST_VALUE` with an explicit `RANGE` peer frame mishandles a `NULL` order key.
- Dominant language
- Go
- Stars
- 24.4k
- Forks
- 873
- Avg merge
- 1d 8h
- Merged PRs (30d)
- 120
Description
## What happened
Dolt `LAST_VALUE` with an explicit ASC `RANGE` peer frame mishandles a `NULL` order key.
The `NULL` row should be its own order peer for `RANGE BETWEEN CURRENT ROW AND CURRENT ROW`, but Dolt returns the value from the non-`NULL` row instead.
MySQL 8.0.43 confirms the expected behavior. For this two-row table, `FIRST_VALUE(v)` over `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` returns `10, 10`, because `ORDER BY k ASC` places `NULL` first and the `k = 1` running frame includes the preceding `NULL` row. That part of Dolt's behavior is not a bug. The bug is the explicit peer-frame `LAST_VALUE` result for the `NULL` row.
## Environment
Dolt main at commit `4e31c4cc65ad5cb3b725d51e3b693a23b756f2e1` (`dolt version 2.2.3`).
## How to reproduce
In a fresh Dolt repository, run:
```sql
CREATE TABLE t(id INT PRIMARY KEY, g INT, k INT NULL, v INT NOT NULL);
INSERT INTO t VALUES (1,0,NULL,10),(2,0,1,20);
SELECT id,
FIRST_VALUE(v) OVER (
PARTITION BY g ORDER BY k ASC
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS wf
FROM t ORDER BY id;
SELECT id,
LAST_VALUE(v) OVER (
PARTITION BY g ORDER BY k ASC
RANGE BETWEEN CURRENT ROW AND CURRENT ROW
) AS wf
FROM t ORDER BY id;
```
## Expected result
For the `FIRST_VALUE` running-frame control, the expected output is:
| id | wf |
| --- | --- |
| 1 | 10 |
| 2 | 10 |
For the `LAST_VALUE` peer frame, the expected output is:
| id | wf |
| --- | --- |
| 1 | 10 |
| 2 | 20 |
## Actual result
Actual on Dolt current main for the `FIRST_VALUE` running frame matches MySQL:
| id | wf |
| --- | --- |
| 1 | 10 |
| 2 | 10 |
Actual on Dolt current main for the `LAST_VALUE` peer frame is incorrect:
| id | wf |
| --- | --- |
| 1 | 20 |
| 2 | 20 |
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.