MySQL Bugs: #105842: joining on integer and varchar fields has unexpected behaviour

Bug #105842	joining on integer and varchar fields has unexpected behaviour
Submitted:	8 Dec 2021 21:43	Modified:	9 Dec 2021 12:57
Reporter:	Robbert Nix	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	8.0.26	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
if you join tables on fields that are integer and varchar, unexpected links are made 

for example integer field with value 123 is linked to string field with value '123ABC'

How to repeat:
CREATE SCHEMA `test` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ;

create table test.t1_int(field_int integer);
insert into test.t1_int(field_int) value ( 123 ); 
insert into test.t1_int(field_int) value ( 234 ); 
insert into test.t1_int(field_int) value ( 345 ); 

create table test.t2_str(field_str varchar(100));
insert into test.t2_str(field_str) value ( '123ABC' ); 
insert into test.t2_str(field_str) value ( '234' ); 
insert into test.t2_str(field_str) value ( '456CHAR' ); 

select * from  test.t1_int left join  test.t2_str s on i.field_int = s.field_str;

output:
 field_int, field_str
'123', '123ABC'         <---   I did not expect this record
'234', '234'
'345', NULL

https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html

Strings are automatically converted to numbers and numbers to strings as necessary.

root@localhost:hhs_test15:44:52>select '111'='111asdf' as res;
+-----+
| res |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

root@localhost:hhs_test15:45:01>select 111='111asdf' as res;
+-----+
| res |
+-----+
|   1 |
+-----+
1 row in set, 1 warning (0.00 sec)

root@localhost:hhs_test15:45:06>select 111='11asdf' as res;
+-----+
| res |
+-----+
|   0 |
+-----+
1 row in set, 1 warning (0.00 sec)

Hi,

Thank you for your bug report.

However, this is not a bug.

In the cases like this, both strings and integers are converted to double values. This is fully described in our Reference Manual.

Not a bug.