SELECT processed before WHERE clause
-
I have a SELECT CAST(Column to int) that has been working OK, on the assumption that the WHERE has already filtered out any rows that would cause the CAST to error. Then I changed the filtering (still excluding any problem rows) - and it failed. I finally realised that the execution plan must have changed, and that the SELECT was now processing all the rows before the WHERE did the filtering. Fixed by changing it to TRY_CAST.
Somewhat embarrassed that I didn't know that this could happen - hence took too long to track down the problem - and that it is documented, the yellow warning box in below.
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-transact-sql?view=sql-server-ver15
But still finding it difficult, as always gone by the 'typical' logical processing order. Also, the WHERE cannot use column aliases from the SELECT, which does imply some order? And if we had a GROUP BY, wouldn't that force the WHERE to be done before the SELECT?
-
No. The select did not process all the rows.
The order of processing is as follows:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT
I think you may be thinking that the select is processing first, but it's likely from your where clause.
Can you publish your code?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ -
Can't post code at moment. The change I made to the WHERE clause returns a subset of the rows that were successfully returned previously. But errors on casting a value in a row that should have already been filtered out.
-
The order of processing is as follows:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT
LIMIT is MySQL not SQL Server. The last three things to be processed are : SELECT then TOP / OFFSET then ORDER BY
Also, the WHERE cannot use column aliases from the SELECT, which does imply some order? And if we had a GROUP BY, wouldn't that force the WHERE to be done before the SELECT?
The only thing that reference a column alias defined by SELECT is the ORDER BY clause. WHERE, GROUP BY and everything else are processed before SELECT which is why they are unaware of SELECT aliases.This is a good pic that details Logical Query Processing. The Itzik Ben-Gan book that it comes from is a great read and covers this topic in great detail.
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
- Alan Burstein wrote:
The last three things to be processed are : SELECT then TOP / OFFSET then ORDER BY
That's not what the SQL documentation says and that doesn't make sense either. TOP/OFFSET must be the last thing processed or you wouldn't be able to consistently return the same set of rows when using TOP/OFFSET.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
The following is from ITPro Logical Query Processing: What It Is And What It Means to You
Figure 3: Logical query processing order of query clauses
1 FROM
2 WHERE
3 GROUP BY
4 HAVING
5 SELECT
5.1 SELECT list
5.2 DISTINCT
6 ORDER BY
7 TOP / OFFSET-FETCHDrew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
The original poster of the question did link to Microsoft's documentation which as the OP said, does highlight that there are exceptions to this logical order of processing a query, in that a conversion may happen before WHERE clause filtering.
-
Yes. It's not about the logical order of processing but an exception to it. Specifically, that a conversion in the SELECT can be executed before the WHERE. I came across it the other day - for the first time - and it is documented.
My question is: despite a rare occurrence, should it be allowed?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply