MySQL Bugs: #119972: Incorrect Result from IN Subquery with UNIQUE Index

Bug #119972	Incorrect Result from IN Subquery with UNIQUE Index
Submitted:	2 Mar 19:47	Modified:	3 Mar 6:00
Reporter:	Seren Zhou	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	8.0.45	OS:	Any
Assigned to:		CPU Architecture:	Any

View
Add Comment
Files
Developer
Edit Submission
View Progress Log
Contributions

Description:
MySQL returns incorrect results when executing `string_value IN (SELECT bit_column FROM table)` when the `bit_column` has a UNIQUE index.

How to repeat:
CREATE TABLE t1 (
    c1 BIT(1) DEFAULT NULL,
    UNIQUE KEY (c1)
) ;

INSERT INTO t1 VALUES (0x00), (0x01);

SELECT 'database27' IN (SELECT c1 FROM t1);
-- Actual: 0

SELECT 'database27' IN (SELECT /*+ NO_INDEX(t1 c1) */ c1 FROM t1);
-- Actual: 1

Hi Seren Zhou,

Thank you for the test case. Verified as described.