Icon View Incident Report

Serious Serious
Reported By: Oliver Bock
Reported On: 11/7/2005
For: Version 4.21 Build 11
# 2126 Recursive Join in WHERE Clause Causes Incorrect Results

WHERE fails in triangular join.

Run the SQL below with the supplied tables and you'll see that for some rows, ResourceAgg.AssetGLAID = GL.GLAccountID.

But if I add:

WHERE ResourceAgg.AssetGLAID = GL.GLAccountID

then the result set is empty!

I can work around it using:

WHERE ResourceAgg.AssetGLAID - GL.GLAccountID = 0

select GL.GLAccountID, ResourceAgg.AssetGLAID,
ResourceAgg.AssetGLAID = GL.GLAccountID
from GL
join VAsset on GL.SourceID = VAsset.VPurchaseDetID 
join ResourceAgg on VAsset.ResourceID = ResourceAgg.ResourceID
WHERE ResourceAgg.AssetGLAID = GL.GLAccountID



Resolution Resolution
Fixed Problem on 11/11/2005 in version 4.22 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