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 |
+----------------+------+------+--------+
