Bug #119034 Incorrect result when comparing a numeric string literal to a BIT column with a UNIQUE key
Submitted: 18 Sep 9:34 Modified: 22 Sep 16:55
Reporter: zz z Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:9.4.0 8.4.6 OS:Any
Assigned to: CPU Architecture:Any

[18 Sep 9:34] zz z 
Description:
When a BIT column has a UNIQUE key, a predicate comparing that column to a string literal behaves incorrectly, leading to rows being improperly filtered from the result set. The bug is triggered by the presence of the UNIQUE key.
The inconsistency is evident when comparing the output of a WHERE clause filter against a direct evaluation of the same boolean expression in a SELECT list. The latter correctly computes the result for all rows, while the former does not.

How to repeat:
CREATE TABLE t0 ( c2 BIT(7), UNIQUE (c2)) ;
INSERT  INTO t0 (c2) VALUES (b'1100011');
INSERT INTO t0 (c2) VALUES (b'0010110');

SELECT c2 AS ca1 FROM t0 WHERE (NOT (('9') >= (c2)));
-- return 1100011
SELECT SUM(count) FROM (SELECT ((NOT (('9') >= (c2)))) IS TRUE AS count FROM t0) AS ta_norec;
-- return 2
[22 Sep 16:55] MySQL Verification Team 
Thank you for the report.