BUG? SQL 2k - IN () allows invalid syntax and fails silently

[rawheiser]

Behavior I found:

Invalid Column name in select embedded in a IN() clause appears to
return a NULL, and fails silently - completing the query giving
incorrect results.

Behavior I expected:

RunTime error, as column didn't exist.

-------------------------------------------------------

create
table #THEONE
( RowNo int not null )

create
table #THEOTHER
( RowNumb int not null )

select *
from #THEONE
where RowNo not in (
-- SHOULD THROW RUNTIME ERROR --
select RowNo from #THEOTHER
)

-------------------------------------------------------

-- select @@VERSION

-- Microsoft SQL Server 2000 - 8.00.871 (Intel X86)
-- Oct 21 2003 16:16:50
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

[nib]

Actually, if I recall, there is a nuance of SQL at work here that makes
this not be a true bug. Basically, because RowNo exists in your original
table, the query will work. I don't remember all the exact specifics,
but I know I've seen it discussed.

Zach

rawheiser wrote:

Behavior I found:

Invalid Column name in select embedded in a IN() clause appears to
return a NULL, and fails silently - completing the query giving
incorrect results.

Behavior I expected:

RunTime error, as column didn't exist.

-------------------------------------------------------

create
table #THEONE
( RowNo int not null )

create
table #THEOTHER
( RowNumb int not null )

select *
from #THEONE
where RowNo not in (
-- SHOULD THROW RUNTIME ERROR --
select RowNo from #THEOTHER
)

-------------------------------------------------------

-- select @@VERSION

-- Microsoft SQL Server 2000 - 8.00.871 (Intel X86)
-- Oct 21 2003 16:16:50
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

[Steve Kass]

First of all, this isn't a bug. Consider this example:

create table #THEONE (
RowNo int not null,
MaxOffset int not null
)

create table #THEOTHER (
RowNumb int not null,
RowOffset int not null
)

select * from #THEONE
where RowNo not in (
select RowNo + RowOffset
-- RowNo must be a valid column name to use at this point!
from #THEOTHER
where RowOffset < MaxOffset
)

You can refer to the columns of #THEONE anywhere in the SELECT and WHERE clauses of a query from that table. If not, you'd be unable to write correlated subqueries, at the least.

To avoid this programming error, always qualify column names. Had you written this, you'd have seen the problem:
select * from #THEONE
where #THEONE.RowNo not in (
select #THEOTHER.RowNo + #THEOTHER.RowOffset
from #THEOTHER
where #THEOTHER.RowOffset < #THEONE.MaxOffset
)

This is how most programming languages work. Identifiers are visible from inside their scope, unless shadowed by a redeclaration of the same identifier in a sub-something.
Secondly, you should not be getting a NULL anywhere. The inner query of your example is select RowNo from #THEOTHER, and the the IN clause will either be IN (#THEONE.RowNo) (correlated with the #THEONE rows) or IN (), depending on whether #THEOTHER contains any rows or not.

Steve Kass
Drew University

rawheiser wrote:

Behavior I found:

Invalid Column name in select embedded in a IN() clause appears to
return a NULL, and fails silently - completing the query giving
incorrect results.

Behavior I expected:

RunTime error, as column didn't exist.

-------------------------------------------------------

create
table #THEONE
( RowNo int not null )

create
table #THEOTHER
( RowNumb int not null )

select *
from #THEONE
where RowNo not in (
-- SHOULD THROW RUNTIME ERROR --
select RowNo from #THEOTHER
)

-------------------------------------------------------

-- select @@VERSION

-- Microsoft SQL Server 2000 - 8.00.871 (Intel X86)
-- Oct 21 2003 16:16:50
-- Copyright (c) 1988-2003 Microsoft Corporation
-- Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

[Erland Sommarskog]

[posted and mailed, please reply in news]

rawheiser (ra*******@gmail.com) writes:

-------------------------------------------------------

create
table #THEONE
( RowNo int not null )

create
table #THEOTHER
( RowNumb int not null )

select *
from #THEONE
where RowNo not in (
-- SHOULD THROW RUNTIME ERROR --
select RowNo from #THEOTHER
)

That is perfectly legal SQL, although meaningless. This does give you a
compile-time error (not
run-time):

select *
from #THEONE a
where a.RowNo not in (
-- SHOULD THROW RUNTIME ERROR --
select b.RowNo from #THEOTHER b
)

Always, always, use column aliases for all you column references when
you have more than one table in a query!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[rawheiser]

Doh! ... Variable Scopeing ... I should have know that.

The obscure I can get right away, the obvious takes longer.
Thanks ...