BUG: Access Violation In Outer Join With Null Check On Not Null Column (271635)

The information in this article applies to:

Microsoft SQL Server 6.5

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.

Modification Type:	Major	Last Reviewed:	10/3/2003
Keywords:	kbBug kbpending KB271635