title: Window function behaves differently in Subquery/CTE?
body: <p>I thought the following three SQL statements are semantically the same. The database engine will expand the second and third query to the first one internally. </p>
  
  <pre><code>select .... 
  from T 
  where Id = 1
  
  select * 
  from 
      (select .... from T) t 
  where Id = 1
  
  select * 
  from 
      (select .... from T where Id = 1) t
  </code></pre>
  
  <p>However, I found the window function behaves differently. I have the following code.</p>
  
  <pre><code>-- Prepare test data
  with t1 as 
  ( 
      select *
      from (values ( 2, null), ( 3, 10), ( 5, -1), ( 7, null), ( 11, null), ( 13, -12), ( 17, null), ( 19, null), ( 23, 1759) ) v ( id, col1 )
  )
  select * 
  into #t 
  from t1
  
  alter table #t add primary key (id)
  go
  </code></pre>
  
  <p>The following query returns all the rows.</p>
  
  <pre><code>select  
       id, col1,
       cast(substring(max(cast(id as binary(4)) + cast(col1 as binary(4))) 
                         over (order by id
                               rows between unbounded preceding and 1 preceding), 5, 4) as int) as lastval
  from    
      #t
  
  id  col1    lastval
  -------------------
  2   NULL    NULL
  3   10      NULL
  5   -1      10
  7   NULL    -1
  11  NULL    -1
  13  -12     -1
  17  NULL    -12
  19  NULL    -12
  23  1759    -12
  </code></pre>
  
  <p>Without CTE/subquery: then I added a condition just return the row which <code>Id = 19</code>.</p>
  
  <pre><code>select  
      id, col1,
      cast(substring(max(cast(id as binary(4)) + cast(col1 as binary(4))) over (order by id rows between unbounded preceding and 1 preceding), 5, 4) as int) as lastval
  from    
      #t
  where
      id = 19;
  </code></pre>
  
  <p>However, <code>lastval</code> returns <code>null</code>?</p>
  
  <p>With CTE/subquery: now the condition is applied to the CTE:</p>
  
  <pre><code>with t as 
  ( 
      select   
          id, col1,
          cast(substring(max(cast(id as binary(4)) + cast(col1 as binary(4))) over (order by id rows between unbounded preceding and 1 preceding ), 5, 4) as int) as lastval
      from     
          #t)
  select *
  from t
  where id = 19;
  
  -- Subquery
  select  
      *
  from
      (select   
           id, col1,
           cast(substring(max(cast(id as binary(4)) + cast(col1 as binary(4))) over (order by id rows between unbounded preceding and 1 preceding), 5, 4) as int) as lastval
       from     
           #t) t
  where   
      id = 19;
  </code></pre>
  
  <p>Now <code>lastval</code> returns <code>-12</code> as expected?</p>