Bug #94251 Aggregate function result is dependent by window is defined directly or as named

Submitted: 8 Feb 2019 7:01 Modified: 17 Apr 2019 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:Windows (Windows 10 Pro)
Assigned to: CPU Architecture:x86 (AMD Athlon 64 X2)

[8 Feb 2019 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 2019 11:16] MySQL Verification Team
Hello Владислав Сокол!

Thank you for the report and feedback.

regards,
Umesh
[17 Apr 2019 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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.