Bug #114781 bit_col and char_col comparing cause result incorrect

• Submitted: 25 Apr 2024 9:34
• Modified: 25 Apr 2024 10:10
• Reporter: haizhen xue
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[25 Apr 2024 9:34] haizhen xue

Description:
create table t1 (`date_col` date DEFAULT NULL, `bit_col` bit(8) DEFAULT b'0',KEY `ndx_date_col` (`date_col`),  KEY `ndx_bit_col` (`bit_col`));
create table t2 (`date_col` date DEFAULT NULL, `char_col` char(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,FULLTEXT KEY `ndx_char_col` (`char_col`));
insert into t1 values('1000-01-01',b'0');
insert into t2 values('1000-01-01','aaaa');
Query A：
SELECT A.date_col,A.bit_col,B.date_col,B.char_col FROM t1 AS A right join t2 AS B ON true where B.date_col='1000-01-01' order by 1,2;

Query B：
SELECT A.date_col,A.bit_col,B.date_col,B.char_col FROM t1 AS A right join t2 AS B ON A.bit_col=B.char_col where B.date_col='1000-01-01' order by 1,2;

mysql> explain format=tree SELECT A.date_col,A.bit_col,B.date_col,B.char_col FROM t1 AS A right join t2 AS B ON A.bit_col=B.char_col where B.date_col='1000-01-01' order by 1,2;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                              |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Sort: A.date_col, A.bit_col
    -> Stream results  (cost=0.70 rows=1)
        -> Nested loop left join  (cost=0.70 rows=1)
            -> Filter: (B.date_col = DATE'1000-01-01')  (cost=0.35 rows=1)
                -> Table scan on B  (cost=0.35 rows=1)
            -> Filter: (cast(A.bit_col as double) = cast(B.char_col as double))  (cost=0.35 rows=1)
                -> Index lookup on A using ndx_bit_col (bit_col=B.char_col)  (cost=0.35 rows=1)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(cast(A.bit_col as double) = cast(B.char_col as double)) maybe wrong.

How to repeat:
create table t1 (`date_col` date DEFAULT NULL, `bit_col` bit(8) DEFAULT b'0',KEY `ndx_date_col` (`date_col`),  KEY `ndx_bit_col` (`bit_col`));
create table t2 (`date_col` date DEFAULT NULL, `char_col` char(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,FULLTEXT KEY `ndx_char_col` (`char_col`));
insert into t1 values('1000-01-01',b'0');
insert into t2 values('1000-01-01','aaaa');
Query A：
SELECT A.date_col,A.bit_col,B.date_col,B.char_col FROM t1 AS A right join t2 AS B ON true where B.date_col='1000-01-01' order by 1,2;

Query B：
SELECT A.date_col,A.bit_col,B.date_col,B.char_col FROM t1 AS A right join t2 AS B ON A.bit_col=B.char_col where B.date_col='1000-01-01' order by 1,2;

mysql> SELECT A.date_col,A.bit_col,B.date_col,B.char_col FROM t1 AS A right join t2 AS B ON true where B.date_col='1000-01-01' order by 1,2;
+------------+------------------+------------+----------+
| date_col   | bit_col          | date_col   | char_col |
+------------+------------------+------------+----------+
| 1000-01-01 | 0x00             | 1000-01-01 | aaaa     |
+------------+------------------+------------+----------+
1 row in set (0.00 sec)

mysql> SELECT A.date_col,A.bit_col,B.date_col,B.char_col FROM t1 AS A right join t2 AS B ON A.bit_col=B.char_col where B.date_col='1000-01-01' order by 1,2;
+----------+------------------+------------+----------+
| date_col | bit_col          | date_col   | char_col |
+----------+------------------+------------+----------+
| NULL     | NULL             | 1000-01-01 | aaaa     |
+----------+------------------+------------+----------+
1 row in set (0.00 sec)

expect Query B's result is empty.

Suggested fix:

Query B：
SELECT A.date_col,A.bit_col,B.date_col,B.char_col FROM t1 AS A right join t2 AS B ON A.bit_col=B.char_col where B.date_col='1000-01-01' order by 1,2;

expect Query B's result is empty.

[25 Apr 2024 10:10] MySQL Verification Team

Hi Mr. xue,

Thank you for your bug report.

However, this is not a bug.

According to the SQL Standard, comparing columns (or variables or values) of different types is not allowed. SQL Standard stipulates that , in that case, the query should not return any results, but instead it should return the error. 

MySQL tries to go beyond the valid standard and to find a common denominator for the different data types. Common denominator, for MySQL, between CHAR and DOUBLE is DOUBLE. Other implementation may choose a different approaches. 

Simply, put none of those is a correct one, but each SQL product has it's own set of confict resolution solutions.

This is our solution and it will not be changed.

Not a bug.