Results 1 to 5 of 5

Thread: I did a bad thing but want to learn how it worked

  1. #1

    Thread Starter
    Frenzied Member cory_jackson's Avatar
    Join Date
    Dec 2011
    Location
    Fallbrook, California
    Posts
    1,083

    I did a bad thing but want to learn how it worked

    Code:
    Delete from Accounts where ID in (Select ID from Temp)
    I messed up on a process containing this step but the outcome is weird and I'd like to understand what happened.
    I had changed the columns in the Temp table so it didn't have the ID column. If I run just the "Select ID from Temp" it fails to execute. But if I execute the delete query with the Temp ID select query in it, not only does it run with no errors, but it deletes all the contents of accounts.
    1. Why doesn't the invalid subquery generate an error and stop the execution.
    2. If for some reason the subquery ran and returned values, it should be an empty set. How does it select all the rows in Accounts from nothing?

    And in general, I just wanted to understand the mechanism. I looked at the execution plan but I don't understand it.
    Thanks much for taking the time to read my post.

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,580

    Re: I did a bad thing but want to learn how it worked

    Possible answer: https://learn.microsoft.com/en-us/an...on-delete.html

    Bottom Line:
    "ID" doesn't exist in Temp (your SubQuery). SQL (in this Case MS-SQL-Server) looks in your outer Query if the Column exists. If Yes, SQL-Server "qualifies" your Column with the outer table.
    In your case it would transform the SQL into (Aircode)
    Code:
    DELETE From Accounts WHERE ID In (SELECT Accounts.ID From temp)
    Note: At least for MSSQL this is by design!
    https://learn.microsoft.com/en-us/sq...r15#qualifying

    Bottom Line for you: Qualify your Columns to get an error
    Code:
    Delete from Accounts where Accounts.ID in (Select Temp.ID from Temp)
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    18,190

    Re: I did a bad thing but want to learn how it worked

    One of my silly old 10 commandments - ALWAYS ALIAS a table and fully qualify your field names. Lofty goal - and certainly ignored in places like that sub-query, where it's failure to run in the full context of the DELETE query causes such ugly problems.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  4. #4
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    3,580

    Re: I did a bad thing but want to learn how it worked

    Quote Originally Posted by szlamany View Post
    One of my silly old 10 commandments - ALWAYS ALIAS a table and fully qualify your field names. Lofty goal - and certainly ignored in places like that sub-query, where it's failure to run in the full context of the DELETE query causes such ugly problems.
    Agreed with the Aliasing.

    Neverthless, i was surprised by this "bug", too (Yeah: it's not a bug, it's by Design), since i would expect any DBMS to complain.
    though: No idea if this "bug" applies to other DBMS. It might be, that I've run into this on our DB2 on iSeries, but not sure about it. Might have been just a typo
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  5. #5
    PowerPoster
    Join Date
    Jun 2013
    Posts
    6,359

    Re: I did a bad thing but want to learn how it worked

    Quote Originally Posted by Zvoni View Post
    ...i would expect any DBMS to complain...
    That'd be the normal behavior (strictly conforming to SQL-language rules) -
    which indicate by the parentheses, that another sub-context (a Set-Operation on a Sub-Table) was entered.

    The behaviour as it is (apparently only in TSQL) -
    can be compared to a programming-language,
    which suddenly makes all Property-Names of an "outer Object" visible as SymbolNames in a SubRoutine.

    Guess it's historical in a way (probably existed already in SyBase),
    and if they "fix it", the planet would explode (or all stock-exchange will stop working)...

    Olaf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width