Bug #117223 Query results are unexpected with BIT data type and IN operator.

• Submitted: 17 Jan 7:33
• Modified: 17 Jan 9:16
• Reporter: wang jack
• Status: Verified
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 8.4.1 9.1.0, 8.0.40
• OS: Windows
• CPU Architecture: x86

[17 Jan 7:33] wang jack

Description:
From my understanding, the first query below should return empty, but it is actually return 1 row.
I am unsure if this CASE is the same as https://bugs.mysql.com/bug.php?id=117222, so I have submitted another one. If it is a duplicate, please let me know.

mysql> SELECT t0.c0 FROM t0 WHERE (0.23472264375817165) IN (t0.c0);
+------------+
| c0         |
+------------+
| 0x000000   |
+------------+
1 row in set (0.00 sec)

mysql> SELECT (0.23472264375817165) IN (t0.c0) FROM t0;
+----------------------------------+
| (0.23472264375817165) IN (t0.c0) |
+----------------------------------+
|                                0 |
+----------------------------------+
1 row in set (0.00 sec)

How to repeat:
DROP TABLE IF EXISTS t0;
CREATE TABLE t0(c0 BIT(19) ) ;
CREATE INDEX i0 ON t0(c0) VISIBLE;
INSERT LOW_PRIORITY INTO t0(c0) VALUES(0);

SELECT t0.c0 FROM t0 WHERE (0.23472264375817165) IN (t0.c0); -- 1 row
SELECT (0.23472264375817165) IN (t0.c0) FROM t0;             -- 1 row: {0}

[17 Jan 9:16] MySQL Verification Team

Hello wang jack,

Thank you for the report and test case.
Verified as described.

regards,
Umesh