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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.30
• OS: Any
• 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.