Bug #119588 Incorrect result in JOIN with IN subquery

• Submitted: 24 Dec 3:03
• Modified: 26 Dec 17:22
• Reporter: Seren Zhou
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.44
• OS: Any
• CPU Architecture: Any

[24 Dec 3:03] Seren Zhou

Description:
There is a logic inconsistency in how MySQL evaluates join conditions.
This query returns results, but the ON condition (t2.c1 IN (SELECT t1.c1 FROM t1)) should be false for 'Kx' against {0x00, 0x01}.

CREATE TABLE t1(c1 BIT);
CREATE TABLE t2(c1 CHAR(20));

INSERT INTO t2 (c1) VALUES ('Kx');
INSERT INTO t1 (c1) VALUES (b'0');
INSERT INTO t1 (c1) VALUES (b'1');

SELECT t1.c1 FROM t1 JOIN t2 ON (t2.c1 IN (SELECT t1.c1 FROM t1));
/* Expected: Empty set
   Actual: 
   +------------+
   | c1         |
   +------------+
   | 0x00       |
   | 0x01       |
   +------------+
*/

How to repeat:
CREATE TABLE t1(c1 BIT);
CREATE TABLE t2(c1 CHAR(20));

INSERT INTO t2 (c1) VALUES ('Kx');
INSERT INTO t1 (c1) VALUES (b'0');
INSERT INTO t1 (c1) VALUES (b'1');

SELECT t1.c1 FROM t1 JOIN t2 ON (t2.c1 IN (SELECT t1.c1 FROM t1));

/* Expected: Empty set
   Actual: 
   +------------+
   | c1         |
   +------------+
   | 0x00       |
   | 0x01       |
   +------------+
*/

[26 Dec 17:22] Roy Lyseng

Verified as described.