Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Peter He » View Column Resolving Issue in Sub-Query
Add to briefcase
18 posts, Page 1 of 2
12»»

View Column Resolving Issue in Sub-Query Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
peterhe
Posted Tuesday, January 01, 2008 11:02 PM
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, February 20, 2013 10:51 AM
Points: 531, Visits: 416
Comments posted to this topic are about the item View Column Resolving Issue in Sub-Query


Post #437824
Hugo Kornelis
Posted Wednesday, January 02, 2008 2:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:21 AM
Points: 5,244, Visits: 7,057
Hi Peter,

It's a nice theory, but unfortunately (or maybe I should say: fortunately), completely incorrect.

IF NOT EXISTS(SELECT 1 FROM sys.server_principals b 
 WHERE b.sid=(SELECT [sid] FROM sys.databases a WHERE a.name=DB_NAME()))

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

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()))

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:

IF NOT EXISTS(SELECT 1 FROM sys.server_principals b 
 WHERE b.sid=(SELECT [indepid] FROM sys.databases a WHERE a.name=DB_NAME()))

Msg 207, Level 16, State 1, Line 2
Invalid column name 'indepid'.

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.

Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #437862
RichB
Posted Wednesday, January 02, 2008 4:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 3:06 AM
Points: 1,026, Visits: 751
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...


Post #437907
Jambu Krishnamurthy
Posted Wednesday, January 02, 2008 6:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 11:32 AM
Points: 195, Visits: 70
Thank you very much SSCCommitted for that correction.

It was too scary, and I was almost about to talk to my DBA friends
in different companies. That would have not been a good start with
my friends on a New Year.

Before talking to my friends, I just wanted to ask in the forum about
what was the behavior in Oracle. And Luckily I had you reply ready
to quell all dounts.

thankz
again and Have a New Year
Post #437944
Robert Davis
Posted Wednesday, January 02, 2008 9:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, May 19, 2013 8:09 AM
Points: 1,558, Visits: 1,396
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.


My blog: SQL Soldier
Twitter: @SQLSoldier
Microsoft Certified Master: SQL Server 2008
Sr. Product Consultant and Chief SQL Server Evangelist @ Idera
My book: Pro SQL Server 2008 Mirroring
Post #438036
Steve Jones - SSC Editor
Posted Wednesday, January 02, 2008 11:19 AM


SSC-Dedicated

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

Group: Administrators
Last Login: Today @ 1:14 AM
Points: 31,433, Visits: 13,746
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.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #438085
Remaks
Posted Wednesday, January 02, 2008 2:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, January 14, 2011 8:55 AM
Points: 71, Visits: 42
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) :)
Post #438165
Ian Yates
Posted Wednesday, January 02, 2008 4:32 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 16, 2012 3:26 AM
Points: 1,038, Visits: 443
The article should really be removed or altered - it makes the site look bad


Post #438186
Alexander Kuznetsov
Posted Wednesday, January 02, 2008 5:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:55 AM
Points: 219, Visits: 807
The best practice is to always prefix column names, which prevents such errors.

Cheers,
Alex Kuznetsov,
SQL Server MVP
Post #438189
Alexander Kuznetsov
Posted Wednesday, January 02, 2008 6:41 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:55 AM
Points: 219, Visits: 807
Also Steve: I think Hugo is absolutely correct.

Cheers,
Alex Kuznetsov,
SQL Server MVP
Post #438204
« Prev Topic | Next Topic »

Add to briefcase
18 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.