TOP used before the predicate – feels like a Sql bug but certainly i am missing something
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?
SELECT TOP (1) 'g' objecttype, v.vehicleid, v.runid, v.rundate, v.id, startdatetime, 1--matches the CROSS JOIN for Pickup FROM dbo.tblRunactual v WHERE v.verified = 1 ORDER BY v.StartDateTime
…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.
As I say in the answer, the optimizer cannot change what the query means, it can only try to find an efficient physical implementation given the result specification.
Pushing the filter down into the scope shown would change the meaning of the query. Finding the first row in StartDateTime order where verified = 1 is just not the same thing as finding the first row with the extra filter condition vehicleid = 409 AND rundate = 20140219.
Without the TOP, the optimizer has much more freedom because placement of the filter does not affect the semantics, only the performance. This reasoning is quite general (it has to be!) and certainly not limited to TOP.
BTW did you mean to change UNION ALL to UNION in the rewrite?
UNION ALL to UNION
Yes. A subsequent CTE had a DISTINCT that i removed because i removed the ALL.
And yes, I did recall the code from the other question.