|
Description:
In theory, the result of sql1 ⊆ the result of sql2:
SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON f1 != 0; -- sql1
|
SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON 1; -- sql2
|
Because the 'ON 1' in sql2 is always true, but the 'ON f1 != 0' in sql1 may be false.
However, some values changed after changing 'ON f1 != 0' to 'ON 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 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON f1 != 0; -- sql1
|
+----------+
|
| f1 |
|
+----------+
|
| -11046 |
|
| 2018 |
|
| 2089.051 |
|
+----------+
|
3 rows in set (0.00 sec)
|
|
mysql> SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON 1; -- sql2
|
+-------------------+
|
| f1 |
|
+-------------------+
|
| 20180516222895.9 |
|
| 20180516235959.9 |
|
| 20180516236030.95 |
|
+-------------------+
|
3 rows in set (0.00 sec)
|
How to repeat:
drop table if exists t;
|
create table t (c1 double);
|
insert into t values (-13064),(0),(71.051);
|
|
SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON f1 != 0; -- sql1
|
SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON 1; -- 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:5.5.54, it cannot be reproduced in mariadb:5.5.53:
MariaDB [TEST]> select version();
|
+-------------------------+
|
| version() |
|
+-------------------------+
|
| 5.5.54-MariaDB-1~wheezy |
|
+-------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [TEST]> SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON f1 != 0; -- sql1
|
+----------+
|
| f1 |
|
+----------+
|
| -11046 |
|
| 2018 |
|
| 2089.051 |
|
+----------+
|
3 rows in set (0.00 sec)
|
|
MariaDB [TEST]> SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON 1; -- sql2
|
+-------------------+
|
| f1 |
|
+-------------------+
|
| 20180516222895.9 |
|
| 20180516235959.9 |
|
| 20180516236030.95 |
|
+-------------------+
|
3 rows in set (0.00 sec)
|
|
MariaDB [TEST]> select version();
|
+-------------------------+
|
| version() |
|
+-------------------------+
|
| 5.5.53-MariaDB-1~wheezy |
|
+-------------------------+
|
1 row in set (0.00 sec)
|
|
MariaDB [TEST]> SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON f1 != 0; -- sql1
|
+-------------------+
|
| f1 |
|
+-------------------+
|
| 20180516222895.9 |
|
| 20180516235959.9 |
|
| 20180516236030.95 |
|
+-------------------+
|
3 rows in set (0.00 sec)
|
|
MariaDB [TEST]> SELECT f1 FROM (SELECT 1) AS t1 JOIN (SELECT (c1+DATE_SUB('2018-05-17', INTERVAL 1 DAY_MICROSECOND)) AS f1 FROM t) AS t2 ON 1; -- sql2
|
+-------------------+
|
| f1 |
|
+-------------------+
|
| 20180516222895.9 |
|
| 20180516235959.9 |
|
| 20180516236030.95 |
|
+-------------------+
|
3 rows in set (0.00 sec)
|
|