Coalesce Adding Extra Characters

Question

I have the following values:

table_1:

value
123

table_2:

value
456


select 
t1.value as t1_value
,t2.value as t2_value
,coalesce(t1.value,t2.value,null) as coalesced_value
from
    table_1 as t1
left join
    table_2 as t2
    on
      t1.it_does_not_matter = t2.it_does_not_matter


Output:

t1_value     t2_value     coalesced_value
123          456          123.00000

Why do I have the extra 0 values at the end? How do I make it so my output is this:

Desired Output:
t1_value     t2_value     coalesced_value
123          456          123

Answer 1

For starters coalesce to NULL is pointless, unless you are just showing that it's the magic third value.

coalesce(t1.value,t2.value,null)

but in Snowflake

WITH a as ( select 123::number a ), b as (select 456 as b)
SELECT a.a, b.b, coalesce(a.a, b.b, null)
FROM a LEFT JOIN b ON true;    

gives:

A   B   COALESCE(A.A, B.B, NULL)
123 456 123

changing the second to floating

WITH a as ( select 123::number a ), b as (select 456.1 as b)
SELECT a.a, b.b, coalesce(a.a, b.b, null)
FROM a LEFT JOIN b ON true;    

gives:

A   B       COALESCE(A.A, B.B, NULL)
123 456.1   123.0

but casting the float as a double

WITH a as ( select 123::number a ), b as (select 456.1::double as b)
SELECT a.a, b.b, coalesce(a.a, b.b, null)
FROM a LEFT JOIN b ON true;    

gives:

A   B       COALESCE(A.A, B.B, NULL)
123 456.1   123

so I question what the types of your two tables are.. and if you want it to always be a number just cast the coalesce.

coalesce(a.a, b.b, null)::number

this could be happening if you data is variant, and other columns are floating and thus it's treat it all as floating, but you are just seeing one row.