Reported By: Robert Kaplan Reported On: 11/23/2001 For: Version 3.03 Build 1
# 961An 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
ResolutionFixed Problem on 11/24/2001 in version 3.04 build 1