Bug #108946 | 0 changed to 0.0 caused by DISTINCT and UNION ALL | ||
---|---|---|---|
Submitted: | 1 Nov 2022 3:35 | Modified: | 13 Dec 2022 4:12 |
Reporter: | ZongYin Hao | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 8.0.30 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | 0, distinct, unionall |
[1 Nov 2022 3:35]
ZongYin Hao
[1 Nov 2022 7:45]
MySQL Verification Team
Hello ZongYin Hao, Thank you for the report and test case. regards, Umesh
[13 Dec 2022 4:12]
ZongYin Hao
We look for the first version of the bug in all docker images (with format x.x.x) of https://hub.docker.com/_/mysql/tags We found that the bug first occurred in mysql:8.0.0, it cannot be reproduced in mysql:5.7.40 (we can't find more intermediate versions in dockerhub): mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.40 | +-----------+ 1 row in set (0.00 sec) mysql> (SELECT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1'); +----------------+ | (c1 DIV 1)*0.1 | +----------------+ | 0.0 | | 1 | +----------------+ 2 rows in set (0.01 sec) mysql> (SELECT DISTINCT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1'); +----------------+ | (c1 DIV 1)*0.1 | +----------------+ | 0.0 | | 1 | +----------------+ 2 rows in set (0.00 sec) Hope it can be helpful for your debugging.