Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

non-correlated subquery failing to fail Expand / Collapse
Author
Message
Posted Monday, June 24, 2013 10:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 7:45 AM
Points: 4, Visits: 105
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.
Post #1466807
Posted Monday, June 24, 2013 10:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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)
Post #1466827
Posted Monday, June 24, 2013 10:23 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 605, Visits: 3,539
the column 'CustomerKey' in the subquerys SELECT is being referenced from the outer query DimCustomer_LLF.
Post #1466829
Posted Monday, June 24, 2013 11:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 7:45 AM
Points: 4, Visits: 105
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

Post #1466849
Posted Monday, June 24, 2013 1:37 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 40,428, Visits: 36,879
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 2008, MVP
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

Post #1466884
Posted Monday, June 24, 2013 2:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 7:45 AM
Points: 4, Visits: 105
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
Post #1466895
Posted Monday, June 24, 2013 4:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:02 PM
Points: 35,593, Visits: 32,182
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1466945
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse