| Bug #84720 | WITH ROLLUP not providing expected NULL values | ||
|---|---|---|---|
| Submitted: | 30 Jan 2017 12:09 | Modified: | 30 Jan 2017 12:42 |
| Reporter: | Simon Mudd (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.6.27,5.7, 5.7.17 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | rollup | ||
[30 Jan 2017 12:14]
Simon Mudd
Correction: The second column of the last 2 rows should be NULL.
[30 Jan 2017 12:15]
Simon Mudd
Adjust Synopsis
[30 Jan 2017 12:42]
MySQL Verification Team
Hello Simon, Thank you for the report and test case. Thanks, Umesh
[30 Jan 2017 13:04]
Ruud H.G. van Tol
Found a non-enum test case: SELECT tablename , 10*((0+`at`) DIV 1000) AS _ymd_HM , action , COUNT(*) FROM rollup_enum_bug GROUP BY 1 ,10.0*((0+`at`) DIV 1000),3 WITH ROLLUP; The column-definition has '10', but the 'group by' definition has '10.0'.
[30 Jan 2017 13:13]
Ruud H.G. van Tol
Same with the '0' and '0.0' in this one: SELECT tablename, 0+`at` AS _at, COUNT(*) FROM rollup_enum_bug GROUP BY 1, 0.0+`at` WITH ROLLUP;
[30 Jan 2017 13:19]
Ruud H.G. van Tol
See also: https://bugs.mysql.com/bug.php?id=25373 http://stackoverflow.com/questions/659845/mysql-total-group-by-with-rollup-curiosity https://bugs.mysql.com/bug.php?id=24856
Description: WITH ROLLUP is not providing the expected NULL column. See test case below. How to repeat: CREATE TABLE `rollup_enum_bug` ( `id` bigint unsigned NOT NULL AUTO_INCREMENT, `tablename` varchar(60) NOT NULL, `action` enum('INSERT','UPDATE','DELETE') NOT NULL, `at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='test'; INSERT INTO rollup_enum_bug (tablename, action) VALUES ("foo", "INSERT"),("foo","UPDATE"),("bar","INSERT"),("bar","UPDATE"); INSERT INTO rollup_enum_bug (tablename, action) SELECT tablename, action FROM rollup_enum_bug; INSERT INTO rollup_enum_bug (tablename, action) SELECT tablename, action FROM rollup_enum_bug; SELECT CONCAT(LEFT(`at`,15),'0') AS _ymd_HM, action, tablename, COUNT(*) FROM rollup_enum_bug GROUP BY 1,0+action,3 WITH ROLLUP; +------------------+--------+-----------+----------+ | _ymd_HM | action | tablename | COUNT(*) | +------------------+--------+-----------+----------+ | 2017-01-30 12:50 | INSERT | bar | 4 | | 2017-01-30 12:50 | INSERT | foo | 4 | | 2017-01-30 12:50 | INSERT | NULL | 8 | | 2017-01-30 12:50 | UPDATE | bar | 4 | | 2017-01-30 12:50 | UPDATE | foo | 4 | | 2017-01-30 12:50 | UPDATE | NULL | 8 | | 2017-01-30 12:50 | UPDATE | NULL | 16 | | NULL | UPDATE | NULL | 16 | +------------------+--------+-----------+----------+ 8 rows in set (0.00 sec) The second column of the last row should be NULL. Suggested fix: Fix the result to match what is expected.