Microsoft KB Archive/271635

= BUG: Access Violation In Outer Join With Null Check On Not Null Column =

Article ID: 271635

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q271635



BUG #: 19053 (SQLBUG_65)



SYMPTOMS
It is possible to experience an access violation on a left join if the following conditions are met:
 * The right table has a composite index where the join column is not in the first position.


 * The join condition specifies a specific OR NULL value.


 * The query uses ANSI join syntax.

For example: SELECT LT.LastName , LT.FirstName , RT.Amount FROM Customers LT LEFT JOIN Accounts RT ON   (LT.CustomerID = RT.Customer ID AND    (RT.Amount = 32 OR RT.Amount is NULL))



WORKAROUND
Here are two ways to work around this problem:
 * If the null check is omitted from the join condition, the access violation does not occur.

-or-


 * If the index on the right table is rebuilt to have the join column as the first key, the access violation does not occur.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5.

Keywords: kbbug kbpending KB271635

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.