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

 Last reviewed: May 2, 1997
Article ID: 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 : kbbug6.00 SSrvProg
Version : 6.0
Platform : WINDOWS
Issue type : kberrmsg

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: May 2, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.