Bug or By Design

5 Mar 2007 5:56 PM

Glenn Coello

TableA contains FieldA varchar(10) NOT NULL

TableB contains FieldB varchar(10) NULL

Using Query Analyzer SS2K SP4 I run two queries.

SELECT FieldA from TableA
WHERE FieldA NOT IN (SELECT FieldB from TableB)
- this query returns 0 rows

SELECT FieldA from TableA
WHERE FieldA NOT IN (SELECT FieldB from TableB)
ORDER BY FieldA
- this query returns the correct results set

Is this a bug, or is this working as it should?

5 Mar 2007 7:46 PM

Tibor Karaszi

If the only difference between the two is the ORDER BY clause, then they definitely should return
the same number of rows. If they don't, then you have found a bug in the optimizer and see if
there's a fix for it (KB etc) and of not, consider reporting. Also, consider using NOT EXISTS
instead, perhaps that won't expose the (?) bug.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Show quote

"Glenn Coello" <GlennCoe***@discussions.microsoft.com> wrote in message
news:6D92025C-A9BA-4FF4-BA2B-B265119E0BC0@microsoft.com...
> TableA contains FieldA varchar(10) NOT NULL
>
> TableB contains FieldB varchar(10) NULL
>
> Using Query Analyzer SS2K SP4 I run two queries.
>
> SELECT FieldA from TableA
> WHERE FieldA NOT IN (SELECT FieldB from TableB)
> - this query returns 0 rows
>
> SELECT FieldA from TableA
> WHERE FieldA NOT IN (SELECT FieldB from TableB)
> ORDER BY FieldA
> - this query returns the correct results set
>
> Is this a bug, or is this working as it should?