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 ««12

View Column Resolving Issue in Sub-Query Expand / Collapse
Author
Message
Posted Thursday, January 3, 2008 9:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 27, 2014 2:07 AM
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.
Post #438481
Posted Friday, January 4, 2008 2:40 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:18 AM
Points: 531, Visits: 433
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.






Post #438814
Posted Friday, January 4, 2008 7:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:54 PM
Points: 6,130, Visits: 8,394
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
Post #438940
Posted Monday, January 7, 2008 7:24 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:05 AM
Points: 1,070, Visits: 913
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.



Post #439565
Posted Monday, January 14, 2008 9:56 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:18 AM
Points: 531, Visits: 433
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.



Post #442535
Posted Monday, January 14, 2008 12:41 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:56 PM
Points: 7,179, Visits: 15,774

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?
Post #442632
Posted Tuesday, September 23, 2008 4:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 9, 2012 9:44 AM
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.
;)
Post #574821
Posted Monday, February 2, 2009 11:32 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:27 AM
Points: 212, Visits: 307
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.
Post #648587
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse