MySQL Bugs: #101684: Adding ROLLUP breaks group labels

Bug #101684	Adding ROLLUP breaks group labels
Submitted:	19 Nov 2020 15:35	Modified:	25 Nov 2020 9:22
Reporter:	Rob Ketzscher	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.22	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Adding "WITH ROLLUP" to a grouped report breaks labels in certain scenarios.

See below and/or https://stackoverflow.com/questions/64791459/problems-with-rollup-in-mysql-8-0-21

How to repeat:
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=df65003f39f8822818e7483f9972fe05

Create sample:
CREATE TABLE TableName(Prob DOUBLE);
INSERT INTO TableName ( Prob ) VALUES ( RAND());
INSERT INTO TableName ( Prob ) SELECT IF(RAND()<0.1,NULL,RAND()) FROM TableName;
INSERT INTO TableName ( Prob ) SELECT IF(RAND()<0.1,NULL,RAND()) FROM TableName;
INSERT INTO TableName ( Prob ) SELECT IF(RAND()<0.1,NULL,RAND()) FROM TableName;
INSERT INTO TableName ( Prob ) SELECT IF(RAND()<0.1,NULL,RAND()) FROM TableName;
INSERT INTO TableName ( Prob ) SELECT IF(RAND()<0.1,NULL,RAND()) FROM TableName;
INSERT INTO TableName ( Prob ) SELECT IF(RAND()<0.1,NULL,RAND()) FROM TableName;
INSERT INTO TableName ( Prob ) SELECT IF(RAND()<0.1,NULL,RAND()) FROM TableName;
INSERT INTO TableName ( Prob ) SELECT IF(RAND()<0.1,NULL,RAND()) FROM TableName;
INSERT INTO TableName ( Prob ) SELECT IF(RAND()<0.1,NULL,RAND()) FROM TableName;
INSERT INTO TableName ( Prob ) SELECT IF(RAND()<0.1,NULL,RAND()) FROM TableName;

Expected behaviour: 
SELECT TRUNCATE(table_s.Prob,1) AS p,COUNT(*) AS c
FROM TableName table_s
GROUP BY TRUNCATE(table_s.Prob,1);

Unexpected behaviour:
SELECT TRUNCATE(table_s.Prob,1) AS p,COUNT(*) AS c
FROM TableName table_s
GROUP BY TRUNCATE(table_s.Prob,1)
WITH ROLLUP;

Hello Rob Ketzscher,

Thank you for the report and test case.

regards,
Umesh

- 8.0.22

mysql> SELECT TRUNCATE(table_s.Prob,1) AS p,COUNT(*) AS c
    -> FROM TableName table_s
    -> GROUP BY TRUNCATE(table_s.Prob,1);
+------+-----+
| p    | c   |
+------+-----+
|  0.8 | 106 |
|  0.2 | 101 |
|  0.6 |  96 |
|    0 |  86 |
|  0.5 |  69 |
|  0.7 | 101 |
| NULL | 112 |
|  0.4 |  85 |
|  0.3 | 103 |
|  0.1 |  93 |
|  0.9 |  72 |
+------+-----+
11 rows in set (0.00 sec)

mysql> SELECT TRUNCATE(table_s.Prob,1) AS p,COUNT(*) AS c
    -> FROM TableName table_s
    -> GROUP BY TRUNCATE(table_s.Prob,1)
    -> WITH ROLLUP;
+------+------+
| p    | c    |
+------+------+
| NULL |  112 |
|    0 |   86 |
|   0. |   93 |
|   0. |  101 |
|   0. |  103 |
|   0. |   85 |
|   0. |   69 |
|    1 |   96 |
|    1 |  101 |
|    1 |  106 |
|    1 |   72 |
| NULL | 1024 |
+------+------+
12 rows in set (0.00 sec)

- 5.7.32

mysql> SELECT TRUNCATE(table_s.Prob,1) AS p,COUNT(*) AS c
    -> FROM TableName table_s
    -> GROUP BY TRUNCATE(table_s.Prob,1);
+------+-----+
| p    | c   |
+------+-----+
| NULL |  94 |
|  0.0 | 102 |
|  0.1 | 103 |
|  0.2 |  87 |
|  0.3 |  86 |
|  0.4 |  95 |
|  0.5 |  93 |
|  0.6 |  90 |
|  0.7 | 106 |
|  0.8 |  87 |
|  0.9 |  81 |
+------+-----+
11 rows in set (0.00 sec)

mysql> SELECT TRUNCATE(table_s.Prob,1) AS p,COUNT(*) AS c
    -> FROM TableName table_s
    -> GROUP BY TRUNCATE(table_s.Prob,1)
    -> WITH ROLLUP;
+------+------+
| p    | c    |
+------+------+
| NULL |   94 |
|  0.0 |  102 |
|  0.1 |  103 |
|  0.2 |   87 |
|  0.3 |   86 |
|  0.4 |   95 |
|  0.5 |   93 |
|  0.6 |   90 |
|  0.7 |  106 |
|  0.8 |   87 |
|  0.9 |   81 |
| NULL | 1024 |
+------+------+
12 rows in set (0.00 sec)

-- 5.6.50

mysql> SELECT TRUNCATE(table_s.Prob,1) AS p,COUNT(*) AS c
    -> FROM TableName table_s
    -> GROUP BY TRUNCATE(table_s.Prob,1);
+------+-----+
| p    | c   |
+------+-----+
| NULL | 106 |
|  0.0 |  84 |
|  0.1 |  75 |
|  0.2 |  95 |
|  0.3 | 102 |
|  0.4 |  78 |
|  0.5 |  96 |
|  0.6 |  80 |
|  0.7 | 115 |
|  0.8 |  85 |
|  0.9 | 108 |
+------+-----+
11 rows in set (0.00 sec)

mysql> SELECT TRUNCATE(table_s.Prob,1) AS p,COUNT(*) AS c
    -> FROM TableName table_s
    -> GROUP BY TRUNCATE(table_s.Prob,1)
    -> WITH ROLLUP;
+------+------+
| p    | c    |
+------+------+
| NULL |  106 |
|  0.0 |   84 |
|  0.1 |   75 |
|  0.2 |   95 |
|  0.3 |  102 |
|  0.4 |   78 |
|  0.5 |   96 |
|  0.6 |   80 |
|  0.7 |  115 |
|  0.8 |   85 |
|  0.9 |  108 |
| NULL | 1024 |
+------+------+
12 rows in set (0.00 sec)

Documented fix as follows in the MySQL 8.0.24 changelog:

    Certain group by queries that performed correctly did not return
    the expected result when WITH ROLLUP was added. This was due to
    the fact that decimal information was not always correctly piped
    through rollup group items, causing functions returning decimal
    values such as TRUNCATE() to receive data of the wrong type.

Closed.

Fix appears in MySQL 8.0.23, not 8.0.24. No other changes.