SELECT processed before WHERE clause

  • kuopaz

    Ten Centuries

    Points: 1196

    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?

     

  • Michael L John

    One Orange Chip

    Points: 25792

    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/

  • kuopaz

    Ten Centuries

    Points: 1196

    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.

  • Alan Burstein

    SSC Guru

    Points: 61049

    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

  • drew.allen

    SSC Guru

    Points: 76632

    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

  • drew.allen

    SSC Guru

    Points: 76632

    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-FETCH

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Chris Harshman

    SSC-Forever

    Points: 41810

    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.

  • kuopaz

    Ten Centuries

    Points: 1196

    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 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply