Bug #97628 Wrong result with rollup
Submitted: 14 Nov 8:51 Modified: 14 Nov 9:04
Reporter: Hope Lee Email Updates:
Status: Verified 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 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 8:59] Hope Lee
Sorry, the one with distinct is correct, the one without is wrong. The title should be adjusted.
[14 Nov 9:04] Umesh Shastry
Hello Lee,

Thank you for the report and test case.

regards,
Umesh