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
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.21
• OS: Any
• 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.