MINUS not working for the same value in Snowflake

Question

I loaded data from oracle to snowflake in table1 using informatica.

and the same data we have in snowflake table already table2.

i want to perform minus query for testing but it doesn't work as expected.

eg col1 field value is 1.21 in table1 and the datatype is same as snowflake table2. col1 fields value is 1.21 in table2

when i run

select col1 form table1 minus select col1 from table2

it gives two rows but when we check those records value is same.

What could be this issue ?

Any leads are appreciated.

Thanks .

Answer 1

given

select 1.21
minus 
select 1.21;

gives no rows, it suspect it's a type thing.

but

select 1.2100000000000001::double as a
minus 
select 1.2099999999999999::double as a;

gives 1.21, but also if you output those values you see

select 1.2100000000000001::double as a
union 
select 1.2099999999999999::double as a;

values:

1.21
1.21

which is the default format of 6 decimal places of these values

adding one more level of decimal places there is magically no difference..

select 1.21000000000000001::double as a
minus 
select 1.20999999999999999::double as a;

basically floating point number are not how they appear. ether cast them to some smaller type like

select 1.2100000000000001::number(30,3) as a
minus 
select 1.2099999999999999::number(30,3) as a;

gives no "difference"