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