non-correlated subquery failing to fail

Question

non-correlated subquery failing to fail

Larry.Findley

SSC Enthusiast

Points: 108
More actions
June 24, 2013 at 10:02 am

#274477

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.

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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/

davidandrews13 SSCertifiable Points: 6820 More actions · Answer 2

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

Larry.Findley SSC Enthusiast Points: 108 More actions · Answer 3

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

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 SSC Enthusiast Points: 108 More actions · Answer 5

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 SSC Guru Points: 1004406 More actions · Answer 6

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)