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:
None 
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:51] Hope Lee
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);
+  }
 };

 /**
[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.