Simple MySQL joins varchar (100) fields that don't work

Question

So, I am creating a connection for mysql to filter out some bad data, and I ran into this strange problem.

• Both tables are linked by payment_transaction_id .
• Both of them have a value of 3463 .
• The linked result does not return rows.
• Both tables have this value.

Proof that the entry is in the card_transaction_log file:

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

Proof that the record is in a transaction:

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

But the connection does not 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 have never seen anything like it in mysql. I even checked with my colleague to make sure that I was not going crazy and / or dumb.

UPDATE:

Although this is the same as above, this request does not work:

 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 of payment_transaction_id ? I suspect this is not INT, but VARCHAR. If you try to compare VARCHAR with INT, MySQL will automatically add it to INT, but some strange things may happen, for example:

 '3463' = 3463 

but also

 '3463a' = 3463 

but

 '3463a' != '3463b' 

See the fiddle here. You can check your queries as follows:

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

and I suspect at least one of your queries will return 0. Or you can force your connection to use an 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

Can you try:

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

Answer 3

Today I had the same problem and this is what I found out. Despite the fact that the SQL selection in table A and the SQL selection in table B show equal values ​​for primary and foreign keys, the values ​​were actually different ... a '\ n', which did not appear when the values ​​were displayed in the grid, was the final nature of all my foreign key values ​​... The root reason, I did not notice that the file that I used to populate my table through "import" was a damaged CSV file found on the Internet.

you can do a quick test, just manually update a few foreign and primary key values, which are expected to join and run your request again. If it returns results, you have found the root cause!