is the sql server being smart or stupid?
-
a colleague came to me with a problem earlier complaining that his query was NOT erroring when he thought it should, instead it was returning lots of rows he didnt expect (or want for that matter)
try the three following statements
select
colid from sysobjects
go
select
* from syscolumns where colid in (select colid from sysobjects)
go
select
* from syscolumns where colid in (select wibble from sysobjects)
go
i would expect them all to error because there is no colid or wibble in sysobjects, but it seems that sql server decides that because the column doesnt exist in the subquery that it should compare the text against the where clause instead. if the text is the same (second query) it will return all of the rows from the main select but if the text is different (third query) it errors with the column doesnt exist.
it's no problem but i am just curious if anyone can think of why it would work like that (in 2000 and in 2005)
-
That's really strange... I didn't know about this behaviour.
I tested this on a normal (user) table, and results are the same, so it isn't some idiosyncrasy of system tables.
If you look at the execution plan, you will see that the WHERE clause in query no. 2 is interpreted as "WHERE syscolumns.colid = syscolumns.colid".
I still don't know why this happens, and consider it as bug in SQLS, but now it is clear that you can avoid this problem if you stick to the recommended way of writing selects : always use table.column (or alias.column) to identify the column. Following query works as expected (i.e. fails with Invalid column name 'colid'.):
select * from syscolumns where colid in (select sysobjects.colid from sysobjects)
-
Maybe you just found the frontier between a correlated subquery and a subquery. Seems to make some sens but I agree that it should somehow error out if there's nothing "joining" the table together.
-
That's strange... I'm thinking we have a service pack problem here... I'll bet you guys are running SP4 on SQL Server 2000... I'm running SP3A... and here's what I get when I run Paul's queries...
Server: Msg 207, Level 16, State 3, Line 1 --From the first select
Invalid column name 'colid'.
(1578 row(s) affected)
Server: Msg 207, Level 16, State 3, Line 2 --From the thirde select
Invalid column name 'wibble'.
Has anyone checked the Microsoft site for a "known issue" or a "hot fix"?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
hadnt noticed that in the execution plan. so it must be a bug then rather than it trying to assume something.
colleague is going to use joins now instead of being lazy and always using subqueries
-
actually i've tried it on sql2000 sp4, sql2005 without sp and with sp 1 and it happens on all of them
that is why i wasnt sure whether it was something that was happening for an intentional reason, but for the life of me i couldnt work out what that reason was.
-
http://support.microsoft.com/kb/q298674/
will give you your answer
Edit:
p.s. I always use fully-qualified column names in my subqueries
Far away is close at hand in the images of elsewhere.
Anon. -
Crud... I didn't read the second query... sorry... not enough coffee...
To play on what David suggested... it's one of the reasons why I'm glad that the standards I wrote for work forbid the use of IN/SELECT instead of joining to a derived table.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Why forbid instead of educate?
Anyone into the habit of using fully qualified names (aka aliasing) their queries won't be caught unaware by this, which for those who have not read the link, is the expected 'by design and standards' behavior.
Admittedly, it's a tricky trap and easy to fall into, but as with most things, when things are done 'proper', many pitfalls also goes away.
/Kenneth
-
Thanks for the link, David. It is now clear that it isn't bug, but normal behaviour. I didn't think about it this way before - I supposed that the (select ...) part is independent on the rest, i.e. not correlated subquery, unless there is a corresponding statement in WHERE clause of the subquery. Wasn't aware that even this can be enough to make it correlated.
I'm happy that I always use qualified names... otherwise I'd have to go over all views and procedures and correct the code
(actually I use derived tables now, but I know there are some older pieces of code which still contain IN(select..)) -
-
Of course there's some education in it, Ken... but, it's still forbidden because it hurts performance and a derived table should be used instead. And, it will also catch these types of errors, as you know.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply