Bug #119597 WHERE ... IN ... incorrectly returns rows

• Submitted: 26 Dec 2:37
• Modified: 26 Dec 17:57
• Reporter: Seren Zhou
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S1 (Critical)
• Version: 8.0.44
• OS: Any
• CPU Architecture: Any

[26 Dec 2:37] Seren Zhou

Description:
When using the IN predicate to compare a BINARY column (LHS) with a BIT column returned from a subquery (RHS), the server returns incorrect results.

How to repeat:
CREATE TABLE t1(c1 BIT);
CREATE TABLE t2(c1 BINARY(1));

INSERT INTO t1(c1) VALUES(b'0'); 
INSERT INTO t2(c1) VALUES('o');
INSERT INTO t2(c1) VALUES('E');

SELECT c1 FROM t2 WHERE t2.c1 IN (SELECT c1 FROM t1);

/*
ACTUAL RESULT (Incorrect):
+------------+
| c1         |
+------------+
| 0x6F       |
| 0x45       |
+------------+
2 rows in set

EXPECTED RESULT (Correct):
Empty set (0 rows)
*/

[26 Dec 17:57] Roy Lyseng

Verified as described.