Bug #101175 COALESCE and IFNULL do not work as expected in WITH ROLLUP queries
Submitted: 14 Oct 2020 18:12 Modified: 17 Feb 11:11
Reporter: Jeff Van Boxtel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.21 OS:Ubuntu (Ver 8.0.21-0ubuntu0.20.04.4)
Assigned to: CPU Architecture:Any
Tags: COALESCE, null, rollup

[14 Oct 2020 18:12] Jeff Van Boxtel
Description:
When using COALESCE on columns that are also in the GROUP BY of a query WITH ROLLUP, an incorrect value is displayed.

This bug does not appear to exist on MySQL 5.7.31-0ubuntu0.16.04.1 . I only noticed it after we upgraded to MySQL 8.0 .

How to repeat:
drop temporary table if exists bug_test;
create temporary table bug_test (
	a int null, b int null, c int null
);
insert into bug_test values (1, 4, 10);
insert into bug_test values (2, 5, 20);
insert into bug_test values (3, 6, 30);
insert into bug_test values (NULL, 7, 40);

# roll works as expected
select a, b, SUM(c) from bug_test
group by a, b
with rollup;

+------+------+--------+
| a    | b    | SUM(c) |
+------+------+--------+
| NULL |    7 |     40 |
| NULL | NULL |     40 |
|    1 |    4 |     10 |
|    1 | NULL |     10 |
|    2 |    5 |     20 |
|    2 | NULL |     20 |
|    3 |    6 |     30 |
|    3 | NULL |     30 |
| NULL | NULL |    100 |
+------+------+--------+

# coalesce does not
select COALESCE(a, b), SUM(c) from bug_test
group by a, b
with rollup;

+----------------+------+------+--------+
| COALESCE(a, b) | a    | b    | SUM(c) |
+----------------+------+------+--------+
|              7 | NULL |    7 |     40 |
|              1 | NULL | NULL |     40 | <-- should be null
|              1 |    1 |    4 |     10 |
|              2 |    1 | NULL |     10 | <-- should be 1
|              2 |    2 |    5 |     20 |
|              3 |    2 | NULL |     20 | <-- should be 2
|              3 |    3 |    6 |     30 |
|              3 |    3 | NULL |     30 |
|           NULL | NULL | NULL |    100 |
+----------------+------+------+--------+

# neither does ifnull
select IFNULL(a, b), a, b, SUM(c) from bug_test
group by a, b
with rollup;

+--------------+------+------+--------+
| IFNULL(a, b) | a    | b    | SUM(c) |
+--------------+------+------+--------+
|            7 | NULL |    7 |     40 |
|            1 | NULL | NULL |     40 |
|            1 |    1 |    4 |     10 |
|            2 |    1 | NULL |     10 |
|            2 |    2 |    5 |     20 |
|            3 |    2 | NULL |     20 |
|            3 |    3 |    6 |     30 |
|            3 |    3 | NULL |     30 |
|         NULL | NULL | NULL |    100 |
+--------------+------+------+--------+

# removing the other columns doesn't fix it
select COALESCE(a, b), SUM(c) from bug_test
group by a, b
with rollup;

+----------------+--------+
| COALESCE(a, b) | SUM(c) |
+----------------+--------+
|              7 |     40 |
|              1 |     40 | <-- should be null
|              1 |     10 |
|              2 |     10 | <-- should be 1
|              2 |     20 |
|              3 |     20 | <-- should be 2
|              3 |     30 |
|              3 |     30 | 
|           NULL |    100 |
+----------------+--------+

# only workaround I've found is to
# wrap the rollup query inside
# a subquery and do the COALESCE
# outside of that subquery.
# expected results:
select COALESCE(a, b), roll.* FROM (
select  a, b, SUM(c) from bug_test
group by a, b
with rollup) roll;

+----------------+------+------+--------+
| COALESCE(a, b) | a    | b    | SUM(c) |
+----------------+------+------+--------+
|              7 | NULL |    7 |     40 |
|           NULL | NULL | NULL |     40 | <-- expected result
|              1 |    1 |    4 |     10 |
|              1 |    1 | NULL |     10 | <-- expected result
|              2 |    2 |    5 |     20 |
|              2 |    2 | NULL |     20 | <-- expected result
|              3 |    3 |    6 |     30 |
|              3 |    3 | NULL |     30 |
|           NULL | NULL | NULL |    100 |
+----------------+------+------+--------+
[14 Oct 2020 19:15] MySQL Verification Team
Thank you for the bug report.
[17 Feb 11:11] Erlend Dahl
This looks like it has been fixed in 8.0.23, as a side effect of

Bug#31790217 REWRITE STREAMING AGGREGATION