FIX: IN Condition with More Than 15 Values May Fail

ID: Q133407

• Microsoft SQL Server, version 6.0

SYMPTOMS

If the IN conditional expression of the WHERE clause contains more then 15 constants and is being tested against an INT NULL column, then it will return no rows even if there are qualifying rows.

WORKAROUND

Use an INT NOT NULL column to do these qualifications.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. This problem was corrected in Service Pack 1 for SQL Server version 6.0. For more information, contact your primary support provider.

MORE INFORMATION

The following commands demonstrate the problem:

   create table t1(c1 int null)
   go
   insert t1 values( 1)
   insert t1 values( 2)
   insert t1 values( 3)
   insert t1 values( 4)
   insert t1 values( 5)
   go
   print "This SELECT fails"
   go
   select c1 from t1 where c1 in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
   13, 14, 15, 16)
   go
   print "This SELECT works"
   go
   select c1 from t1 where c1 in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12,
   13, 14, 15)
   go

Keywords          : kbprg SSrvProg SSrvTran_SQL kbbug6.00 kbfix6.00.sp1 SSrvWinNT 
Version           : 6.0
Platform          : WINDOWS

Last Reviewed: May 1, 1997