Why would one specific SQL Server Database fail to convert VARCHAR to NUMERIC?

Question

Is it possible that 2 different SQL Server databases, having the same schema and data, could process a query in a different way, and one of them throwing a " Error converting data type varchar to numeric. " error, while the other one doesn't?

For example, see this query:

SELECT count(*) FROM DEPARTMENTS dpt
inner join
EMPLOYEES emp
on dpt.subgroup_id = emp.subgroup_id
WHERE
emp.employee_id = 12856
and
dpt.department_id in
(   
    select cm.mapped_value from CONFIGURATIONS c
    inner join
    CONFIG_MAPPINGS cm on cm.configuration_id = c.id
    where
    c.name = 'Department that can override self-destruction protocol'
)

The inner select subquery is returning either a value that can be cast to a number, or no results at all. So what I've noticed is that:

1. One database running Microsoft SQL Server 2012 - 11.0.2100.60 executes the query without errors, as long as the inner statement will not return a non-numeric value
2. Another database running Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 is always throwing the "Error converting data type varchar to numeric." error, regardless of what the inner select query returns.

I've also noticed that if I change the subquery with another one that returns exactly the same, but without the inner join, the 2nd database is not throwing the error

SELECT count(*) FROM DEPARTMENTS dpt
inner join
EMPLOYEES emp
on dpt.subgroup_id = emp.subgroup_id
WHERE
emp.employee_id = 12856
and
dpt.department_id in
(   
    select cm.mapped_value FROM CONFIG_MAPPINGS
    WHERE cm.configuration_id = 'DPT_OVERRIDE_SDP'
)

I know that I should improve the query anyway, adding a ISNUMERIC check, but just out of curiosity, my question is:

Could it be that one these databases is processing the query in a different way (perhaps different execution plan) and always exposes the error, while the other database manages to execute the query?

Answer 1

After comparing the execution plans on both databases, I've noticed they are quite different.

If the plan was joining DEPARTMENTS and CONFIG_MAPPINGS before joining CONFIG_MAPPINGS with CONFIGURATIONS, and before the c.name = .. filter was applied, then we would be joining on dpt.department_id (numeric) = cm.mapped_value, while mapped_value would also contain non-numeric values at that point.

This is the case with the 2nd database, and this explains the conversion error