Bug #99245 Wrong result is returned with DISTINCT syntax

• Submitted: 14 Apr 2020 3:55
• Modified: 14 Apr 2020 6:01
• Reporter: Hope Lee (OCA)
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.19, 5.6.47, 5.7.29
• OS: CentOS
• CPU Architecture: Any

[14 Apr 2020 3:55] Hope Lee

Description:
The wrong result is returned when DISTINCT syntax is used.

How to repeat:
CREATE TABLE `select_four` (
  `mediumint_test` mediumint(9) DEFAULT NULL,
  `double_test` double DEFAULT NULL);

INSERT INTO select_four VALUES (71, 35.1478);

root@localhost:test 8.0.19-rds-dev-debug> select 7 DIV (mediumint_test MOD double_test) / 8 as a FROM select_four;
+--------+
| a      |
+--------+
| 1.1250 |
+--------+
1 row in set (0.00 sec)

root@localhost:test 8.0.19-rds-dev-debug> select distinct 7 DIV (mediumint_test MOD double_test) / 8 as a FROM select_four;
+--------+
| a      |
+--------+
| 0.9999 |
+--------+
1 row in set (0.00 sec)

The result returned above is obviously wrong.

[14 Apr 2020 6:01] MySQL Verification Team

Hello Lee,

Thank you for the report and test case.

regards,
Umesh