Bug #119357 Incorrect String Comparison with HEAP BIT Column in NOT IN Subquery

• Submitted: 10 Nov 16:36
• Modified: 12 Nov 4:42
• Reporter: zz z
• Status: Duplicate
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 9.4.0 8.4.6
• OS: Any
• CPU Architecture: Any

[10 Nov 16:36] zz z

Description:
The evaluation of a NOT IN subquery is inconsistent when it operates on a HEAP engine table and reads from a BIT column containing only NULL values. According to SQL's three-valued logic, the result of ... NOT IN (NULL) should be NULL. This is handled correctly in a WHERE clause, where the query returns an empty set. However, when the same expression is moved into the SELECT list of a derived table, it is incorrectly evaluated as TRUE, causing the SUM aggregation to return the total row count of 2 instead of the expected 0

How to repeat:
CREATE TABLE t170 (c1 BIT, c2 CHAR (14)) ENGINE HEAP;
INSERT t170 () VALUES ();
INSERT t170 () VALUES ();

SELECT NULL FROM t170 WHERE ('2031-06-25 14:26:08' NOT IN (SELECT t170.c1 FROM t170));
-- empty set
SELECT SUM(count) FROM (SELECT (('2031-06-25 14:26:08' NOT IN (SELECT t170.c1 FROM t170))) IS TRUE AS count FROM t170) AS ta_norec;
--2

[12 Nov 4:42] zz z

sorry,it is the same as Bug #119349