Bug #118912 Mismatch in Column values with SUM of IFNULL vs IFNULL of SUM

• Submitted: 28 Aug 16:24
• Reporter: ASHOKKUMAR PUDHURAJA
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8 and above, 8.0.42, 8.4.5, 9.3.0
• OS: Any
• CPU Architecture: Any

[28 Aug 16:24] ASHOKKUMAR PUDHURAJA

Description:
Execution of UNION ALL operation with two select queries there seems to be mismatch with same column with SUM of IFNULL vs IFNULL of SUM, 

Presume the amount column may be null in future, but not at present data contains any null column values

How to repeat:
Presently create a simple table structure,

CREATE TABLE table1 (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    number INT,
    amount DECIMAL(10,2)
);

INSERT INTO table1 (id, name, number, amount) VALUES
(1, 'Alice', 10, 250.75),
(2, 'Bob', 20, 500.00),
(3, 'Charlie', 30, 1250.25);

CREATE TABLE table2 (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    number INT,
    amount DECIMAL(10,2)
);

INSERT INTO table2 (id, name, number, amount) VALUES
(1, 'David', 5, 100.50),
(2, 'Eva', 15, 750.00),
(3, 'Frank', 25, 300.00);

execute the below query

(
SELECT
id,
name,
number,
SUM(IFNULL(amount, 0)) as sumofifnull,
IFNULL(SUM(amount), 0) AS ifnullsum
FROM table1
WHERE id > 0
GROUP BY id, name, number
ORDER BY id, name, number
)
UNION ALL
(
SELECT
id,
name,
number,
SUM(IFNULL(amount, 0)) as sumofifnull,
IFNULL(SUM(amount), 0) AS ifnullsum
FROM table2
WHERE id > 0
GROUP BY id, name, number
ORDER BY id, name, number
LIMIT 0, 2
);

Suggested fix:
there is some issue in computation of amount field on group by operations