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


View Column Resolving Issue in Sub-Query


View Column Resolving Issue in Sub-Query

Author
Message
jimmy-481471
jimmy-481471
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: 16
Hi,

Hugo is very much right. I would like you to go through the correlated subqueries in case if you want any further details on this.

cheers,

Jimz.
peterhe
peterhe
Mr or Mrs. 500
Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)

Group: General Forum Members
Points: 578 Visits: 449
I am on vacation till Jan.14. I have no reach to any SQL server and have very limited access to internet during my vacation. So I am sorry for the delay to the reply of the comments.

1) I don't think it's necessary to scope the column in the subquery. Actually you can only select the columns from the tables in the FROM subclause in the sub-query. It does not make sense to select columns from the tables refereneced by the main query. What result set are you expecting from a query like:

SELECT * FROM sys.server_principals b WHERE b.sid=(SELECT b.[sid] FROM sys.databases a WHERE a.name=DB_NAME())

2) Since I cannot reach SQL Server, can anyone run the following and let me what you get:

CREATE TABLE Ta (Ca1 int not null, Ca2 int not null)
GO
CREATE TABLE Tb (Cb1 int not null, Cb2 int not null)
GO
INSERT Ta (Ca1,Ca2) VALUES (1,1)
INSERT Ta (Ca1,Ca2) VALUES (2,2)
INSERT Tb (Cb1,Cb2) VALUES (2,2)
INSERT Tb (Cb1,Cb2) VALUES (3,3)

If we make a mistake in the following query
SELECT * FROM Ta WHERE Ca1 IN (SELECT Cb1 FROM Tb WHERE Cb2=2)
and write it as:
SELECT * FROM Ta WHERE Ca1 IN (SELECT Ca1 FROM Tb WHERE Cb2=2)

What result you get? Does SQL Server report error?

3) As I said in the article, it's talking about a scenario when a developer makes a mistake and SQL Server does not report the problem. If SQL server impliments the sub-query as a join (as mentioned by Hugo), it's an even bigger issue. The sub-query returns a scalar value in the example. It has nothing to do with the main query.



Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)SSCrazy Eights (8.7K reputation)

Group: General Forum Members
Points: 8728 Visits: 11716
peterhe (1/4/2008)

1) I don't think it's necessary to scope the column in the subquery. Actually you can only select the columns from the tables in the FROM subclause in the sub-query.


That is not true. You can also reference columns from the outer query. That is called a correlated subquery, and many people use them on a daily basis.

It does not make sense to select columns from the tables refereneced by the main query. What result set are you expecting from a query like:

SELECT * FROM sys.server_principals b WHERE b.sid=(SELECT b.[sid] FROM sys.databases a WHERE a.name=DB_NAME())


Exactly one row in sys.databases will match the WHERE clause. For this row, b.[sid] will be taken from the outer query and used as the result of the subquey. This is of course always equal to b.sid, so the WHERE clause is True for each and every row in sys.server_principals. The result set I expect from this query would be every single row in sys.server_principals.

2) Since I cannot reach SQL Server, can anyone run the following and let me what you get:

CREATE TABLE Ta (Ca1 int not null, Ca2 int not null)
GO
CREATE TABLE Tb (Cb1 int not null, Cb2 int not null)
GO
INSERT Ta (Ca1,Ca2) VALUES (1,1)
INSERT Ta (Ca1,Ca2) VALUES (2,2)
INSERT Tb (Cb1,Cb2) VALUES (2,2)
INSERT Tb (Cb1,Cb2) VALUES (3,3)

If we make a mistake in the following query
SELECT * FROM Ta WHERE Ca1 IN (SELECT Cb1 FROM Tb WHERE Cb2=2)
and write it as:
SELECT * FROM Ta WHERE Ca1 IN (SELECT Ca1 FROM Tb WHERE Cb2=2)

What result you get? Does SQL Server report error?


The first query returns this (as expected):

Ca1         Ca2
----------- -----------
2 2



The second query returns this, as expected by me (but probably not by you):

Ca1         Ca2
----------- -----------
1 1
2 2



3) As I said in the article, it's talking about a scenario when a developer makes a mistake and SQL Server does not report the problem. If SQL server impliments the sub-query as a join (as mentioned by Hugo), it's an even bigger issue. The sub-query returns a scalar value in the example. It has nothing to do with the main query.


SQL Server doesn't necessarily implement the subquery as a join (though that is, in fact, exactly what the optimizer will do in many cases - but it's irrelevant here). It assumes that you wanted to write a correlated subquery, since you are referencing columns from the outer query in the subquery. I'd love there to be a provision that references to outer queries always require explicit prefixing of table name or table alias, but such a provision is unfortunately not in any of the SQL standards that I know of (SQL-92, SQL-1999, and SQL-2003), and is not implemented by Microsoft either.

In fact, I'd put it one step further and say that I'd prefer it to be required to prefix each column in every query that uses more than a single table. That would prevent lots of those pesky, hard to find bugs caused by typos.

Thanks for taking the time to respond. Enjoy the rest of your holiday, I'll still be here, ready to read your reply, when you get back.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
RichB
RichB
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1159 Visits: 1023
For what its worth, I remembered a thread about this on google a while back.

I think it was this one, if this link works!

Covers the main points of the argument in detail - rather than anyone spend too much time trying to figure it out.



peterhe
peterhe
Mr or Mrs. 500
Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)Mr or Mrs. 500 (578 reputation)

Group: General Forum Members
Points: 578 Visits: 449
Thanks Hugo for your clarification and thanks Richard for the link.

I do use correlated subqueries in a daily basis but in my mind, the outer table can only be referenced in the WHERE clause in the subquery, not in the SELECT list of the subquery.

1) I checked BOL about subquery, it has a Caution section in the end:
" 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."
So I can't blame MS. It does a good job to document what SQL Server does in this scenario.

2) I also checked Oracle (10g for Windows, express edition), its behaviour is the same as MS SQL Server.

So two major RDBMS are handling this in the same way. I am not sure this is defined by SQL-99/2003 spec or not.

My apologize to everyone if the article caused any confusion. However, frankly i am not fully convinced. The subquery that select a column from the outer table is cumbersome and confusing in terms of semantics. It always has more concise and clear way for this type of queries. To me, it brings in more troubles than a feature. And I guess that's why MS put a caution section in the BOL.

I haven't used and will not use this type of correlated subqueries. To avoid the typos or mistakes, I'll follow Hugo's suggestion to scope the referenced columns.



Matt Miller (#4)
Matt Miller (#4)
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: General Forum Members
Points: 8239 Visits: 18251

The subquery that select a column from the outer table is cumbersome and confusing in terms of semantics. It always has more concise and clear way for this type of queries.


As much as I'd like to agree with you, there are times when you might need to reference data from the outer query in the SELECT part of the inner query, like, for example in some bizarre calculation.

I'm not saying it's smart, or the right way to do it. But all the parser might be able to do (at best) MIGHT be some kind of "you realize this is a REALLY DUMB thing to run" kind of warning. There are way too many things you might be able to do that might look "dumb" or ludicrous to a parser which might STILL need to be run (because the rules themselves are ludicrous), or that might just happen to work given some very specific circumstance you the developer happen to know about.

For what it's worth, ANSI's standard mentions putting prefixes on ALL columns. Period. Nevermind if there are more than one table involved or not.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
shaikr
shaikr
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 26
This issue is not new to SQL Server 2005 or to views, this problem exists in SQL Server 2000 (not sure in previoius versions). This does not happen all the time, not sure why this happens. Try this,

Create Table table1 (c1 int, c2 int)
Create Table table2 (c3 int, c4 int)

Select *
from table1
where C1 in ( select C1 from table2)

Even though column C1 does not exists, you will not get error.

Yes, I do agree that we as professionals must ensure that sub-query works.
Wink
rrjegan17
rrjegan17
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 346
Peter,
What you have explained over there is wrong.

If you run this one, it will execute succesfully.

IF NOT EXISTS(SELECT 1 FROM sys.server_principals b
WHERE b.sid=(SELECT b.sid FROM sys.databases a WHERE a.name=DB_NAME()))
BEGIN
PRINT N'Invalid Database Owner'
END
ELSE
BEGIN
PRINT N'Valid Database Owner'
END

But if you run

IF NOT EXISTS(SELECT 1 FROM sys.server_principals b
WHERE b.sid=(SELECT a.sid FROM sys.databases a WHERE a.name=DB_NAME()))
BEGIN
PRINT N'Invalid Database Owner'
END
ELSE
BEGIN
PRINT N'Valid Database Owner'
END

It would not run and hence as Hugo said your concept was wrong.
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