Bug #86534 WL#9603: LEAD(<>,0) reset results of other wf's on same window
Submitted: 1 Jun 2017 2:32 Modified: 12 Jun 2017 18:42
Reporter: Dag Wanvik Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[1 Jun 2017 2:32] Dag Wanvik
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
[24 Oct 2017 14:29] Paul DuBois
Fixed in 8.0.2.