Execution plan flips Filter and Execute Scalar when using the PK, causes cast to fail

Question

I have some data tables that where imported in from a flat file source so everything is varchar(max) except FileRecordID which is a int that hold the row number the data came from in the flat file source (and is the primary key of the imported table.)

The following query works perfectly

SELECT *
  FROM [CLIENT]
  left outer join
  (
    select cast(ENTRY_CODE as int) as ENTRY_CODE, ENTRY_DESCRIPTION
    from [ENTRY]
    inner join [TABLE] on [ENTRY].ENTRY_TABLE = [Table].FileRecordID
    where ENTRY_RECD = 'A' and TABLE_RECD = 'A' and TABLE_CODE = 'CLTDISC' and ISNUMERIC(ENTRY_CODE) = 1
  ) as discounts on CLIENT_CLASS = ENTRY_CODE
  where Client_recd = 'A'
  --and client.[FileRecordID] = 10607

I get the exact number of rows I was expecting and everything is fine. However if I uncomment that last line so I only show the record from the client data table who has client.[FileRecordID] = 10607 it reports to me

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'BK/TN' to data type int.

The problem is from cast(ENTRY_CODE as int) as ENTRY_CODE in the first outer join. ENTRY can contain text in the ENTRY_CODE column, however all records that have ENTRY_TABLE set to the line number from TABLE where TABLE_CODE = 'CLTDISC' will always be numbers only. I perform the cast as ENTRY_CODE may or may not have leading 0's and spaces so I am trying to get them to be well formed.

What appears to me to be happening is adding client.[FileRecordID] = 10607 to the outer query causes the where clause not to be evaluated before the cast to int in the inner query.

I have tried things like adding and ISNUMERIC(ENTRY_CODE) = 1 to both the inner and outer queries but it has no affect.

1. How do I fix this?
2. Can anyone explain what is happening so I can understand how to avoid this in the future?

Non filtered query planed execution

Filtered (non working) query planned execution Notice the reversed order of the Compute Scalar and the Filter

---

Other things I have tried that do not work:

• Move TABLE_CODE = 'CLTDISC' in to the on clause in the inner join.
• Added OPTION (FORCE ORDER) query hint to the query.

---

Update:
I found a solution but it is very "hackish" IMHO, I really would still like a explanation of how to avoid things like this in the future and how to do this correctly.

SELECT *
  FROM [CLIENT]
  left outer join
  (
    select ENTRY_CODE, ENTRY_DESCRIPTION
    from [ENTRY]
    inner join [TABLE] on [ENTRY].ENTRY_TABLE = [Table].FileRecordID
    where ENTRY_RECD = 'A' and TABLE_RECD = 'A' and TABLE_CODE = 'CLTDISC' and ISNUMERIC(ENTRY_CODE) = 1
  ) as discounts on CLIENT_CLASS = REPLACE(LTRIM(REPLACE(ENTRY_CODE, '0', ' ')), ' ', '0')
  where Client_recd = 'A'
  and client.[FileRecordID] = 10607

Answer 1

I'm sure there are dba.se and StackOverflow duplicates, but it was far faster for me to post this link:

https://feedback.azure.com/forums/908035-sql-server/suggestions/32912431-sql-server-should-not-raise-illogical-errors

In short, the Query Optimizer is really free to rewrite the plan as it sees best. Sometimes, it means a column is transformed (CAST -> int) close to retrieval so that it doesn't have to be carried many steps forward before performing the CAST.

The only sure-fire way of preventing the error is to look at the CAST expression itself, e.g.:

cast(CASE WHEN ISNUMERIC(ENTRY_CODE)=1 THEN ENTRY_CODE END as int)

Answer 2

To test for values that can be converted to INT successfully, you need to test that all the characters are digits, or in other words, the string doesn't contain any non-digit characters.

WHERE EntryCode NOT LIKE '%[^0-9]%'

The query optimizer should push the predicate down into the index scan on ENTRY, eliminating the rows before trying to convert them to INT.