Description:
In theory, the result of sql1 ⊆ the result of sql2:
SELECT f1 FROM (SELECT DATE_ADD('2015-06-23', INTERVAL 1 MINUTE_SECOND)%c2 AS `f1` FROM t WHERE LN(0.5) NOT IN (SELECT c1 FROM t)) AS t1 WHERE `f1` != 1; -- sql1
SELECT f1 FROM (SELECT DATE_ADD('2015-06-23', INTERVAL 1 MINUTE_SECOND)%c2 AS `f1` FROM t WHERE LN(0.5) NOT IN (SELECT c1 FROM t)) AS t1 WHERE 1; -- sql2
Because the condition of sql2 (WHERE 1) is always true, but the condition of sql1 (WHERE f1 != 1) may be false.
However, I can't find my value 95 after changing (WHERE f1 != 1) to (WHERE 1), seems like a logical bug:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT f1 FROM (SELECT DATE_ADD('2015-06-23', INTERVAL 1 MINUTE_SECOND)%c2 AS `f1` FROM t WHERE LN(0.5) NOT IN (SELECT c1 FROM t)) AS t1 WHERE `f1` != 1; -- sql1
+------+
| f1 |
+------+
| 95 |
+------+
1 row in set (0.00 sec)
mysql> SELECT f1 FROM (SELECT DATE_ADD('2015-06-23', INTERVAL 1 MINUTE_SECOND)%c2 AS `f1` FROM t WHERE LN(0.5) NOT IN (SELECT c1 FROM t)) AS t1 WHERE 1; -- sql2
+------+
| f1 |
+------+
| 41 |
| 1 |
+------+
2 rows in set (0.00 sec)
How to repeat:
drop table if exists t;
CREATE TABLE t (c1 BIGINT UNSIGNED, c2 DECIMAL(40,20), key(c1));
INSERT INTO t VALUES (2,120),(0,2);
SELECT f1 FROM (SELECT DATE_ADD('2015-06-23', INTERVAL 1 MINUTE_SECOND)%c2 AS `f1` FROM t WHERE LN(0.5) NOT IN (SELECT c1 FROM t)) AS t1 WHERE `f1` != 1; -- sql1
SELECT f1 FROM (SELECT DATE_ADD('2015-06-23', INTERVAL 1 MINUTE_SECOND)%c2 AS `f1` FROM t WHERE LN(0.5) NOT IN (SELECT c1 FROM t)) AS t1 WHERE 1; -- sql2
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.11, it cannot be reproduced in mysql:5.7.10:
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.10 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT f1 FROM (SELECT DATE_ADD('2015-06-23', INTERVAL 1 MINUTE_SECOND)%c2 AS `f1` FROM t WHERE LN(0.5) NOT IN (SELECT c1 FROM t)) AS t1 WHERE `f1` != 1; -- sql1
Empty set (0.00 sec)
mysql> SELECT f1 FROM (SELECT DATE_ADD('2015-06-23', INTERVAL 1 MINUTE_SECOND)%c2 AS `f1` FROM t WHERE LN(0.5) NOT IN (SELECT c1 FROM t)) AS t1 WHERE 1; -- sql2
Empty set (0.00 sec)
Description: In theory, the result of sql1 ⊆ the result of sql2: SELECT f1 FROM (SELECT DATE_ADD('2015-06-23', INTERVAL 1 MINUTE_SECOND)%c2 AS `f1` FROM t WHERE LN(0.5) NOT IN (SELECT c1 FROM t)) AS t1 WHERE `f1` != 1; -- sql1 SELECT f1 FROM (SELECT DATE_ADD('2015-06-23', INTERVAL 1 MINUTE_SECOND)%c2 AS `f1` FROM t WHERE LN(0.5) NOT IN (SELECT c1 FROM t)) AS t1 WHERE 1; -- sql2 Because the condition of sql2 (WHERE 1) is always true, but the condition of sql1 (WHERE f1 != 1) may be false. However, I can't find my value 95 after changing (WHERE f1 != 1) to (WHERE 1), seems like a logical bug: mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.31 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT f1 FROM (SELECT DATE_ADD('2015-06-23', INTERVAL 1 MINUTE_SECOND)%c2 AS `f1` FROM t WHERE LN(0.5) NOT IN (SELECT c1 FROM t)) AS t1 WHERE `f1` != 1; -- sql1 +------+ | f1 | +------+ | 95 | +------+ 1 row in set (0.00 sec) mysql> SELECT f1 FROM (SELECT DATE_ADD('2015-06-23', INTERVAL 1 MINUTE_SECOND)%c2 AS `f1` FROM t WHERE LN(0.5) NOT IN (SELECT c1 FROM t)) AS t1 WHERE 1; -- sql2 +------+ | f1 | +------+ | 41 | | 1 | +------+ 2 rows in set (0.00 sec) How to repeat: drop table if exists t; CREATE TABLE t (c1 BIGINT UNSIGNED, c2 DECIMAL(40,20), key(c1)); INSERT INTO t VALUES (2,120),(0,2); SELECT f1 FROM (SELECT DATE_ADD('2015-06-23', INTERVAL 1 MINUTE_SECOND)%c2 AS `f1` FROM t WHERE LN(0.5) NOT IN (SELECT c1 FROM t)) AS t1 WHERE `f1` != 1; -- sql1 SELECT f1 FROM (SELECT DATE_ADD('2015-06-23', INTERVAL 1 MINUTE_SECOND)%c2 AS `f1` FROM t WHERE LN(0.5) NOT IN (SELECT c1 FROM t)) AS t1 WHERE 1; -- sql2 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.11, it cannot be reproduced in mysql:5.7.10: mysql> select version(); +-----------+ | version() | +-----------+ | 5.7.10 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT f1 FROM (SELECT DATE_ADD('2015-06-23', INTERVAL 1 MINUTE_SECOND)%c2 AS `f1` FROM t WHERE LN(0.5) NOT IN (SELECT c1 FROM t)) AS t1 WHERE `f1` != 1; -- sql1 Empty set (0.00 sec) mysql> SELECT f1 FROM (SELECT DATE_ADD('2015-06-23', INTERVAL 1 MINUTE_SECOND)%c2 AS `f1` FROM t WHERE LN(0.5) NOT IN (SELECT c1 FROM t)) AS t1 WHERE 1; -- sql2 Empty set (0.00 sec)