Description:
Hi,
We've obeserved an issue where the `MAX()` window function incorrectly returns `NULL` for the first row returned, even when its window frame contains one or more rows.
This bug triggers when all of the following conditions are met:
1. The `MAX()` function is used as a window function (e.g., `MAX(column) OVER ( ... )`).
2. The window frame start is defined using `N FOLLOWING` (e.g., `ROWS BETWEEN 1 FOLLOWING AND ...`).
3. The window frame is ordered by the same expression used in the `MAX()` argument, differing only in syntax (e.g., aliases or table references).
4. The window frame is ordered in descending order (e.g., `ORDER BY column DESC`).
How to repeat:
**Minimized Query to Reproduce**:
```sql
CREATE TABLE t0 (c0 INT);
INSERT INTO t0 VALUES (1), (2);
SELECT c0, MAX(c0) OVER (ORDER BY c0 DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) FROM t0;
```
**Expected Result**:
```
mysql> SELECT c0, MAX(c0 + 0) OVER (ORDER BY c0 DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) FROM t0;
+------+----------------------------------------------------------------------------+
| c0 | MAX(c0+0) OVER (ORDER BY c0 DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) |
+------+----------------------------------------------------------------------------+
| 2 | 1 |
| 1 | NULL |
+------+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
```
The first row's window frame consists of one row with `c0 = 1`, so `MAX()` should return `1`. (Note `MAX(c0 + 0)` while keeping `c0` for the `ORDER BY` clause to force the correct result, avoiding the third condition mentioned.)
**Actual result** (MySQL, tested on version 8.4.3 and 9.1.0):
```
mysql> SELECT c0, MAX(c0) OVER (ORDER BY c0 DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) FROM t0;
+------+--------------------------------------------------------------------------+
| c0 | MAX(c0) OVER (ORDER BY c0 DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) |
+------+--------------------------------------------------------------------------+
| 2 | NULL |
| 1 | NULL |
+------+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)
```
However, `MAX()` incorrectly returned `NULL` instead of `1`, even though there was a row to evaluate in its window frame.
Optionally, you can verify this in other ways, e.g., by checking the number of rows within each window frame:
```sql
mysql> SELECT c0, COUNT(*) OVER (ORDER BY c0 DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS cnt FROM
t0;
+------+-----+
| c0 | cnt |
+------+-----+
| 2 | 1 |
| 1 | 0 |
+------+-----+
2 rows in set (0.00 sec)
```
Or by using `MIN()` instead of `MAX()` (only in this case, since there is only one row is in the frame):
```sql
mysql> SELECT c0, MIN(c0) OVER (ORDER BY c0 DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS min FROM t
0;
+------+------+
| c0 | min |
+------+------+
| 2 | 1 |
| 1 | NULL |
+------+------+
2 rows in set (0.00 sec)
```
Or by using other window functions to confirm the expected behavior:
```sql
mysql> SELECT c0, AVG(c0) OVER (ORDER BY c0 DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS avg FROM t
0;
+------+--------+
| c0 | avg |
+------+--------+
| 2 | 1.0000 |
| 1 | NULL |
+------+--------+
2 rows in set (0.01 sec)
mysql> SELECT c0, SUM(c0) OVER (ORDER BY c0 DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS sum FROM t
0;
+------+------+
| c0 | sum |
+------+------+
| 2 | 1 |
| 1 | NULL |
+------+------+
2 rows in set (0.00 sec)
mysql> SELECT c0, FIRST_VALUE(c0) OVER (ORDER BY c0 DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS firstvalue FROM t0;
+------+------------+
| c0 | firstvalue |
+------+------------+
| 2 | 1 |
| 1 | NULL |
+------+------------+
2 rows in set (0.00 sec)
mysql> SELECT c0, LAST_VALUE(c0) OVER (ORDER BY c0 DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS lastvalue FROM t0;
+------+-----------+
| c0 | lastvalue |
+------+-----------+
| 2 | 1 |
| 1 | NULL |
+------+-----------+
2 rows in set (0.00 sec)
mysql> SELECT c0, NTH_VALUE(c0,1) OVER (ORDER BY c0 DESC ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS nth FROM t0;
+------+------+
| c0 | nth |
+------+------+
| 2 | 1 |
| 1 | NULL |
+------+------+
2 rows in set (0.00 sec)
```
Suggested fix:
We believe this issue is related to optimization logic handling these specific window function conditions.
We suggest reviewing the code related to `MAX()` as a window function to ensure it correctly handles the first row in such cases.
Thank you for your time and attention to this matter. We look forward to your response.
Best regards,
