Select with non-existent column runs without error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Select with non-existent column runs without error

Author

Topic

Sean Frost
Starting Member

19 Posts

Posted - 2011-01-03 : 13:17:36

I got into trouble for sending the wrong results to a client. It turned out that a query behaved in a manner I would not have expected. The sample query below illustrates the problem. Even though id2 is not a column in the #tmpA table, the query runs without any errors, but giving incorrect results.

Can you help me understand why this happens, and if there is something that could be done to prevent/avoid this? I would much rather prefer getting an error message like I get if I were to run the inner query by iteself.

create table #tmpA (id int);
insert into #tmpA values (1),(2),(3);

create table #tmpB (id2 int);
insert into #tmpB values (1),(2),(3),(4);

select * from #tmpB where id2 in (select id2 from #tmpA);

drop table #tmpA;
drop table #tmpB;

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-03 : 13:36:14

Strange. The boolean operation appears to default to "true" for every row, despite the error in the subquery. Would be interested to see why this behavior occurs (and why there is no error thrown at the subquery during execution). The same happens regardless of # temp tables or not.

A better solution would be an inner join on the matching columns.

select #tmpB.*
from #tmpB inner join #tmpA on #tmpB.ID2 = #tmpA.ID

Poor planning on your part does not constitute an emergency on my part.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-01-03 : 13:57:32

id2 is a valid column (think correlated sub-query). So, that is perfectly valid syntax. However, if you properly qualify your columns, then you will get an error:

select * from #tmpB AS B where B.id2 in (select A.id2 from #tmpA AS A);

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-03 : 18:26:20

SQL behaves like any block structured language. It first tries to resolve column names in the query or sub-query. If it fails, it then looks at the closest containing query and so forth from the inside out.

This is why it is a good idea to qualify all of the column names.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Sean Frost
Starting Member

19 Posts

Posted - 2011-01-03 : 22:33:27

Of course! I see it now!!

Now, if only I can explain to my client the nuances of correlated queries and name resolution logic in SQL....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources