Bug #120022 CAST(AVG()) returns a different result than CAST(SUM()/COUNT()) on DECIMAL values

• Submitted: 11 Mar 2:22
• Modified: 11 Mar 7:10
• Reporter: Wang Ojiken
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: MySQL 8.0.28
• OS: Any
• CPU Architecture: Any
• Tags: aggregate-function, avg, cast, type-conversion

[11 Mar 2:22] Wang Ojiken

Description:
The result of CAST(AVG(expr) AS SIGNED) differs from the mathematically equivalent expression CAST(SUM(expr)/COUNT(expr) AS SIGNED) when applied to a DECIMAL column.

According to the definition of AVG(expr), it should be equivalent to SUM(expr)/COUNT(expr). However, the results differ after applying CAST(... AS SIGNED).

This suggests that AVG() and SUM()/COUNT() may be handled differently during type conversion or rounding.

How to repeat:
DROP TABLE IF EXISTS t0;

CREATE TABLE t0
(
    c0 FLOAT UNSIGNED ZEROFILL NULL,
    c1 DECIMAL UNSIGNED ZEROFILL NULL,
    UNIQUE (c1)
);

INSERT INTO t0 (c0, c1) VALUES (NULL, 0);
INSERT INTO t0 (c0, c1) VALUES (0.334623, 1);

CREATE INDEX i0 ON t0 (c1, c0);

SELECT CAST(AVG(t0.c1) AS SIGNED) FROM t0;

SELECT CAST((SUM(t0.c1) / COUNT(t0.c1)) AS SIGNED) FROM t0;

SELECT CAST(0.5000 AS SIGNED);

Result:

SELECT CAST(AVG(c1) AS SIGNED) FROM t0;
-> 0

SELECT CAST((SUM(c1)/COUNT(c1)) AS SIGNED) FROM t0;
-> 1

SELECT CAST(0.5000 AS SIGNED);
-> 1

Suggested fix:
AVG(expr) should behave consistently with SUM(expr)/COUNT(expr) when casting to SIGNED. The internal handling of AVG() may need to align with the equivalent expression.

[11 Mar 2:37] Wang Ojiken

The main point I want to highlight is the behavior of AVG() under CAST. 
In this example, CAST(0.5000 AS SIGNED) returns 1, while CAST(AVG(c1) AS SIGNED) returns 0.

The expression CAST(SUM(c1)/COUNT(c1) AS SIGNED) is provided only to demonstrate the difference between AVG() and its mathematically equivalent formulation. The primary observation is that AVG(c1) behaves differently from the literal value 0.5000 when cast to SIGNED.

[11 Mar 7:10] Roy Lyseng

Thank you for the bug report.
Verified as described.