Bug #116556 Inconsistent Results with IN Clause for Binary Data
Submitted: 5 Nov 2024 11:04 Modified: 5 Nov 2024 12:15
Reporter: Wenqian Deng Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:9.0 OS:Any
Assigned to: CPU Architecture:Any

[5 Nov 2024 11:04] Wenqian Deng
Description:
A discrepancy occurs when using an IN clause with binary values in MySQL. Specifically, querying a derived table (t1) containing IF expressions produces different results compared to querying directly on the same expression in the original table (t0). 

How to repeat:
1. Create table t0 and insert records

CREATE TABLE t0 (c0 BIT, c1 BIT, c2 TIME);
INSERT INTO t0 VALUES (1, 0, '14:23:54');
INSERT INTO t0 VALUES (1, 1, '20:01:03');
INSERT INTO t0 VALUES (1, 0, '23:15:47');
INSERT INTO t0 VALUES (1, 1, '00:29:58');
INSERT INTO t0 VALUES (0, 0, '11:06:48');
INSERT INTO t0 VALUES (1, 0, '06:53:25');
INSERT INTO t0 VALUES (1, 1, '10:43:40');
INSERT INTO t0 VALUES (0, 0, '20:20:48');
INSERT INTO t0 VALUES (0, 1, '15:29:47');
INSERT INTO t0 VALUES (1, 0, '20:35:02');
INSERT INTO t0 VALUES (1, 0, '09:26:20');

2. Query table t0 with direct IF expression and binary value in IN clause

mysql> SELECT (IF(t0.c0, t0.c1, t0.c2)) FROM t0 WHERE ((IF(t0.c0, t0.c1, t0.c2)) IN (11, 0xB3FDDFA760));
Field   1:  `(IF(t0.c0, t0.c1, t0.c2))`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     10
Max_length: 0
Decimals:   31
Flags:      BINARY 

0 rows in set (0.01 sec)

3. Create derived table t1 using same IF expression

CREATE TABLE t1 AS (SELECT (IF(t0.c0, t0.c1, t0.c2)) AS c0 FROM t0);

4. Query with IN clause using derived table t1

mysql> SELECT t1.c0 FROM t1 WHERE (t1.c0 IN (11, 0xB3FDDFA760));
Field   1:  `c0`
Catalog:    `def`
Database:   `test`
Table:      `t1`
Org_table:  `t1`
Type:       VAR_STRING
Collation:  binary (63)
Length:     10
Max_length: 8
Decimals:   0
Flags:      BINARY 

+--------------------+
| c0                 |
+--------------------+
| 0x31313A30363A3438 |
+--------------------+
1 row in set, 11 warnings (0.00 sec)

Expected Result:

The SELECT results from t0 and t1 should be identical, as they are expected to have the same types and values. 
However, the query on t1 with IN condition returns a single row, while the direct query with IF expression returns zero rows.
[5 Nov 2024 11:54] MySQL Verification Team
Hi Mr. Deng,

Thank you for your bug report.

However , this is not a bug.

SQL Standard clearly stipulates that you can not use different column and data types in a single expression.

However, you are mixing types BIT, TIME and LONG INT in a single expression. 

MySQL tries it's best (contradicting SQL Standard) and tries to find a common denominator, which in this case is DOUBLE.

Hence , what you get is expected behaviour ......

Not a bug.
[5 Nov 2024 12:15] Wenqian Deng
However, I think the issue lies in MySQL’s inconsistent handling of identical data. 
Why do SELECT t1.c0... and SELECT (IF(t0.c0, t0.c1, t0.c2))... lead to different results, given that the value stored in t1.c0 should be the same as (IF(t0.c0, t0.c1, t0.c2))?