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

[29 May 2021 20:02] Jochem van Dieten
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 |
-------------------
[30 May 2021 11:13] MySQL Verification Team
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>
[1 Jul 2021 1:00] Bugs System
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".

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.039 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.