[MySQL] DATE data type comparing BUG

Good day fellow UiPath RPA Developers,

Has anyone here encountered a bug/issue on DATE and DATE TIME format on MySQL?

I have this query and it is not working on DATE and DATE TIME format. I tried other columns with different data types like VARCHAR or DECIMAL and this query is working fine.

UPDATE int_cleaned_trans SET report_flag = 'NO MATCH' 
WHERE batch_process_id = '7701' 
AND (trans_date) NOT IN 
(SELECT distinct(trans_date) FROM ext_cleaned_trans 
WHERE batch_process_id = '7701' 
AND flag='SUCCESS');

This is a sample INPUT:

And this is the expected OUTPUT:

I tried comparing other columns like instead of comparing ‘trans_date’(date) I compared using ‘amount’ (decimal (15,2)) or ‘ref_key_1’(varchar(50)) or ‘ref_key_2’(varchar(50)) and the query was working well. I think the main issue is because trans_date is in DATE format.

Is it bug or is there something wrong with the query provided above?

Any kind of help/inputs will be appreciated.

Best Regards,
Anthony Jr.

It seems like the query is not the problem but the data because trans_id value has duplicate values on other rows.