Join Erroring on Value That Doesn't Exist

November 19, 2021

I am finding that a subquery that is built to return some specific IDs does not work within a join condition as I would expect. However if I use the same subquery to build a table full of those values, and use it in the same join clause, the query is a success.

It feels as is Snowflake is scanning the source table of the subquery and erroring out on a value, even though that value isn’t actually in the result set of the subquery.

See code example below:

create table CompanyAssetExternalIDs_TEMP as 
    select distinct to_number(external_id,38,0) as external_id
    from "Company_assets_ev"
    where provider = 'screenplay'
    and external_id is not null
    and external_id != ''
    and external_id != 'NULL'
        
// This query doesn't work, it fails on "Numeric value '6162211ca2d88b37ac48dc0d' is not recognized" however that value doesn't exist in the subquery, nor in the main select table
select
last_day(LA.created_at::date,'month') Month,
LA.rights_holder
from "linear_asset" LA
//This sub query is the same SQL as the create table command above so we can be sure they only contain the same values
inner join 
(
    select distinct to_number(external_id,38,0) as external_id
    from "Company_assets_ev"
    where provider = 'screenplay'
    and external_id is not null
    and external_id != ''
    and external_id != 'NULL'
) SubQueryJoin on SubQueryJoin.external_id = LA.External_id

//This query works, despite the fact that its joined on, what should be, the exact same set of data as the subquery above
select
last_day(LA.created_at::date,'month') Month,
LA.rights_holder
from "linear_asset" LA
inner join "Company_DW"."PUBLIC"."CompanyAssetExternalIDs_TEMP" as TableJoin on TableJoin.external_id = LA.external_id

>Solution :

Using try_to_number to nullify non-number values:

select last_day(LA.created_at::date,'month') Month,
       LA.rights_holder
from "linear_asset" LA
inner join 
(
    select distinct try_to_number(external_id,38,0) as external_id
    from "Company_assets_ev"
    where provider = 'screenplay'
    and external_id is not null
    and external_id != ''
    and external_id != 'NULL'
) SubQueryJoin on SubQueryJoin.external_id = LA.External_id