| Bug #98768 | The ROLLUP super-aggregate row output is not correct | ||
|---|---|---|---|
| Submitted: | 28 Feb 2020 1:41 | Modified: | 30 Mar 2020 16:49 |
| Reporter: | Hope Lee (OCA) | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0.16, 8.0.19, 5.7.29 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[28 Feb 2020 6:16]
MySQL Verification Team
Hello Lee, Thank you for the report and test case. regards, Umesh
[30 Mar 2020 16:49]
Jon Stephens
Documented fix in the MySQL 8.0.21 changelog, as follows:
Reimplemented rollups without using slices. This fixes the
following known issues:
·A repeating column in a GROUP BY ... WITH ROLLUP yielded the
wrong result; that is, a GROUP BY of the form GROUP BY a, b,
a WITH ROLLUP erroneously produced NULL for some of the
column names in the result.
·A GROUP BY ... WITH ROLLUP that did not require a temporary
table to print the result also produced an erroneous NULL in
place of at least one of the expected column names in the
output.
Closed.
Description: If the ROLLUP doesn't need a temporary table to write the results, it will produce the wrong results. Specifically, the super-aggregate row output is not correct. How to repeat: CREATE TABLE `test_datatype_list` ( `int_test` int(11) DEFAULT NULL, `char_test` char(1) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, `varchar_test` varchar(20) DEFAULT NULL ); INSERT INTO test_datatype_list VALUES (24, 'a', "2017-03-22"), (30, NULL, "12:15:12"), (NULL, 'B', "4_varheks"), (24, 'A', NULL); Actual Answer: root@localhost:test 8.0.16-rds-dev-debug> SELECT concat(IFNULL(varchar_test, 'Ok'), char_test) , sum(int_test) FROM test_datatype_list GROUP BY concat(IFNULL(varchar_test, 'Ok'), char_test) WITH ROLLUP; +-----------------------------------------------+---------------+ | concat(IFNULL(varchar_test, 'Ok'), char_test) | sum(int_test) | +-----------------------------------------------+---------------+ | NULL | 30 | | 2017-03-22a | 24 | | 4_varheksB | NULL | | OkA | 24 | | OkA | 78 | +-----------------------------------------------+---------------+ 5 rows in set (0.00 sec) Expect: +-----------------------------------------------+---------------+ | concat(IFNULL(varchar_test, 'Ok'), char_test) | sum(int_test) | +-----------------------------------------------+---------------+ | NULL | 30 | | 2017-03-22a | 24 | | 4_varheksB | NULL | | OkA | 24 | | NULL | 78 | +-----------------------------------------------+---------------+ Suggested fix: diff --git a/sql/item.h b/sql/item.h index 17c6cd9..1280910 100644 --- a/sql/item.h +++ b/sql/item.h @@ -5209,6 +5209,10 @@ class Item_copy : public Item { func_arg->banned_function_name = "values"; return true; } + bool eq(const Item *target_item, bool binary_cmp) const override { + return type() == target_item->type() && + item->eq(down_cast<const Item_copy*>(target_item)->item, binary_cmp); + } }; /**