non-correlated subquery failing to fail

  • In the following NON-CORRELATED subquery

    DELETE

    FROM dbo.DimCustomer_LLF

    WHERE CustomerKey in (SELECT DISTINCT(CustomerKey) FROM dbo.cdc_states);

    GO

    we should get a syntax error. CustomerKey is not a column in dbo.cdc_states. If we apply the rules for correlated subqueries and qualify everything then the systax error occurs as expected. The desired result was to delete a subset of the rows in dbo.DimCustomer_LLF. What happens instead is that all rows are deleted.

    What are we missing? Is this documented somewhere (we haven't found it in any searches)?

    This seems like a serious error in the DBMS.

  • Larry.Findley (6/24/2013)


    In the following NON-CORRELATED subquery

    DELETE

    FROM dbo.DimCustomer_LLF

    WHERE CustomerKey in (SELECT DISTINCT(CustomerKey) FROM dbo.cdc_states);

    GO

    we should get a syntax error. CustomerKey is not a column in dbo.cdc_states. If we apply the rules for correlated subqueries and qualify everything then the systax error occurs as expected. The desired result was to delete a subset of the rows in dbo.DimCustomer_LLF. What happens instead is that all rows are deleted.

    What are we missing? Is this documented somewhere (we haven't found it in any searches)?

    This seems like a serious error in the DBMS.

    This is not an error with SSMS. Since CustomerKey is in the DimCustomer table it is within scope of the subquery. The query is a bit odd but syntactically there is nothing wrong with it. Your subquery is now selecting all distinct CustomerKey values from cdc_states.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • the column 'CustomerKey' in the subquerys SELECT is being referenced from the outer query DimCustomer_LLF.

  • Thanks. I guess I just needed a little more patience. I finally found the following:

    If a column is referenced in a subquery that does not exist in the table referenced by the subquery's FROM clause, but exists in a table referenced by the outer query's FROM clause, the query executes without error. SQL Server implicitly qualifies the column in the subquery with the table name in the outer query.

    from:

    http://msdn.microsoft.com/en-us/library/ms178050(v=SQL.90).aspx

  • And this is why you should always, always, always qualify column names with the table name when you have subqueries around.

    This will fail.

    DELETE

    FROM dbo.DimCustomer_LLF AS DCust

    WHERE DCust.CustomerKey in (SELECT DISTINCT(CS.CustomerKey) FROM dbo.cdc_states AS CS);

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Quite true. It's just that this seemed so counter intuitive.

    While I have only been working with MSS for just over a year, I have about 20 years working with RDBS (DB2 for mainframe) and SQL, and had never encountered this situation. I just had to see what DB2 would do if I did something like this there: (Yes, I have been spoiled working with what seems a much more mature platform. And I used SELECT instead of DELETE, not wishing to destroy any data.) It returned all the rows from PSFT88.PSXLATITEM, AND returned a warning.

    ---------+---------+---------+---------+---------+---------+---------+---------+

    SELECT *

    FROM PSFT88.PSXLATITEM

    WHERE EFF_STATUS IN

    ( SELECT DISTINCT(EFF_STATUS) FROM PSFT88.PSXFERITEM );

    ---------+---------+---------+---------+---------+---------+---------+---------+

    DSNT404I SQLCODE = 12, WARNING: THE UNQUALIFIED COLUMN NAME EFF_STATUS WAS

    INTERPRETED AS A CORRELATED REFERENCE

    DSNT418I SQLSTATE = 01545 SQLSTATE RETURN CODE

    DSNT415I SQLERRP = DSNXORSO SQL PROCEDURE DETECTING ERROR

    DSNT416I SQLERRD = 0 0 22352 1142404404 0 0 SQL DIAGNOSTIC INFORMATION

    DSNT416I SQLERRD = X'00000000' X'00000000' X'00005750' X'4417B534'

    X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

  • Larry.Findley (6/24/2013)


    It's just that this seemed so counter intuitive.

    Heh... yeah. For me too... I banged my head on the counter a couple of times when I first ran across this type of thing in someone's code. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

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