Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1:
SELECT f1 FROM (SELECT FROM_DAYS(1) AS f1 FROM t) AS `t3` JOIN (SELECT c1 FROM t) AS `t4` ON ((NOT (f1<=ANY (SELECT c1 FROM t))) OR (c1 = 0)) IS TRUE; -- sql1
SELECT f1 FROM (SELECT DISTINCT FROM_DAYS(1) AS f1 FROM t) AS `t3` JOIN (SELECT c1 FROM t) AS `t4` ON ((NOT (f1<=ANY (SELECT c1 FROM t))) OR (c1 = 0)) IS TRUE; -- sql2
However, there is an extra line in the result of sql2, seems like a logical bug:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31 |
+-----------+
1 row in set (0.01 sec)
mysql> SELECT f1 FROM (SELECT FROM_DAYS(1) AS f1 FROM t) AS `t3` JOIN (SELECT c1 FROM t) AS `t4` ON ((NOT (f1<=ANY (SELECT c1 FROM t))) OR (c1 = 0)) IS TRUE;
Empty set (0.00 sec)
mysql> SELECT f1 FROM (SELECT DISTINCT FROM_DAYS(1) AS f1 FROM t) AS `t3` JOIN (SELECT c1 FROM t) AS `t4` ON ((NOT (f1<=ANY (SELECT c1 FROM t))) OR (c1 = 0)) IS TRUE;
+------------+
| f1 |
+------------+
| 0000-00-00 |
+------------+
1 row in set, 2 warnings (0.01 sec)
How to repeat:
drop table if exists t;
CREATE TABLE t (c1 DECIMAL(40,20) UNSIGNED);
INSERT INTO t VALUES (1);
SELECT f1 FROM (SELECT FROM_DAYS(1) AS f1 FROM t) AS `t3` JOIN (SELECT c1 FROM t) AS `t4` ON ((NOT (f1<=ANY (SELECT c1 FROM t))) OR (c1 = 0)) IS TRUE;
SELECT f1 FROM (SELECT DISTINCT FROM_DAYS(1) AS f1 FROM t) AS `t3` JOIN (SELECT c1 FROM t) AS `t4` ON ((NOT (f1<=ANY (SELECT c1 FROM t))) OR (c1 = 0)) IS TRUE;
Suggested fix:
The following list may be helpful for your debugging:
1. The bug cannot be reproduced after removing IS TRUE;
2. 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:8.0.16, it cannot be reproduced in mysql:8.0.15:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.15 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT f1 FROM (SELECT FROM_DAYS(1) AS f1 FROM t) AS `t3` JOIN (SELECT c1 FROM t) AS `t4` ON ((NOT (f1<=ANY (SELECT c1 FROM t))) OR (c1 = 0)) IS TRUE;
+------------+
| f1 |
+------------+
| 0000-00-00 |
+------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT f1 FROM (SELECT DISTINCT FROM_DAYS(1) AS f1 FROM t) AS `t3` JOIN (SELECT c1 FROM t) AS `t4` ON ((NOT (f1<=ANY (SELECT c1 FROM t))) OR (c1 = 0)) IS TRUE;
+------------+
| f1 |
+------------+
| 0000-00-00 |
+------------+
1 row in set, 2 warnings (0.00 sec)
