PRSQL9103029: Certain Subqueries Will Give Unexpected Results
PSS ID Number: Q70849 Article last modified on 12-18-1992
1.10 1.11 4.20 OS/2
Summary: PROBLEM ID: PRSQL9103029
SYMPTOMS The following query does not return expected results if the subquery evaluates to 0 (zero): delete from tab1 where 1> (select count(*) from tab2 where col2 = tab1.col1)
CAUSE The subquery will evaluate to the value null instead of the value zero.
WORKAROUND To perform this functionality, use the following query: delete tab1 where col1 not in (select b.col1 from tab1 B where 0 > (select count(*) from tab2 where col2=b.co1))
STATUS Microsoft has confirmed this to be a problem in SQL Server versions 1.1, 1.11, and 4.2. We are researching this problem and will post new information here as it becomes available.
Additional reference words: Transact-SQL
Copyright Microsoft Corporation 1992.
