Icon View Incident Report

Serious Serious
Reported By: Robert Kaplan
Reported On: 11/23/2001
For: Version 3.03 Build 1
# 961 An IS NULL Test Against Column in a Target Table of an SQL LEFT OUTER JOIN Causes Incorrect Results

Review the following query. I don't think DBISAM is correct. Look at the result of the JOIN before the WHERE. After applying WHERE (DETAIL.YEAR = 2000) OR (DETAIL.YEAR IS NULL) should only include rows 2 and 3, but DBISAM is returning all rows, with NULLs for rows 1 and 4.

SELECT * FROM MASTER LEFT OUTER JOIN DETAIL ON MASTER.ID = DETAIL.ID

Result:

Row Mast Detail
1 Master1 1999
2 Master1 2000
3 Master2 NULL (no details)
4 Master3 1999

Now apply:

WHERE (DETAIL.YEAR = 2000) OR (DETAIL.YEAR IS NULL)

Row Mast Detail
1 Master1 NULL
2 Master1 2000
3 Master2 NULL
4 Master3 NULL



Resolution Resolution
Fixed Problem on 11/24/2001 in version 3.04 build 1
Image