Bug #100967 Join get wrong result for type bit(64)
Submitted: 27 Sep 2020 6:35 Modified: 25 Nov 2020 18:02
Reporter: Shenghui Wu Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.21 OS:Any
Assigned to: CPU Architecture:Any

[27 Sep 2020 6:35] Shenghui Wu
Description:
Create a table with column a bigint and c bit(64). Insert the values -1 and 18446744073709551615.
The result for `a=c` is zero, but if use the condition as join condition, it will get result.

How to repeat:
MySQL [test]> create table t(a bigint, b bigint unsigned, c bit(64));
Query OK, 0 rows affected (0.010 sec)

MySQL [test]> insert into t values(-1,18446744073709551615,18446744073709551615);
Query OK, 1 row affected (0.003 sec)

MySQL [test]> select a=b,a=c,b=c from t;
+------+------+------+
| a=b  | a=c  | b=c  |
+------+------+------+
|    0 |    0 |    1 |
+------+------+------+
1 row in set (0.000 sec)

MySQL [test]> select t1.a,t2.c from t t1 join t t2 on t1.a = t2.c;
+------+----------+
| a    | c        |
+------+----------+
|   -1 | ��������         |
+------+----------+
1 row in set (0.001 sec)
[27 Sep 2020 21:24] MySQL Verification Team
Thank you for the bug report.
[25 Nov 2020 12:27] Tor Didriksen
Posted by developer:
 
Fixed by the patch for:
    Bug #31832001 HASH JOIN MISMATCHES SIGNED AND UNSIGNED
[25 Nov 2020 18:02] Paul DuBois
Posted by developer:
 
Fixed in 8.0.23.

When performing a hash join, the optimizer could register a false
match between a negative integer value and a very large unsigned
integer value.