Bug #119588 Incorrect result in JOIN with IN subquery

Submitted: 24 Dec 2025 3:03 Modified: 26 Dec 2025 17:22
Reporter: Seren Zhou Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:8.0.44 OS:Any
Assigned to: CPU Architecture:Any

[24 Dec 2025 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 2025 17:22] Roy Lyseng 
Verified as described.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.038 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.