Bug #119349 Incorrect String Comparison with MyISAM BIT Column in IN Subquery

• Submitted: 10 Nov 12:06
• Modified: 20 Nov 13:04
• Reporter: zz z
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 9.4.0
• OS: Any
• CPU Architecture: Any

[10 Nov 12:06] zz z

Description:
An evaluation error occurs when the left-hand side of an IN subquery is a string literal ('L') and the subquery itself reads from a BIT column of a MyISAM table containing both NULL and b'0'. According to MySQL's type conversion rules, the string 'L' should be converted to the numeric value 0 when compared against the BIT type, which should make the result of 'L' IN (NULL, b'0') be TRUE. However, when this expression is evaluated in the SELECT list of a derived table, it is incorrectly evaluated as FALSE or NULL. This leads to the final SUM aggregation returning 0 instead of the expected row count of 3, exposing a flaw in the implicit conversion logic between string literals and BIT types on MyISAM tables.

How to repeat:
CREATE TABLE t152 (c1 BIT) ENGINE MyISAM;
INSERT t152 () VALUES ();
INSERT t152 () VALUES ();
INSERT t152 () VALUES (b'0');
SELECT * FROM t152 WHERE (('L' IN (SELECT t152.c1 FROM t152)));
-- 3 rows
SELECT SUM(count) FROM (SELECT ((('L' IN (SELECT t152.c1 FROM t152)))) IS TRUE AS count FROM t152) AS ta_norec;
-- 0

[12 Nov 4:41] zz z

MEMORY HEAP OR ARCHIVE can also trigger the bug
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
CREATE TABLE t2322 (c2 BIT) ENGINE ARCHIVE;
INSERT t2322 () VALUES (b'0');
INSERT t2322 () VALUES (b'0');
INSERT t2322 () VALUES ();
SELECT * FROM t2322 WHERE (2002 != ALL (SELECT t2322.c2 FROM t2322));
-- empty set
SELECT SUM(count) FROM (SELECT ((2002 != ALL (SELECT t2322.c2 FROM t2322))) IS TRUE AS count FROM t2322) AS ta_norec;
-- 3

CREATE TABLE t1962 (c1 INT, c2 BIT) ENGINE MEMORY;
INSERT t1962 () VALUES ();
INSERT t1962 () VALUES ();
SELECT NULL FROM t1962 WHERE(22000000 != ALL (SELECT t1962.c2 FROM t1962));
-- Returned Row Count: 0
SELECT SUM(count) FROM (SELECT ((22000000 != ALL (SELECT t1962.c2 FROM t1962))) IS TRUE AS count FROM t1962) AS ta_norec;
-- Returned Row Count: 2

[12 Nov 4:46] zz z

it can be triggered in 9.4.0 not 8.4.6

[20 Nov 13:04] Roy Lyseng

Verified as described.