BUG: Possible Handled AV w/ Sub-query Using NOT IN and '>'

• Microsoft SQL Server version 6.0

SYMPTOMS

It is possible to experience a handled access violation (AV) when using an IN condition with a sub-query.

CAUSE

There are several conditions that must be present to get the AV:

1. The select search criteria must contain an IN condition where the set of values is generated using a sub-query. The column referenced to the left of the IN condition and the column referred to in the sub-query both need to be defined as CHAR NOT NULL. An additional requirement is that the size of the column to the left of the IN condition must be greater than the column referred to in the sub-query.



2. The second search criteria that the column referenced to the left of the IN condition must also be compared to a string value using either <, >, >=, or <= operators.



3. There must be a non-unique index on the column referenced to the left of the IN condition.

create table test1 (a char(2) not null)
create table test2 (b char(1) not null,
              c char(1) not null)
go
insert test1 values ('1')
insert test2 values ('1','1')
go
create nonclustered index a on test1(a)
go
select a from test1 where a not in (select c from test2
where b = '1')and a <= '1'
go 

WORKAROUND

There are two possible means to workaround this problem. The first is to drop the current index and create either a unique clustered or non- clustered index. Or you can use the convert() function to step-up the column referenced in the sub-query to the same size as the column referenced on the left of the IN condition. An example of this 'step-up' would be to change the query to the following:

select a from test1 where a not in (select convert(char(2),c) from test2 where b = '1') and a <= '1'

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

Additional query words: sql operator select sub-query

Keywords          : kbprg SSrvProg kbbug6.00 
Version           : 6.0
Platform          : WINDOWS 
Issue type        : 

Last Reviewed: March 25, 1999