Nullable numeric `RANGE` bounds expand to the whole partition
What happened
For a legal ascending numeric RANGE frame, a leading NULL peer group causes Dolt to use the whole partition for every row. The equivalent non-window range translation returns the correct peer and numeric-boundary results.
Environment
Dolt main (commit 92baa5e78afd013b42a62d21b05da0481031e8d0). MySQL version 8.0.43.
How to reproduce
Run the corresponding SQL in a fresh Dolt repository.
CREATE TABLE t(id INT PRIMARY KEY, k INT NULL, v INT NOT NULL);
INSERT INTO t VALUES
(1,NULL,10),(2,NULL,20),(3,1,30),(4,2,40),(5,4,50);
SELECT id, k,
SUM(v) OVER (
ORDER BY k
RANGE BETWEEN 1 PRECEDING AND CURRENT ROW
) AS total
FROM t
ORDER BY id;The window-to-non-window translation is:
SELECT o.id, o.k,
(SELECT SUM(i.v)
FROM t AS i
WHERE (o.k IS NULL AND i.k IS NULL)
OR (o.k IS NOT NULL AND i.k IS NOT NULL
AND i.k BETWEEN o.k - 1 AND o.k)) AS total
FROM t AS o
ORDER BY o.id;For a NULL current ordering value, MySQL's numeric RANGE semantics select its NULL peers. For a non-NULL current value, the translation selects the inclusive interval [k - 1, k].
Expected Result
Both forms were run against MySQL 8.0.43 and returned:
id k total
1 NULL 30
2 NULL 30
3 1 30
4 2 70
5 4 50Dolt's non-window translation returned the same rows.
Dolt actual
Dolt returned the whole-partition sum for every row:
id k total
1 NULL 150
2 NULL 150
3 1 150
4 2 150
5 4 150Source: dolthub/dolt