BUG: Select Using 'or' From View Defined w/ '>=' Can Cause AV

• Microsoft SQL Server version 6.0

SYMPTOMS

A select using 'or' in the where clause on a view that is defined using a '>=' (greater than or equal to) comparison operator in the where clause can result in an a Thread Level Handled AV (Access Violation). The AV occurs during the process of determining the cost of a particular access plan that might be used to fulfill the requested select statement.

WORKAROUND

Changing the information available prior to SQL Server's evaluation of the query cost can be used to workaround this problem. Removing 'or' from the where clause as defined in the view, changing the type of comparison operator used in the view where clause, changing the index on the base table to 'clustered not unique', changing the columns being referenced by select on the view, and/or removing the 'or' found in the select where clause can all affect the occurrence of this problem.

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.

This problem does not happen in Microsoft SQL Server version 6.5.

MORE INFORMATION

The following is a replication scenario provided to illustrate the problem:

create table test
(col1                     char(1)       NOT NULL,
 col2                     char(1)       NOT NULL,
 col3                     char(1)       NOT NULL)
go
insert test values ('1','Y','A')
insert test values ('2','Y','A')
go
create unique clustered index idx1 on test (col1)
go
create view vtest as select * from test
where col1 >='' or col2 = 'Y'
go
select col1 from vtest where
col1 = '1' or col1 = '2'
go 

Additional query words: DEFAULT VALUES INSERT

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

Last Reviewed: March 26, 1999