Description:
Verified with github's trunk 87307d4ddd88405117e3f1e51323836d57ab1f57 of today.
MIN() can be used as a window function, if given an OVER clause.
And MIN() is expected, per the SQL standard, to work like this: ignore NULL values, and return the minimum of other values (or NULL if no other value).
Please look at the query below. In the window, there is no PARTITION BY, so the partition is the entire table 't' ; rows are ordered by 'a', and to build the frame we keep only from the first row up to the current row, and take the minimum of that.
The tables contains NULL,1,2. As MySQL default's is to put NULLs first in a sort, "ORDER BY a" leads to intermediate result NULL,1,2. So for the row which has a=NULL, its frame contains NULL, and the MIN is NULL (remove NULLs from the frame, there remains nothing, so MIN is NULL). Then for the row which has a=1, its frame contains NULL and 1, and the MIN should be *1* (remove NULLs from the frame, there remains 1, so MIN should be 1). But it is, wrongly, NULL.
I tried SQL Server, PostgreSQL and DuckDB, all return the result which I expected.
Also, if I remove "ORDER BY a", MySQL returns the result which I expected.
How to repeat:
CREATE TABLE t(a INT);
INSERT INTO t VALUES (null), (1), (2);
SELECT a,MIN(a) OVER (ORDER BY a ROWS UNBOUNDED PRECEDING) FROM t;
result:
NULL NULL
1 NULL
2 NULL
Postgresql, SQL Server, DuckDB (note that for them, which support NULLS FIRST and NULLS LAST, we have to add NULLS FIRST to the ORDER BY clause in order to imitate MySQL's sort order), and MariaDB:
NULL NULL
1 1
2 1
