Bug #112912 Parsing a string To BIT type got a wrong result

• Submitted: 1 Nov 2023 8:45
• Modified: 1 Nov 2023 14:12
• Reporter: Alex Wong
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[1 Nov 2023 8:45] Alex Wong

Description:
Hello,

I have encountered an unexpected result in my MySQL program, and I would like to report it as a potential bug. Here is the code that I used:
CREATE TABLE IF NOT EXISTS t0(c0 BIT(5));
CREATE INDEX i1 USING HASH ON t0(c0);
INSERT IGNORE INTO t0(c0) VALUES(""); 

SELECT * FROM t0; -- expect: 00000 ,actual:00000
SELECT t0.c0 FROM t0 WHERE  ("a0101") IN (t0.c0); -- expect:00000, actual: no rows

I checked the result of the query SELECT ("a0101") IN (t0.c0) and it returned 1, indicating that the condition should be satisfied. Therefore, I expected the query SELECT t0.c0 FROM t0 WHERE ("a0101") IN (t0.c0) to return one row. However, it did not return any rows. When I cancel the index i1, the result is correct.

Your assistance in investigating this issue would be greatly appreciated. Thank you for your attention and support.

Best regards,
Ruifeng

How to repeat:
CREATE TABLE IF NOT EXISTS t0(c0 BIT(5));
CREATE INDEX i1 USING HASH ON t0(c0);
INSERT IGNORE INTO t0(c0) VALUES(""); 

SELECT * FROM t0; -- expect: 00000 ,actual:00000
SELECT t0.c0 FROM t0 WHERE  ("a0101") IN (t0.c0); -- expect:00000, actual: no rows
SELECT ("a0101") IN (t0.c0) from t0; -- 1

Suggested fix:
I doubt this problem is related to the index using hash.

[1 Nov 2023 14:12] MySQL Verification Team

HI Mr. Wong,

Thank you for your bug report.

Here is what we have got as the output from your test case, without hash index:

SELECT * FROM t0;
-------------------
c0
\0

You are inserting empty string into a BIT column and you got the only bit you inserted

SELECT t0.c0 FROM t0 WHERE  ("a0101") IN (t0.c0);
----------------------------------------------------
c0
\0

The above  is wrong.

SELECT ("a0101") IN (t0.c0) from t0;
1

The above is correct, since there is a row in the table.

With hash inded , the second query returns empty result which is correct.

So, this is a verified bug, but of very low severity.