Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.1.10, 10.11.1
-
ubuntu 18.04
Description
Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1:
SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`); -- sql1 |
SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT DISTINCT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`); -- sql2 |
However, the value 20000120000000 changed to 2001 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 (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`); |
+----------------+ |
| f3 |
|
+----------------+ |
| 20000120000000 |
|
| 20000120000000 |
|
+----------------+ |
2 rows in set, 4 warnings (0.00 sec) |
|
|
mysql> SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT DISTINCT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`); |
+------+ |
| f3 |
|
+------+ |
| 2001 |
|
| 2001 |
|
+------+ |
2 rows in set, 4 warnings (0.00 sec) |
How to repeat:
drop table if exists t; |
CREATE TABLE t (c1 DECIMAL(40,20)); |
INSERT INTO t VALUES (-0),(120); |
|
|
SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`); |
SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT DISTINCT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`); |
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.10, it cannot be reproduced in mariadb:10.1.2 (we can't find more intermediate versions in dockerhub):
MariaDB [TEST]> select version(); |
+--------------------------+ |
| version() |
|
+--------------------------+ |
| 10.1.10-MariaDB-1~jessie |
|
+--------------------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [TEST]> SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`); |
+----------------+ |
| f3 |
|
+----------------+ |
| 20000120000000 |
|
+----------------+ |
1 row in set, 1 warning (0.00 sec) |
|
|
MariaDB [TEST]> SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT DISTINCT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`); |
+------+ |
| f3 |
|
+------+ |
| 2001 |
|
+------+ |
1 row in set, 2 warnings (0.00 sec) |
|
|
MariaDB [TEST]> select version(); |
+-----------------------------------+ |
| version() |
|
+-----------------------------------+ |
| 10.1.2-MariaDB-1~wheezy-wsrep-log |
|
+-----------------------------------+ |
1 row in set (0.00 sec) |
|
|
MariaDB [TEST]> SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`); |
Empty set, 8 warnings (0.00 sec) |
|
|
MariaDB [TEST]> SELECT (1^`f5`) AS `f3` FROM (SELECT c1 AS `f4` FROM t) AS `t1` JOIN (SELECT DISTINCT (DATE_ADD(c1, INTERVAL 1 DAY_SECOND)) AS `f5` FROM t) AS `t3` ON (FROM_DAYS(1)) NOT IN (`f4`,`f5`); |
Empty set, 4 warnings (0.00 sec) |
Attachments
Issue Links
- relates to
-
-
- Confirmed
-