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: | |
| 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
[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.