SQLServerCentral / Discuss Content Posted by Peter He / Article Discussions / Article Discussions by Author / View Column Resolving Issue in Sub-Query / Latest Posts

RE: View Column Resolving Issue in Sub-Query

rrjegan17 — Mon, 02 Feb 2009 23:32:45 GMT

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' ENDBut if you runIF 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' ENDIt would not run and hence as Hugo said your concept was wrong.

RE: View Column Resolving Issue in Sub-Query

shaikr — Tue, 23 Sep 2008 16:19:34 GMT

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.;)

RE: View Column Resolving Issue in Sub-Query

Matt Miller (#4) — Mon, 14 Jan 2008 12:41:47 GMT

[quote]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.[/quote]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.

RE: View Column Resolving Issue in Sub-Query

peterhe — Mon, 14 Jan 2008 09:56:49 GMT

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.

RE: View Column Resolving Issue in Sub-Query

RichB — Mon, 07 Jan 2008 07:24:54 GMT

For what its worth, I remembered a thread about this on google a while back.[url=http://groups.google.co.uk/group/microsoft.public.sqlserver.programming/browse_thread/thread/f59c76bd7e066ec7/11b8b570c4b5f417?#11b8b570c4b5f417]I think it was this one, if this link works![/url]Covers the main points of the argument in detail - rather than anyone spend too much time trying to figure it out.

RE: View Column Resolving Issue in Sub-Query

Hugo Kornelis — Fri, 04 Jan 2008 07:22:55 GMT

[quote][b]peterhe (1/4/2008)[/b][hr]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.[/quote]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.[quote]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())[/quote]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.[quote]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)GOCREATE TABLE Tb (Cb1 int not null, Cb2 int not null)GOINSERT 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 querySELECT * 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? [/quote]The first query returns this (as expected):[code]Ca1 Ca2----------- -----------2 2[/code]The second query returns this, as expected by me (but probably not by you):[code]Ca1 Ca2----------- -----------1 12 2[/code][quote]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.[/quote]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.

RE: View Column Resolving Issue in Sub-Query

peterhe — Fri, 04 Jan 2008 02:40:46 GMT

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)GOCREATE TABLE Tb (Cb1 int not null, Cb2 int not null)GOINSERT 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 querySELECT * 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.

RE: View Column Resolving Issue in Sub-Query

jimmy-481471 — Thu, 03 Jan 2008 09:58:33 GMT

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.

RE: View Column Resolving Issue in Sub-Query

Alexander Kuznetsov — Wed, 02 Jan 2008 18:41:51 GMT

Also Steve: I think Hugo is absolutely correct.Cheers,Alex Kuznetsov,SQL Server MVP

RE: View Column Resolving Issue in Sub-Query

Alexander Kuznetsov — Wed, 02 Jan 2008 17:05:52 GMT

The best practice is to always prefix column names, which prevents such errors.Cheers,Alex Kuznetsov,SQL Server MVP

RE: View Column Resolving Issue in Sub-Query

Ian Yates — Wed, 02 Jan 2008 16:32:10 GMT

The article should really be removed or altered - it makes the site look bad :(

RE: View Column Resolving Issue in Sub-Query

Remaks — Wed, 02 Jan 2008 14:57:11 GMT

I wish I'd read the discussion before going down the same road Hugo did to prove what I believed I saw up front. I've made the mistake too many times of including an unqualified, invalid inner query field that was a valid outer query field and getting unexpected results, but not an error. I felt that before I responded I should verify that what I thought I saw was in fact the error I believed it to be. That was a waste of 5 minutes. (So I figured I'd waste a few more actually posting this response) :)

RE: View Column Resolving Issue in Sub-Query

Steve Jones - SSC Editor — Wed, 02 Jan 2008 11:19:18 GMT

Hopefully Peter will respond soon to this and thanks for a nice post on what you see, Hugo.We don't necessarily check every article for accuracy. It's a time constraint as well as the fact that I don't necessarily have the expertise to check every section of every article,QOD, etc. It's a good learning experience and people sometimes find things that they feel are correct or it's what they have observed. We want to give them their voice and let them show the world from their perspective.We have had lots of debates and often I wouldn't necessarily say that any particular side is correct. It's often a question of where something applies or how it might fit your situation. I do think that Hugo seems to be correct in this case, but I haven't dug into it today to see.

RE: View Column Resolving Issue in Sub-Query

Robert Davis — Wed, 02 Jan 2008 09:36:10 GMT

I have to agree with the others. I think blaming SQL Server because someone doesn't code something correctly is just plain illogical. Using that logic, every mistake I ever made (and there have certainly been mistakes) could be called a SQL Server bug.

RE: View Column Resolving Issue in Sub-Query

Jambu Krishnamurthy — Wed, 02 Jan 2008 06:36:12 GMT

Thank you very much SSCCommitted for that correction.It was too scary, and I was almost about to talk to my DBA friendsin different companies. That would have not been a good start withmy friends on a New Year.Before talking to my friends, I just wanted to ask in the forum aboutwhat was the behavior in Oracle. And Luckily I had you reply readyto quell all dounts.thankzagain and Have a New Year

RE: View Column Resolving Issue in Sub-Query

RichB — Wed, 02 Jan 2008 04:43:49 GMT

Thanks Hugo for a very concise and informative post, far better than that which I was going to attempt! It would be nice if the initial article could be updated with at least a caveat.Happy new year btw...

RE: View Column Resolving Issue in Sub-Query

Hugo Kornelis — Wed, 02 Jan 2008 02:19:31 GMT

Hi Peter,It's a nice theory, but unfortunately (or maybe I should say: fortunately), completely incorrect.[code]IF NOT EXISTS(SELECT 1 FROM sys.server_principals b WHERE b.sid=(SELECT [sid] FROM sys.databases a WHERE a.name=DB_NAME()))[/code]The reason that this works has nothing to do with the fact that sys.databases is a view, or that sid is a column in one of the base tables. It's related to how unqualified names in a subquery are resolved - SQL Server will first check for a column named sid in the inner query, and if it doesn't find one, it checks the outer query. This query is therefor equivalent to[code]IF NOT EXISTS(SELECT 1 FROM sys.server_principals b WHERE b.sid=(SELECT [color="#FF0000"][b]b.[/b][/color][sid] FROM sys.databases a WHERE a.name=DB_NAME()))[/code]It's a shame that you didn't check your theory more carefully before submitting it to SQLServerCentral.com, and it's also a shame that Steve didn't check your claims before publishing. One very easy wway to check it would have been to replace [sid] with the name of a different column that is also aliased in the sys.databases view, like this:[code]IF NOT EXISTS(SELECT 1 FROM sys.server_principals b WHERE b.sid=(SELECT [[color="#FF0000"][b]indepid[/b][/color]] FROM sys.databases a WHERE a.name=DB_NAME()))[color="#FF0000"]Msg 207, Level 16, State 1, Line 2Invalid column name 'indepid'.[/color][/code]If your theory would have held, SQL Server would've used the indepid column from sys.syssingleobjref, which is aliased as source_database_id in thhe sys.databases view, instead of generating an invalid column name error.

View Column Resolving Issue in Sub-Query

peterhe — Tue, 01 Jan 2008 23:02:17 GMT

Comments posted to this topic are about the item [B]View Column Resolving Issue in Sub-Query[/B]