Description:
URL: https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lead
=====
The MySQL docs entry for the [`LEAD` window function](https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lead) includes the following example description:
> For example, if `N` is `3`, the return value is `default` for the last two rows.
Shouldn't it be the last *three* rows? If the current row is the third to last row in a partition, then it is not possible to look three rows ahead for a leading value, thus resulting in the `default` value being used for this third to last row (as well as the second to last and actual last rows as well ... hence three rows and not two).
The sample [sales](https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html) data set provided in the docs can illustrate what I mean.
The query
```sql
SELECT
S.*,
LEAD(S.profit, 3, -1) OVER(PARTITION BY S.country ORDER BY S.profit DESC) AS 'lead'
FROM
sales S;
```
produces the following result set:
```
+------+---------+------------+--------+------+
| year | country | product | profit | lead |
+------+---------+------------+--------+------+
| 2000 | Finland | Computer | 1500 | -1 |
| 2000 | Finland | Phone | 100 | -1 |
| 2001 | Finland | Phone | 10 | -1 |
| 2000 | India | Computer | 1200 | -1 |
| 2000 | India | Calculator | 75 | -1 |
| 2000 | India | Calculator | 75 | -1 |
| 2001 | USA | Computer | 1500 | 150 |
| 2000 | USA | Computer | 1500 | 100 |
| 2001 | USA | Computer | 1200 | 75 |
| 2001 | USA | TV | 150 | 50 |
| 2001 | USA | TV | 100 | -1 |
| 2000 | USA | Calculator | 75 | -1 |
| 2001 | USA | Calculator | 50 | -1 |
+------+---------+------------+--------+------+
```
The `lead` entries for the `Finland` and `India` `country` partitions all have `-1`, the assigned `default` value, when `N` is `3`. *All* partitions have `-1` as the `lead` value for the last `3` rows of each partition.
The same "issue" appears in the documentation of the `LAG()` window function (i.e., if `N` is `3`, then the return value is `default` for the first *three* rows, not two).
How to repeat:
See description.
Suggested fix:
See description.
