sql server rewrites my query incorrectly?

Question

There is a dirty data in input. We are trying to cleanup dataset and then make some calculations on cleared data.

declare @t table (str varchar(10))
insert into @t select '12345' union all select 'ABCDE' union all select '111aa'

;with prep as
(
select *, cast(substring(str, 1, 3) as int) as str_int
from @t
where isnumeric(substring(str, 1, 3)) = 1
)

select * 
from prep
where 1=1
and case when str_int > 0 then 'Y' else 'N' end = 'Y'
--and str_int > 0

Last 2 lines are doing the same thing. First one works, but if you uncomment second one it will crash with Conversion failed when converting the varchar value 'ABC' to data type int.

Obviously, SQL Server is rewriting query mixing all the conditions together. My guess it that it considers 'case' as a havy operation and performs it as a last step. That's why workaround with case works.

Is this behavior documented in any way? or is it a bug?

Answer 1

This is a known issue with SQL Server, and Microsoft does not consider it a bug although users do. The difference between the two queries is the execution path. One is doing the conversion before the filtering, the other after.

SQL Server reserves the right to re-order the processing. The documentation does specify the logical processing of clauses as:

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. WITH CUBE or WITH ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP

With (presumably but not explicitly documented here) CTEs being logically processed first. What does logically processed mean? Well, it doesn't mean that run-time errors are caught. It really determines the scope of identifiers during the compile phase.

When SQL Server reads from a data source, it can add new variables in. This is a convenient time to do this, because everything is in memory. However, this might occur before the filtering, which is what is causing the error when it occurs.

The fix to this problem is to use a case statement. So, the following CTE will usually work:

with prep as (
      select *, (case when isnumeric(substring(str, 1, 3)) = 1 and str not like '%.%'
                      then cast(substring(str, 1, 3) as int)
                 end) as str_int
      from @t
      where isnumeric(substring(str, 1, 3)) = 1
     )

Looks weird. And I think Redmond thinks so too. SQL Server 2012 introduced try_convert() (see here) which returns NULL if the conversion fails.

It would also help if you could instruct SQL Server to materialize CTEs. That would also solve the problem in this case. You can vote on adding such an option to SQL Server here.