Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.5.9, 10.11.1
-
None
-
ubuntu 18.04
Description
Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1
(SELECT c1 AS f1 FROM t) UNION ALL (SELECT REPLACE('what', f2, f2) AS f1 FROM (SELECT !c2 AS f2 FROM t) AS t1); -- sql1 |
(SELECT c1 AS f1 FROM t) UNION ALL (SELECT REPLACE('what', f2, f2) AS f1 FROM (SELECT DISTINCT !c2 AS f2 FROM t) AS t1); -- sql2 |
However, the numeric format `9.9999997e-5` changed to `0.00009999999747378752` after adding DISTINCT, seems like a logical bug:
mysql> select version(); |
+-----------------------------------------+ |
| version() |
|
+-----------------------------------------+ |
| 10.11.1-MariaDB-1:10.11.1+maria~ubu2204 |
|
+-----------------------------------------+ |
1 row in set (0.00 sec) |
|
|
mysql> (SELECT c1 AS f1 FROM t) UNION ALL (SELECT REPLACE('what', f2, f2) AS f1 FROM (SELECT !c2 AS f2 FROM t) AS t1); -- sql1 |
+--------------+ |
| f1 |
|
+--------------+ |
| 9.9999997e-5 |
|
| what |
|
+--------------+ |
2 rows in set (0.00 sec) |
|
|
mysql> (SELECT c1 AS f1 FROM t) UNION ALL (SELECT REPLACE('what', f2, f2) AS f1 FROM (SELECT DISTINCT !c2 AS f2 FROM t) AS t1); -- sql2 |
+------------------------+ |
| f1 |
|
+------------------------+ |
| 0.00009999999747378752 |
|
| what |
|
+------------------------+ |
2 rows in set (0.00 sec) |
How to repeat:
drop table if exists t; |
CREATE TABLE t (c1 FLOAT UNSIGNED,c2 INT); |
INSERT INTO t VALUES (0.0001,1); |
|
|
(SELECT c1 AS f1 FROM t) UNION ALL (SELECT REPLACE('what', f2, f2) AS f1 FROM (SELECT !c2 AS f2 FROM t) AS t1); -- sql1 |
(SELECT c1 AS f1 FROM t) UNION ALL (SELECT REPLACE('what', f2, f2) AS f1 FROM (SELECT DISTINCT !c2 AS f2 FROM t) AS t1); -- sql2 |
Hope these can be helpful for your debugging:
We look for the first version of the bug in all docker images (with format x.x.x) of https://hub.docker.com/_/mariadb/tags
We found that the bug first occurred in mariadb:10.5.9, it cannot be reproduced in mariadb:10.5.8:
MariaDB [TEST]> select version(); |
+-------------------------------------+ |
| version() |
|
+-------------------------------------+ |
| 10.5.9-MariaDB-1:10.5.9+maria~focal |
|
+-------------------------------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> (SELECT c1 AS f1 FROM t) UNION ALL (SELECT REPLACE('what', f2, f2) AS f1 FROM (SELECT !c2 AS f2 FROM t) AS t1); -- sql1 |
+--------------+ |
| f1 |
|
+--------------+ |
| 9.9999997e-5 |
|
| what |
|
+--------------+ |
2 rows in set (0.001 sec) |
|
|
MariaDB [TEST]> (SELECT c1 AS f1 FROM t) UNION ALL (SELECT REPLACE('what', f2, f2) AS f1 FROM (SELECT DISTINCT !c2 AS f2 FROM t) AS t1); -- sql2 |
+------------------------+ |
| f1 |
|
+------------------------+ |
| 0.00009999999747378752 |
|
| what |
|
+------------------------+ |
2 rows in set (0.001 sec) |
|
|
MariaDB [TEST]> select version(); |
+-------------------------------------+ |
| version() |
|
+-------------------------------------+ |
| 10.5.8-MariaDB-1:10.5.8+maria~focal |
|
+-------------------------------------+ |
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> (SELECT c1 AS f1 FROM t) UNION ALL (SELECT REPLACE('what', f2, f2) AS f1 FROM (SELECT !c2 AS f2 FROM t) AS t1); -- sql1 |
+--------------+ |
| f1 |
|
+--------------+ |
| 9.9999997e-5 |
|
| what |
|
+--------------+ |
2 rows in set (0.001 sec) |
|
|
MariaDB [TEST]> (SELECT c1 AS f1 FROM t) UNION ALL (SELECT REPLACE('what', f2, f2) AS f1 FROM (SELECT DISTINCT !c2 AS f2 FROM t) AS t1); -- sql2 |
+--------------+ |
| f1 |
|
+--------------+ |
| 9.9999997e-5 |
|
| what |
|
+--------------+ |
2 rows in set (0.001 sec) |