MySQL Bugs: #103852: intermediate ROLLUP gives incorrect result

Bug #103852	intermediate ROLLUP gives incorrect result
Submitted:	29 May 2021 20:02	Modified:	30 Jun 2021 11:13
Reporter:	Jochem van Dieten	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.17	OS:	Other (official Docker image)
Assigned to:		CPU Architecture:	x86
Tags:	cte, rollup

Description:
Queries where a ROLLUP is used in a CTE or in an intermediate result give incorrect results

How to repeat:
CREATE TABLE t1 (c1 integer DEFAULT NULL);
insert into t1 (c1) values (17295314);

-- correct result when ROLLUP is the last operation
select 'GROUP_1' as label, count(*) as 'count'   
from t1 
group by label with ROLLUP;
-------------------
| label   | count |
-------------------
| GROUP_1 |     1 |
| NULL    |     1 |
-------------------

-- incorrect result when ROLLUP is not the last operation
select IFNULL(label, 'TOTAL') as delay, count
from (
  select 'GROUP_1' as label, count(*) as 'count'   
  from t1 
  group by label with ROLLUP 
) intermed;
-------------------
| label   | count |
-------------------
| GROUP_1 |     1 |
| GROUP_1 |     1 |
-------------------

WITH intermed AS (
  select 'GROUP_1' as label, count(*) as 'count'   
  from t1 
  group by label with ROLLUP 
)
select IFNULL(label, 'TOTAL') as delay, count
from intermed;
-------------------
| label   | count |
-------------------
| GROUP_1 |     1 |
| GROUP_1 |     1 |
-------------------

-- correct: the same final operation without the ROLLUP 
select IFNULL(label, 'TOTAL') as delay, count
from (
  select 'GROUP_1' as label, 1 as 'count'   
  UNION ALL 
  select null as label, 1 as 'count'   
) intermed;
-------------------
| label   | count |
-------------------
| GROUP_1 |     1 |
| TOTAL   |     1 |
-------------------

Thank you for the bug report. Please check with today release 8.0.25:

Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE L
Database changed
mysql> CREATE TABLE t1 (c1 integer DEFAULT NULL);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 (c1) values (17295314);
Query OK, 1 row affected (0.01 sec)

mysql> -- correct result when ROLLUP is the last operation
mysql> select 'GROUP_1' as label, count(*) as 'count'
    -> from t1
    -> group by label with ROLLUP;
+---------+-------+
| label   | count |
+---------+-------+
| GROUP_1 |     1 |
| NULL    |     1 |
+---------+-------+
2 rows in set (0.01 sec)

mysql> -- incorrect result when ROLLUP is not the last operation
mysql> select IFNULL(label, 'TOTAL') as delay, count
    -> from (
    ->   select 'GROUP_1' as label, count(*) as 'count'
    ->   from t1
    ->   group by label with ROLLUP
    -> ) intermed;
+---------+-------+
| delay   | count |
+---------+-------+
| GROUP_1 |     1 |
| TOTAL   |     1 |
+---------+-------+
2 rows in set (0.01 sec)

mysql> WITH intermed AS (
    ->   select 'GROUP_1' as label, count(*) as 'count'
    ->   from t1
    ->   group by label with ROLLUP
    -> )
    -> select IFNULL(label, 'TOTAL') as delay, count
    -> from intermed;
+---------+-------+
| delay   | count |
+---------+-------+
| GROUP_1 |     1 |
| TOTAL   |     1 |
+---------+-------+
2 rows in set (0.00 sec)

mysql> -- correct: the same final operation without the ROLLUP
mysql> select IFNULL(label, 'TOTAL') as delay, count
    -> from (
    ->   select 'GROUP_1' as label, 1 as 'count'
    ->   UNION ALL
    ->   select null as label, 1 as 'count'
    -> ) intermed;
+---------+-------+
| delay   | count |
+---------+-------+
| GROUP_1 |     1 |
| TOTAL   |     1 |
+---------+-------+
2 rows in set (0.00 sec)

mysql>

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".