Implicit conversion causing error part of the time

Question

I have been scratching my head about this one for the last day or so - I just can't see why a procedure works in one environment, but then fails in another due to a conversion error (same data, same code).

The plan for (a heavily cut down version of) the working server is here: https://www.brentozar.com/pastetheplan/?id=B1jZWTfOf

(This doesn't seem to be working, so I've uploaded the plan XML to pastebin here: https://pastebin.com/47Q6nniw)

A bit of background for the relevant columns causing the problem:

The propertyInst table holds a column called ValueStr, which is an nvarchar data type. The GeneralLedgerCode table has a column id of data type int. Our developers are attempting to join between the two tables. The ValueStr column holds text data, XML data, int data, decimal data etc. As a matter of course we decided to add a function to ensure only integers are parsed (ISNUMERIC(ValueStr) = 1). What we didn't realise is that this would then also try to convert any decimal values - this is causing a conversion error in production of the nature:

Conversion failed when converting the nvarchar value '0.1' to data type int.

My question here is, given that the Table Scan of the propertyInst table will be picking up all numeric values including decimals, why is the implicit conversion in the scalar operator then not failing with the same conversion error? The output of the ISNUMERIC query returns some of these decimals. I can't see how the attached plan is working at all.

Does an implicit conversion operator never fail outright, and is the conversion error coming from the probe residual in the hash match operator? Then again, how could the value even make it to the operator when it would fail the implicit conversion?

As a small assistance, the plan image is here, with the Compute Scalar I'm asking about circled.

Plan

In addition, I can see that there are no actual rows logged against the scalar operator - does this mean the engine decided not to use that particular operator at runtime due to the conversion error?

Plan2

Any help would be appreciated.

Answer 1

The calculation of the compute scalar operator is deferred to when it is actually used (the probe residual of Node ID = 1) and the rows that would have failed for you are filtered out by the previous Hash Match (Node ID = 3).

Have a look at Compute Scalars, Expressions and Execution Plan Performance by Paul White for more information.

There is no indication in execution plans that evaluation of an expression is deferred, or when (at which node) it was actually evaluated. You can normally code defensively around this using CASE (which comes with an evaluation order guarantee) and/or TRY_CONVERT.

Related Microsoft feedback: SQL Server should not raise illogical errors