dolthub / dolthub/dolt

Dolt `LAST_VALUE` with an explicit `RANGE` peer frame mishandles a `NULL` order key.

Open
#11,398 0 comments 0 reactions 1 assignee Claimed by @elianddb View on GitHub
bug correctness customer issue good repro sql
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.