Description:
The presence of LEAD(a, 0) OVER w1 makes the other LEAD wf calls wrong:
mysql> SELECT LEAD(a,2) over w1, LEAD(a,1) OVER w1 FROM t1e WINDOW w1 as ();
+-------------------+-------------------+
| LEAD(a,2) over w1 | LEAD(a,1) OVER w1 |
+-------------------+-------------------+
| 3 | 2 |
| 3 | 3 |
| NULL | 3 |
| NULL | NULL |
| NULL | NULL |
+-------------------+-------------------+
mysql> SELECT LEAD(a,2) over w1, LEAD(a,0) OVER w1, LEAD(a,1) OVER w1 FROM t1e WINDOW w1 as ();
+-------------------+-------------------+-------------------+
| LEAD(a,2) over w1 | LEAD(a,0) OVER w1 | LEAD(a,1) OVER w1 |
+-------------------+-------------------+-------------------+
| NULL | 1 | NULL |
| NULL | 2 | NULL |
| NULL | 3 | NULL |
| NULL | 3 | NULL |
| NULL | NULL | NULL |
+-------------------+-------------------+-------------------+
5 rows in set (0.00 sec)
How to repeat:
CREATE TABLE t1e(a int);
INSERT INTO t1e VALUES(1),(2),(3),(3),(NULL);
SELECT LEAD(a,2) over w1, LEAD(a,1) OVER w1 FROM t1e WINDOW w1 as ();
SELECT LEAD(a,2) over w1, LEAD(a,0) OVER w1, LEAD(a,1) OVER w1 FROM t1e WINDOW w1 as (); -- buggy
