Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1:
SELECT UCASE(`f1`) FROM (SELECT (-c1) AS `f1` FROM t) AS `t1`; -- sql1
SELECT UCASE(`f1`) FROM (SELECT DISTINCT (-c1) AS `f1` FROM t) AS `t1`; -- sql2
However, the numeric format -9.999999747378752E-5 changed to -0.00009999999747378752 after adding DISTINCT, seems like a logical bug:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT UCASE(`f1`) FROM (SELECT (-c1) AS `f1` FROM t) AS `t1`;
+-----------------------+
| UCASE(`f1`) |
+-----------------------+
| -9.999999747378752E-5 |
+-----------------------+
1 row in set (0.00 sec)
mysql> SELECT UCASE(`f1`) FROM (SELECT DISTINCT (-c1) AS `f1` FROM t) AS `t1`;
+-------------------------+
| UCASE(`f1`) |
+-------------------------+
| -0.00009999999747378752 |
+-------------------------+
1 row in set (0.00 sec)
How to repeat:
drop table if exists t;
CREATE TABLE t (c1 FLOAT UNSIGNED);
INSERT INTO t VALUES (0.0001);
SELECT UCASE(`f1`) FROM (SELECT (-c1) AS `f1` FROM t) AS `t1`;
SELECT UCASE(`f1`) FROM (SELECT DISTINCT (-c1) AS `f1` FROM t) AS `t1`;
Suggested fix:
The following list may be helpful for your debugging:
1. The bug cannot be reproduced after deleting UCASE;
2. The bug cannot be reproduced after changing -c1 to c1;
3. 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.23, it cannot be reproduced in mysql:8.0.22:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.22 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT UCASE(`f1`) FROM (SELECT (-c1) AS `f1` FROM t) AS `t1`;
+-------------------------+
| UCASE(`f1`) |
+-------------------------+
| -0.00009999999747378752 |
+-------------------------+
1 row in set (0.00 sec)
mysql> SELECT UCASE(`f1`) FROM (SELECT DISTINCT (-c1) AS `f1` FROM t) AS `t1`;
+-------------------------+
| UCASE(`f1`) |
+-------------------------+
| -0.00009999999747378752 |
+-------------------------+
1 row in set (0.01 sec)
