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: | |
| 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 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.
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)