Bug #120535 Incorrect Query Result: IN and NOT IN return the same row simultaneously (boolean logic violation)

• Submitted: 24 May 5:29
• Modified: 26 May 12:42
• Reporter: Jason Tang
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.45
• OS: Any
• CPU Architecture: Any

[24 May 5:29] Jason Tang

Description:
When using IN / NOT IN with a COALESCE() expression that returns a FLOAT/DOUBLE value, MySQL returns the same row for both conditions:
WHERE (col) IN ( ... ) → returns row
WHERE (col) NOT IN ( ... ) → also returns the same row

How to repeat:
DROP DATABASE IF EXISTS database0;
CREATE DATABASE database0;
USE database0;

CREATE TABLE `t0` (
  `c0` smallint STORAGE DISK DEFAULT NULL COMMENT 'asdf',
  KEY `i0` (`c0`) USING BTREE
);

INSERT INTO t0(c0) VALUES (1);

-- Query 1: returns row
SELECT ALL t0.c0 AS ref0
FROM t0
WHERE (t0.c0) IN (COALESCE(0.5995035972593574, "yo?x&I", '', NULL));

-- Query 2: returns THE SAME row (WRONG)
SELECT ALL t0.c0 AS ref0
FROM t0
WHERE (t0.c0) NOT IN (COALESCE(0.5995035972593574, "yo?x&I", '', NULL));

[26 May 12:42] Roy Lyseng

Thank you for the bug report.
Verified as described.