Bug #106713 Incorrect Semi-join Execution
Submitted: 12 Mar 2022 6:02 Modified: 12 Mar 2022 8:15
Reporter: Xiu Tang Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:8.0.28 OS:Any
Assigned to: CPU Architecture:Any

[12 Mar 2022 6:02] Xiu Tang 
Description:
no_semijoin produce wrong results

How to repeat:
CREATE TABLE IF NOT EXISTS t0(c0 DECIMAL ZEROFILL  COLUMN_FORMAT DEFAULT );

INSERT HIGH_PRIORITY INTO t0(c0) VALUES(NULL), (2000-09-06), (NULL);
INSERT INTO t0(c0) VALUES(NULL);
INSERT DELAYED INTO t0(c0) VALUES(2016-02-18);

mysql> SELECT t0.c0 FROM t0 WHERE t0.c0 IN (SELECT t0.c0 FROM t0 WHERE (t0.c0 NOT IN (SELECT t0.c0 FROM t0 WHERE t0.c0 )) = (t0.c0) );
+------------+
| ref0       |
+------------+
| 0000001985 |
| 0000001996 |
+------------+
2 rows in set (0.00 sec)

mysql> SELECT t0.c0 FROM t0 WHERE t0.c0 IN (SELECT /*+ no_semijoin()*/ t0.c0 FROM t0 WHERE (t0.c0 NOT IN (SELECT t0.c0 FROM t0 WHERE t0.c0 )) = (t0.c0) );
Empty set (0.00 sec)
[12 Mar 2022 8:15] MySQL Verification Team 
Hello Xiu Tang,

Thank you for the report and test case.

regards,
Umesh