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
• Status: Closed
• Category: MySQL Server: DML
• Severity: S2 (Serious)
• Version: 8.0.3
• OS: Any
• 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