Microsoft KB Archive/151583

{|
 * width="100%"|

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

 * }

Q151583

-

The information in this article applies to:


 * Microsoft SQL Server version 6.0

-

BUG#: 13873 (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 :

Issue type :

Technology : kbSQLServSearch kbAudDeveloper kbSQLServ600