Details
Description
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) |
Attachments
Issue Links
- relates to
-
MDEV-30252 Value changed after adding DISTINCT [5.5.62, 10.11.1]
-
- Confirmed
-
-
-
- Confirmed
-
-
-
- Open
-
-
-
- Confirmed
-