Icon View Incident Report

Serious Serious
Reported By: Frank Fischer
Reported On: 4/12/2005
For: Version 4.19 Build 1
# 2011 SQL LEFT OUTER JOINs Containing OR Operators Can Cause Incorrect Results

I upgraded the DBISAM-database from 3.24 to 4.19 Now, I've the following problem with a SQL written below.

The main problem is the first SELECT statement. The result should be all records from table TKd with joined records from table TKdStat. It was this result before I upgraded from version 3.24 to 4.19 But if I have only one condition in the ON - clause ( "[...] ON (KD_ID = TKSKD_ID AND (TKSStat_ID = 1 ) [...]" ) it works again. That's a bit strange because I can't see any reason why the additional condition should nullify the result I got before.

SELECT ANREDE, STRASSE, PLZ, ORT, TEL, FIRMA,  TKd.KD_ID AS KD_ID, 
TKd.KD_ID AS TKdKD_ID, TKd.Bemerk, TKSStat_ID, 
IF(KD_Nr=NULL THEN Name || ' ' || Vorname ELSE Name || ' ' || Vorname 
|| ' - Knd.Nr.: ' || KD_Nr) AS Kunde 
 INTO MEMORY\TMEMKd FROM TKd LEFT OUTER JOIN TKdStat ON (KD_ID = 
TKSKD_ID AND (TKSStat_ID = 1 or TKSStat_ID = 4));

SELECT TMEMKd.*, TVertr.Kd_ID AS VertrKD_ID, TVertr.VArtInd, TVertr.
VertrArt 
 FROM MEMORY\TMEMKd 
 Left OUTER JOIN TVertr ON  (TKdKd_ID = TVertr.Kd_ID) 
 WHERE (TKSStat_ID = NULL )
 GROUP BY TKdKD_ID;



Resolution Resolution
Fixed Problem on 4/13/2005 in version 4.20 build 1


Products Affected Products Affected
DBISAM Additional Software and Utilities
DBISAM ODBC Client-Server
DBISAM ODBC Client-Server with Source
DBISAM ODBC Standard
DBISAM ODBC Standard with Source
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source

Image