The SQL is pretty simple, i have a CTE with 3 unions. Two of the three have a TOP (1). The outside statement has the predicate. The query plan shows that the first statement in the CTE uses the predicate in the seeks. The second two process the TOP before the filter which reduces the row count to zero - which is wrong.
I checked BOL and it doesn't mention the processing order but everything i knew about logical processing says the predicates/joins are first then the projection and finally the TOP and order bys. Based on the link below and an image by Jet Wang in the thread. This seems to back up my thoughts. But certainly i am missing something. This seems too rudimentary for it to be a sql bug. thoughts?
By Dan Holmes 725 asked May 14, 2014 at 09:03 PM
You just have to remember that logical processing order also has scope. Each of your
...is its own scope. The optimizer can move things around within a query (even across scopes) but it must retain the original semantics.
The semantics of your query is that the scope above is logically evaluated before the outer filter is applied. It may not be what you intended, but nevertheless it is correct in SQL.
By SQLkiwi ♦ 6.6k answered May 14, 2014 at 09:21 PM