Bug #119033 Incorrect filtering when comparing a BINARY-cast integer to a BIT column with a UNIQUE key
Submitted: 18 Sep 9:16 Modified: 19 Sep 15:49
Reporter: hongtao zhou Email Updates:
Status: Analyzing Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:9.4.0 8.4.6 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[18 Sep 9:16] hongtao zhou 
Description:
This bug manifests as an incorrect result set where rows are improperly filtered by the WHERE clause. The issue occurs during a comparison between a BIT type column and a BINARY-cast integer. The presence of a UNIQUE key on the BIT column is the essential trigger for this incorrect behavior.

How to repeat:
CREATE TABLE t0 (c1 BIT (24) UNIQUE);
INSERT INTO t0 (c1) VALUES (b'000100110000110000100110');
INSERT INTO t0 (c1) VALUES (b'001110001011010010101101');
SELECT c1 AS ca1 FROM t0 WHERE (BINARY -98) <= (c1) ; -- return 001110001011010010101101
SELECT SUM(count) FROM (SELECT ((BINARY -98)<= (c1)) IS TRUE AS count FROM t0 ORDER BY c1) AS ta_norec; --return 2