|
Description:
In theory, the result of sql1 ⊆ the result of sql2:
SELECT f1 FROM (SELECT (c1-~LN(4)) AS f1 FROM t) AS t1 where f1 != 1; -- sql1
|
SELECT f1 FROM (SELECT (c1-~LN(4)) AS f1 FROM t) AS t1 where 1; -- sql2
|
Because the condition of sql2 (where 1) is always true, but the condition of sql1 (where f1 != 1) may be false.
However, the value 2 changed to -1.8446744073709552e19 after changing f1 != 1 to 1, 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 f1 FROM (SELECT (c1-~LN(4)) AS f1 FROM t) AS t1 where f1 != 1;
|
+------+
|
| f1 |
|
+------+
|
| 2 |
|
+------+
|
1 row in set (0.00 sec)
|
|
mysql> SELECT f1 FROM (SELECT (c1-~LN(4)) AS f1 FROM t) AS t1 where 1;
|
+------------------------+
|
| f1 |
|
+------------------------+
|
| -1.8446744073709552e19 |
|
+------------------------+
|
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);
|
|
SELECT f1 FROM (SELECT (c1-~LN(4)) AS f1 FROM t) AS t1 where f1 != 1;
|
SELECT f1 FROM (SELECT (c1-~LN(4)) AS f1 FROM t) AS t1 where 1;
|
Hope these can be helpful for your debugging:
1. The bug cannot be reproduced after deleting '~';
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:5.5.40, and we can't find any order versions in dockerhub:
MariaDB [TEST]> select version();
|
+-----------------------------+
|
| version() |
|
+-----------------------------+
|
| 5.5.40-MariaDB-1~wheezy-log |
|
+-----------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [TEST]> SELECT f1 FROM (SELECT (c1-~LN(4)) AS f1 FROM t) AS t1 where f1 != 1;
|
+------+
|
| f1 |
|
+------+
|
| 2 |
|
+------+
|
1 row in set (0.00 sec)
|
|
MariaDB [TEST]> SELECT f1 FROM (SELECT (c1-~LN(4)) AS f1 FROM t) AS t1 where 1;
|
+------------------------+
|
| f1 |
|
+------------------------+
|
| -1.8446744073709552e19 |
|
+------------------------+
|
1 row in set (0.00 sec)
|
|