Simple MySQL join on varchar(100) fields not working

Question

So, I'm making a join for mysql to filter out some bad data and I run into this weird issue.

• Both tables are connected by payment_transaction_id.
• They both have the value 3463.
• A joined result returns no rows.
• Both tables have this value.

Proof that the record is in card_transaction_log:

select count(*)
from card_transaction_log 
where payment_transaction_id = 3463;
>> 1

Proof that the record is in transaction:

select count(*)
from transaction 
where payment_transaction_id = 3463;
>> 1

But the join doesn't work.

select count(*)
from card_transaction_log a, transaction b
where a.payment_transaction_id = b.payment_transaction_id
and a.payment_transaction_id = 3463;
>> 0

Honestly, I've never seen anything like this before in mysql. I even checked with my colleague to make sure that I wasn't going crazy and/or dumb.

UPDATE:

While this is the same as above, this query doesn't work either:

select count(*)
from card_transaction_log a
join transaction b
on a.payment_transaction_id = b.payment_transaction_id
where a.payment_transaction_id = 3463;
>> 0

Answer 1

What type is payment_transaction_id ? I suspect it is not an INT but a VARCHAR. If you try to compare a VARCHAR with an INT, MySQL will automatically cast it to an INT but some weird things could happen, eg:

'3463' = 3463

but also

'3463a' = 3463

but

'3463a' != '3463b'

Please see fiddle here. You can test your queries like this:

select count(*)
from card_transaction_log 
where payment_transaction_id = '3463';

and I suspect that at least one of your queries will return 0. Or you can force your join to use the integer value:

select count(*)
from card_transaction_log a
join transaction b
on a.payment_transaction_id+0 = b.payment_transaction_id+0
where a.payment_transaction_id = 3463;

Answer 2

In my case this turned out to be because of a hidden character in the data.

I had imported a CSV containing a list of emails using:

LOAD DATA INFILE 'c:/temp/users.csv' 
INTO TABLE cloudusers 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 0 ROWS;

As a result there was a return character at the end of each VARCHAR. The strings looked identical but weren't.

When I re-did the import using

LOAD DATA INFILE 'c:/temp/users.csv' 
INTO TABLE cloudusers 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 0 ROWS;

(note the '\r\n' instead of '\n') the resulting VARCHARs subsequently matched, and all the joins worked.