Query optimisation does not execute inner query first

Question

When running this query:

create table #test (field varchar(100))

insert into #test (field) values ('this is not a number')
insert into #test (field) values ('1234567')

select  *
from    (   select  field
            from    #test
            where   ISNUMERIC(field) = 1
        ) as subquery
where   CAST(field as int) > 100

drop table #test

I am expecting 1234567 to be returned, but get this instead:

Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value 'this is not a number' to data type int.

It's like the inner query doesn't execute first and the query optimiser makes (incorrect) assumptions about what I am trying to do. I have had to work around this with a temp table:

create table #test (field varchar(100))

insert into #test (field) values ('this is not a number')
insert into #test (field) values ('1234567')

select  field
into    #subquery
from    #test
where   ISNUMERIC(field) = 1

select  *
from    #subquery
where   CAST(field as int) > 100

drop table #subquery
drop table #test

Is this a bug in the SQL query optimiser? Can anyone explain this to me?

EDIT: In response to this question being a duplicate ... I am not using an 'IN' statement. The logic as I have written it implies that I require a filtered subquery returned BEFORE I apply the CAST condition. Any sane person would expect my query to behave in that manner. The optimiser's version of the query clearly is not logically identical.

Answer 1

SQL Server optimiser take the commands (t-sql) as input, looks at the available resources (indexes, statistics etc.) and comes up with the best possible plan, it think for the query. This includes what query/parts of query gets executed first, which predicates gets pushed deeper into the query etc etc.

Thus if you look at the execution plan for this query, the predicates were pushed into the sub-query hence the cast to int failed.

There is only single operator in the execution plan, i.e. table scan and both the predicates were passed to the inner query.

If you think about it, it makes sense, why return more rows from the sub-query than needed, only to be filtered out by the where clause from the outer query. I would not consider it a bug but a clever way of optimising execution plans :)