Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


non-correlated subquery failing to fail


non-correlated subquery failing to fail

Author
Message
Larry.Findley
Larry.Findley
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 163
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.
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16542 Visits: 16997
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
davidandrews13
davidandrews13
SSC Eights!
SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)SSC Eights! (824 reputation)

Group: General Forum Members
Points: 824 Visits: 4447
the column 'CustomerKey' in the subquerys SELECT is being referenced from the outer query DimCustomer_LLF.
Larry.Findley
Larry.Findley
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 163
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47189 Visits: 44356
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


Larry.Findley
Larry.Findley
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 163
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44994 Visits: 39880
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. :-D

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search