question

gotqn avatar image
gotqn asked

Why the Logical Processing Order of the SELECT statement is not followed?

Having the following query: DECLARE @t TABLE (ID UNIQUEIDENTIFIER, BegDate SMALLDATETIME, EndDate SMALLDATETIME) INSERT INTO @t SELECT NEWID(), '19000101', '20151124' -- This is to show you that nothing is really selected SELECT 'You will never see it' FROM @t r1 INNER JOIN @t r2 ON r2.ID = r1.ID WHERE 1=1 AND r1.BegDate > r2.BegDate AND r1.EndDate <= r2.EndDate SELECT r1.ID, r2.BegDate, DATEADD(DAY, -1, r1.BegDate) AS EndDate FROM @t r1 INNER JOIN @t r2 ON r2.ID = r1.ID WHERE 1=1 AND r1.BegDate > r2.BegDate -- This guarantees that r1.BegDate is not minimal value AND r1.EndDate <= r2.EndDate You are getting > Msg 517, Level 16, State 2, Line 21 > Adding a value to a 'smalldatetime' > column caused an overflow. even though the query should not return any data. The questions is why the `SELECT` stuff is executed before the `FROM`, `JOIN` and `WHERE` stuff, as this is not the [Logical Processing Order of the SELECT statement][1]. If you change the `19000101` value to `19000102`, just to get the execution plan the SQL engine is building, you should see this: ![alt text][2] The `Compute Scalar` is actually the `DATEADD` function execution: ![alt text][3] So, the `DATEADD` function is executed and throws an error. [1]: https://msdn.microsoft.com/en-us/library/ms189499.aspx [2]: /storage/temp/3073-x1.png [3]: /storage/temp/3074-x2.png
sql-server-2008-r2sql-servertsqlsql-2008-r2
x1.png (26.4 KiB)
x2.png (28.3 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
Logical processing is not the same as physical execution. As the link states > Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list. If you Parse the query (Ctrl-F5) or get the Estimated Execution Plan (Ctrl-L), then you get no error.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Interestingly, I can make SQL execute in the same order as the logical processing by using `option (force order)`, which is normally used to force the order of joining tables, but ***in this case*** it works too.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.