Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.1.37, 10.11.1, 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 10.11
-
ubuntu 18.04
Description
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 version(); |
+-----------------------------------------+ |
| version() |
|
+-----------------------------------------+ |
| 10.11.1-MariaDB-1:10.11.1+maria~ubu2204 |
|
+-----------------------------------------+ |
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 |
|
| 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'); |
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.1.37, it cannot be reproduced in mariadb:10.1.36:
MariaDB [TEST]> select version(); |
+--------------------------+ |
| version() |
|
+--------------------------+ |
| 10.1.37-MariaDB-1~bionic |
|
+--------------------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [TEST]> (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) |
|
|
MariaDB [TEST]> (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) |
|
|
MariaDB [TEST]> select version(); |
+--------------------------+ |
| version() |
|
+--------------------------+ |
| 10.1.36-MariaDB-1~bionic |
|
+--------------------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [TEST]> (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.00 sec) |
|
|
MariaDB [TEST]> (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) |