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

• Submitted: 28 Aug 16:24
• Modified: 1 Sep 10:13
• Reporter: ASHOKKUMAR PUDHURAJA
• Status: Verified
• 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

[1 Sep 10:13] MySQL Verification Team

Thank you for the report.

It is ok in many cases that sum(ifnull()) is not same as ifnull(sum()) but in this case this looks like a bug as it is different in and out of union.

+----+---------+--------+-------------+-----------+
| id | name    | number | sumofifnull | ifnullsum |
+----+---------+--------+-------------+-----------+
|  1 | Alice   |     10 |      250.75 |    250.75 |
|  2 | Bob     |     20 |      500.00 |    500.00 |
|  3 | Charlie |     30 |     1250.25 |   1250.25 |
|  1 | David   |      5 |      100.50 |      0.00 |
|  2 | Eva     |     15 |      750.00 |      0.00 |
+----+---------+--------+-------------+-----------+
5 rows in set (0.003 sec)

mysql> 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;
+----+---------+--------+-------------+-----------+
| id | name    | number | sumofifnull | ifnullsum |
+----+---------+--------+-------------+-----------+
|  1 | Alice   |     10 |      250.75 |    250.75 |
|  2 | Bob     |     20 |      500.00 |    500.00 |
|  3 | Charlie |     30 |     1250.25 |   1250.25 |
+----+---------+--------+-------------+-----------+
3 rows in set (0.000 sec)

mysql> 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;
+----+-------+--------+-------------+-----------+
| id | name  | number | sumofifnull | ifnullsum |
+----+-------+--------+-------------+-----------+
|  1 | David |      5 |      100.50 |    100.50 |
|  2 | Eva   |     15 |      750.00 |    750.00 |
+----+-------+--------+-------------+-----------+
2 rows in set (0.001 sec)

mysql>