Bug #94251 Aggregate function result is dependent by window is defined directly or as named
Submitted: 8 Feb 7:01 Modified: 17 Apr 22:59
Reporter: Владислав Сокол Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:8.0.12, 8.0.15 OS:Microsoft Windows (Windows 10 Pro)
Assigned to: CPU Architecture:x86 (AMD Athlon 64 X2)

[8 Feb 7:01] Владислав Сокол
Description:
While using aggregate function as a window function the result is dependent by the place of the window definition.

How to repeat:
mysql> WITH RECURSIVE cte AS (
    -> SELECT 1 num
    -> UNION ALL
    -> SELECT num+1 FROM cte WHERE num < 5
    -> )
    -> SELECT num, COUNT(*) OVER (frame) cnt_named, COUNT(*) OVER (ORDER BY num DESC) cnt_direct
    -> FROM cte
    -> WINDOW frame AS (ORDER BY num DESC);
+------+-----------+------------+
| num  | cnt_named | cnt_direct |
+------+-----------+------------+
|    5 |         5 |          1 |
|    4 |         5 |          2 |
|    3 |         5 |          3 |
|    2 |         5 |          4 |
|    1 |         5 |          5 |
+------+-----------+------------+
5 rows in set (0.00 sec)
[8 Feb 11:16] Umesh Shastry
Hello Владислав Сокол!

Thank you for the report and feedback.

regards,
Umesh
[17 Apr 22:59] Jon Stephens
Documented fix as follows in the MySQL 8.0.17 changelog:

    A window without a frame specification inheriting from a window
    with an ORDER BY yielded an incorrect result.

Closed.