Bug #87939 Combining CTE and Window Function gives wrong result
Submitted: 2 Oct 2017 9:00 Modified: 5 Oct 2017 16:40
Reporter: Bernt Marius Johnsen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:8.0.3 OS:Any
Assigned to: CPU Architecture:Any

[2 Oct 2017 9:00] Bernt Marius Johnsen
Description:
mysql> insert into t1 values (1),(1),(2);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> 
mysql> select * from
    ->     (select lead(i) over w as a, i as b from t1
    ->      window w as
    ->      (order by i rows current row))
    -> as t where a = b;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

This was expected, but

mysql> with t2(i) as (select i+1 from t1)
    -> select * from
    ->     (select lead(i) over w as a, i as b from t2
    ->      window w as
    ->      (order by i rows current row))
    -> as t3 where a = b; 
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
|    3 |    3 |
+------+------+
2 rows in set (0.00 sec)

Is obviously wong.

How to repeat:
create table t1 (i integer);
insert into t1 values (1),(1),(2);

select * from
    (select lead(i) over w as a, i as b from t1
     window w as
     (order by i rows current row))
as t where a = b;

with t2(i) as (select i+1 from t1)
select * from
    (select lead(i) over w as a, i as b from t2
     window w as
     (order by i rows current row))
as t3 where a = b;
[5 Oct 2017 16:40] Paul DuBois
Posted by developer:
 
Fixed in 8.0.4.

Queries with a common table expression and a derived table or view
that contained a window function produced incorrect results.
[10 Oct 2018 12:57] Jimy MARCHAND
I have this problem on 8.0.11