Details
-
Bug
-
Status: Confirmed (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.5.1, 10.11.1, 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
-
ubuntu 18.04
Description
Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1:
SELECT -f2 AS f1 FROM (SELECT (BINARY c1) AS f2 FROM t) AS t1; -- sql1 |
SELECT -f2 AS f1 FROM (SELECT DISTINCT (BINARY c1) AS f2 FROM t) AS t1; -- sql2 |
However, the value '-0.' changed to '-0.0001' 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 -f2 AS f1 FROM (SELECT (BINARY c1) AS f2 FROM t) AS t1; -- sql1
|
+------+
|
| f1 |
|
+------+
|
| -0. | |
+------+
|
1 row in set (0.00 sec) |
|
|
mysql> SELECT -f2 AS f1 FROM (SELECT DISTINCT (BINARY c1) AS f2 FROM t) AS t1; -- sql2
|
+---------+
|
| f1 |
|
+---------+
|
| -0.0001 | |
+---------+
|
1 row in set (0.00 sec) |
How to repeat:
drop table if exists t; |
CREATE TABLE t (c1 DOUBLE);
|
INSERT INTO t VALUES (0.0001); |
|
|
SELECT -f2 AS f1 FROM (SELECT (BINARY c1) AS f2 FROM t) AS t1; -- sql1
|
SELECT -f2 AS f1 FROM (SELECT DISTINCT (BINARY c1) AS f2 FROM t) AS t1; -- sql2
|
Hope these can be helpful for your debugging:
1. The bug cannot be reproduced after removing BINARY;
2. 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.1, it cannot be reproduced in mariadb:10.4.27 (we can't find more intermediate versions in dockerhub):
MariaDB [TEST]> select version();
|
+--------------------------------------+
|
| version() |
|
+--------------------------------------+
|
| 10.5.1-MariaDB-1:10.5.1+maria~bionic | |
+--------------------------------------+
|
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> SELECT -f2 AS f1 FROM (SELECT (BINARY c1) AS f2 FROM t) AS t1; -- sql1
|
+------+
|
| f1 |
|
+------+
|
| -0. | |
+------+
|
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> SELECT -f2 AS f1 FROM (SELECT DISTINCT (BINARY c1) AS f2 FROM t) AS t1; -- sql2
|
+---------+
|
| f1 |
|
+---------+
|
| -0.0001 | |
+---------+
|
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> select version();
|
+-----------------------------------------+
|
| version() |
|
+-----------------------------------------+
|
| 10.4.27-MariaDB-1:10.4.27+maria~ubu2004 | |
+-----------------------------------------+
|
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> SELECT -f2 AS f1 FROM (SELECT (BINARY c1) AS f2 FROM t) AS t1; -- sql1
|
+---------+
|
| f1 |
|
+---------+
|
| -0.0001 | |
+---------+
|
1 row in set (0.000 sec) |
|
|
MariaDB [TEST]> SELECT -f2 AS f1 FROM (SELECT DISTINCT (BINARY c1) AS f2 FROM t) AS t1; -- sql2
|
+---------+
|
| f1 |
|
+---------+
|
| -0.0001 | |
+---------+
|
1 row in set (0.000 sec) |