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 Tuesday, January 1, 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: Tuesday, August 12, 2014 1:04 PM
Points: 531, Visits: 431
Comments posted to this topic are about the item View Column Resolving Issue in Sub-Query


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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:36 PM
Points: 6,002, Visits: 8,267
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
Posted Wednesday, January 2, 2008 4:43 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:01 AM
Points: 1,070, Visits: 906
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
Posted Wednesday, January 2, 2008 6:36 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Monday, May 12, 2014 8:54 AM
Points: 195, Visits: 80
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
Posted Wednesday, January 2, 2008 9:36 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:20 AM
Points: 1,616, Visits: 1,544
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
My book: Pro SQL Server 2008 Mirroring
Microsoft Certified Master: SQL Server 2008
Principal DBA: Outerwall, Inc.
Also available for consulting: SQL DBA Master
Post #438036
Posted Wednesday, January 2, 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 @ 3:52 PM
Points: 33,268, Visits: 15,440
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
Posted Wednesday, January 2, 2008 2:57 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 3, 2013 1:10 PM
Points: 71, Visits: 43
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
Posted Wednesday, January 2, 2008 4:32 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 30, 2014 10:08 PM
Points: 1,038, Visits: 444
The article should really be removed or altered - it makes the site look bad


Post #438186
Posted Wednesday, January 2, 2008 5:05 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, Visits: 824
The best practice is to always prefix column names, which prevents such errors.

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

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, May 24, 2014 7:58 PM
Points: 219, Visits: 824
Also Steve: I think Hugo is absolutely correct.

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

Add to briefcase 12»»

Permissions Expand / Collapse