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() |
+-----------+
| 8.0.31 |
+-----------+
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 |
+----------------+
1 row in set, 1 warning (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 |
+------+
1 row in set, 2 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`);
Suggested fix:
We look for the first version of the bug in all docker images (with format x.x.x) of https://hub.docker.com/_/mysql/tags
We found that the bug first occurred in mysql:5.7.22, it cannot be reproduced in mysql:5.7.21:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.21 |
+-----------+
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`);
Empty set, 2 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`);
Empty set, 2 warnings (0.01 sec)
