| Bug #97628 | Wrong result with rollup | ||
|---|---|---|---|
| Submitted: | 14 Nov 2019 8:51 | Modified: | 4 Feb 2020 14:51 |
| 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.18, 8.0.11 | OS: | CentOS |
| Assigned to: | CPU Architecture: | Any | |
[14 Nov 2019 8:59]
Hope Lee
Sorry, the one with distinct is correct, the one without is wrong. The title should be adjusted.
[14 Nov 2019 9:04]
MySQL Verification Team
Hello Lee, Thank you for the report and test case. regards, Umesh
[4 Feb 2020 14:51]
Jon Stephens
Documented fix in the MySQL 8.0.20 changelog as follows:
A query returned inaccurate results when an expression in a
GROUP BY clause used a column name differing in case from that
used for the name of the column when the table containing this
column was created. An example of this would be when the query
used GROUP BY id although the column name as shown in the
original CREATE TABLE statement was ID.
This occurred because, the server performed case-sensitive
comparisons of column names in expressions with names of columns
in tables. This issue is fixed by ensuring that such comparisons
are performed in a case-insensitive fashion as expected.
Closed.
Note that this also fixes BUG#98222.
Description: SELECT DISTINCT returns wrong result when there is WITH ROLLUP How to repeat: CREATE TABLE `test` ( `iD` bigint(20) NOT NULL, `INT_TEST` int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC; INSERT INTO test VALUES (2, 10), (3, NULL); SELECT count(*), id + int_test FROM test GROUP BY id + int_test WITH ROLLUP; SELECT DISTINCT count(*), id + int_test FROM test GROUP BY id + int_test WITH ROLLUP; root@localhost:test 8.0.16-rds-dev-debug> SELECT count(*), id + int_test FROM test GROUP BY id + int_test WITH ROLLUP; +----------+---------------+ | count(*) | id + int_test | +----------+---------------+ | 1 | NULL | | 1 | 12 | | 2 | 12 | +----------+---------------+ 3 rows in set (0.00 sec) The right result should be below: root@localhost:test 8.0.16-rds-dev-debug> SELECT DISTINCT count(*), id + int_test FROM test GROUP BY id + int_test WITH ROLLUP; +----------+---------------+ | count(*) | id + int_test | +----------+---------------+ | 1 | NULL | | 1 | 12 | | 2 | NULL | +----------+---------------+ 3 rows in set (0.00 sec) 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); + } }; /**