title: Query optimisation does not execute inner query first
body: <p>When running this query:</p>
  
  <pre><code>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) &gt; 100
  
  drop table #test
  </code></pre>
  
  <p>I am expecting 1234567 to be returned, but get this instead:</p>
  
  <pre><code>Msg 245, Level 16, State 1, Line 7
  Conversion failed when converting the varchar value 'this is not a number' to data type int.
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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) &gt; 100
  
  drop table #subquery
  drop table #test
  </code></pre>
  
  <p>Is this a bug in the SQL query optimiser?  Can anyone explain this to me?</p>
  
  <p>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.</p>