Description:
In theory, the result of sql2(DISTINCT) ⊆ the result of sql1:
SELECT 1 FROM (SELECT c2 FROM t) AS `t1` WHERE (c2 IN (SELECT c1 FROM t)) AND (c2 NOT IN ('0','0',0.1)); -- sql1
SELECT 1 FROM (SELECT DISTINCT c2 FROM t) AS `t1` WHERE (c2 IN (SELECT c1 FROM t)) AND (c2 NOT IN ('0','0',0.1)); -- 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.00 sec)
mysql> SELECT 1 FROM (SELECT c2 FROM t) AS `t1` WHERE (c2 IN (SELECT c1 FROM t)) AND (c2 NOT IN ('0','0',0.1));
Empty set (0.00 sec)
mysql> SELECT 1 FROM (SELECT DISTINCT c2 FROM t) AS `t1` WHERE (c2 IN (SELECT c1 FROM t)) AND (c2 NOT IN ('0','0',0.1));
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
How to repeat:
drop table if exists t;
CREATE TABLE t (c1 FLOAT UNSIGNED,c2 CHAR(20));
INSERT INTO t VALUES (0,'0'),(0,'0'),(0,'0'),(0,'0'),(0,'0'),(0,'0'),(0,'-0');
SELECT 1 FROM (SELECT c2 FROM t) AS `t1` WHERE (c2 IN (SELECT c1 FROM t)) AND (c2 NOT IN ('0','0',0.1));
SELECT 1 FROM (SELECT DISTINCT c2 FROM t) AS `t1` WHERE (c2 IN (SELECT c1 FROM t)) AND (c2 NOT IN ('0','0',0.1));
Suggested fix:
This is a very strange bug, hope the following list can be helpful for your debugging:
1. The bug cannot be reproduced after deleting any (0,'0') or (0,'-0') in CREATE;
2. The bug cannot be reproduced after deleting any conditions in WHERE, you cannot even delete any '0' or '0.1' in IN;
3. 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.22, it cannot be reproduced in mysql:8.0.21:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.21 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT 1 FROM (SELECT c2 FROM t) AS `t1` WHERE (c2 IN (SELECT c1 FROM t)) AND (c2 NOT IN ('0','0',0.1));
Empty set (0.00 sec)
mysql> SELECT 1 FROM (SELECT DISTINCT c2 FROM t) AS `t1` WHERE (c2 IN (SELECT c1 FROM t)) AND (c2 NOT IN ('0','0',0.1));
Empty set (0.00 sec)
Description: In theory, the result of sql2(DISTINCT) ⊆ the result of sql1: SELECT 1 FROM (SELECT c2 FROM t) AS `t1` WHERE (c2 IN (SELECT c1 FROM t)) AND (c2 NOT IN ('0','0',0.1)); -- sql1 SELECT 1 FROM (SELECT DISTINCT c2 FROM t) AS `t1` WHERE (c2 IN (SELECT c1 FROM t)) AND (c2 NOT IN ('0','0',0.1)); -- 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.00 sec) mysql> SELECT 1 FROM (SELECT c2 FROM t) AS `t1` WHERE (c2 IN (SELECT c1 FROM t)) AND (c2 NOT IN ('0','0',0.1)); Empty set (0.00 sec) mysql> SELECT 1 FROM (SELECT DISTINCT c2 FROM t) AS `t1` WHERE (c2 IN (SELECT c1 FROM t)) AND (c2 NOT IN ('0','0',0.1)); +---+ | 1 | +---+ | 1 | +---+ 1 row in set (0.00 sec) How to repeat: drop table if exists t; CREATE TABLE t (c1 FLOAT UNSIGNED,c2 CHAR(20)); INSERT INTO t VALUES (0,'0'),(0,'0'),(0,'0'),(0,'0'),(0,'0'),(0,'0'),(0,'-0'); SELECT 1 FROM (SELECT c2 FROM t) AS `t1` WHERE (c2 IN (SELECT c1 FROM t)) AND (c2 NOT IN ('0','0',0.1)); SELECT 1 FROM (SELECT DISTINCT c2 FROM t) AS `t1` WHERE (c2 IN (SELECT c1 FROM t)) AND (c2 NOT IN ('0','0',0.1)); Suggested fix: This is a very strange bug, hope the following list can be helpful for your debugging: 1. The bug cannot be reproduced after deleting any (0,'0') or (0,'-0') in CREATE; 2. The bug cannot be reproduced after deleting any conditions in WHERE, you cannot even delete any '0' or '0.1' in IN; 3. 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.22, it cannot be reproduced in mysql:8.0.21: mysql> select version(); +-----------+ | version() | +-----------+ | 8.0.21 | +-----------+ 1 row in set (0.00 sec) mysql> SELECT 1 FROM (SELECT c2 FROM t) AS `t1` WHERE (c2 IN (SELECT c1 FROM t)) AND (c2 NOT IN ('0','0',0.1)); Empty set (0.00 sec) mysql> SELECT 1 FROM (SELECT DISTINCT c2 FROM t) AS `t1` WHERE (c2 IN (SELECT c1 FROM t)) AND (c2 NOT IN ('0','0',0.1)); Empty set (0.00 sec)