Bug #120581 unique index causes incorrect result for IN comparison with implicit type conversion

• Submitted: 31 May 9:02
• Reporter: fan liu
• Status: Open
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.44
• OS: MacOS
• CPU Architecture: Any

[31 May 9:02] fan liu

Description:
A SELECT query returns an empty set when a UNIQUE index exists.
After dropping the index, the expected row is returned.

SELECT t0.c1 FROM t0 WHERE (COALESCE(NULL, '8h')) IN (t0.c1);
empty set;
drop index i0 on t0;
SELECT t0.c1 FROM t0 WHERE (COALESCE(NULL, '8h')) IN (t0.c1);
c1|
--+
 8|

How to repeat:
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
USE test;
CREATE TABLE `t0` (
  `c1` decimal(10,0) DEFAULT NULL,
  UNIQUE KEY `i0` (`c1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO t0(c1) VALUES(8);
INSERT INTO t0(c1) VALUES(NULL);
SELECT t0.c1 FROM t0 WHERE (COALESCE(NULL, '8h')) IN (t0.c1);
empty set;
drop index i0 on t0;
SELECT t0.c1 FROM t0 WHERE (COALESCE(NULL, '8h')) IN (t0.c1);
c1|
--+
 8|