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:
None 
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
Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1:

(SELECT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1'); -- sql1
(SELECT DISTINCT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1'); -- sql2

However, the value 0 changed to 0.0 after adding DISTINCT, seems like a logical bug:

mysql> (SELECT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1');
+----------------+
| (c1 DIV 1)*0.1 |
+----------------+
| 0              |
| 1              |
+----------------+
2 rows in set (0.00 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)

How to repeat:
drop table if exists t;
create table t (c1 double);
insert into t values (0.1);

(SELECT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1');
(SELECT DISTINCT (c1 DIV 1)*0.1 FROM t) UNION ALL (SELECT '1');

Suggested fix:
I think sql2 should return 0 instead of 0.0.
Of course this is a trivial bug.
[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.