| Bug #109725 | Wrong result for AVG() OVER(ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) | ||
|---|---|---|---|
| Submitted: | 20 Jan 2023 18:46 | Modified: | 23 Feb 2023 21:43 |
| Reporter: | Markus Winand | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.32 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[25 Jan 2023 7:33]
MySQL Verification Team
Hello Markus, Thank you for the report and test case. Verified as described. regards, Umesh
[23 Feb 2023 21:43]
Jon Stephens
Documented fix as follows in the MySQL 8.0.33 changelog:
AVG(...) OVER (ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) did not
return the correct result.
Closed.
Description: The below script exposes an incorrect result for AVG with the window-frame ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING. The result is wrong for the second row (it seems to return the SUM, not divided by the count). Problem does not exists in 8.0.31 or earlier and also not in 8.0.32 when using RANGE instead of ROWS. How to repeat: CREATE TABLE t ( n INTEGER ); INSERT INTO t VALUES (1), (2), (3), (4); SELECT n , SUM(n) OVER(ORDER BY n ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS "sum(n)" , COUNT(*) OVER(ORDER BY n ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS "count(n)" , AVG(n) OVER(ORDER BY n ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS "avg(n)" , SUM(n) OVER(ORDER BY n ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) / COUNT(*) OVER(ORDER BY n ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS "sum(n)/count(n)" FROM t ORDER BY n; +------+--------+----------+--------+-----------------+ | n | sum(n) | count(n) | avg(n) | sum(n)/count(n) | +------+--------+----------+--------+-----------------+ | 1 | 9 | 3 | 3.0000 | 3.0000 | | 2 | 7 | 2 | 7.0000 | 3.5000 | <-- Column "avg(n)" is wrong. | 3 | 4 | 1 | 4.0000 | 4.0000 | | 4 | NULL | 0 | NULL | NULL | +------+--------+----------+--------+-----------------+