Icon View Incident Report

Serious Serious
Reported By: Donat Hebert
Reported On: 11/18/2003
For: Version 3.26 Build 1
# 1454 SQL JOIN Conditions that Rely on Cartesian Products Confuse Query JOIN Optimization

"CostCentre between f2.CostCBeg and f2.CostCEnd" had to be modified to read:

(F2.CostCBeg <= m1.CostCentre and F2.CostCend >= m1.CostCentre) in order to
work.

Select e.RecIdent, F1.EarnType, F1.Entity, f1.EmpType, f1.EmpClass,
F1.EmpOrg, F1.BargUnit
, f2.Sequence, f2.CostcBeg, f2.CostCEnd, f2.DistribCode
into 'D:\BUDASTA\USERS\BILL\TEMP\Match'
FROM Bgsaln m1, 'D:\BUDASTA\BgdistET' F1
Join BgSaEarn e on e.Identifier = m1.Identifier
join 'D:\BUDASTA\BgDisetd' f2 on f2.identifier = f1.Identifier
Where m1.CostCentre between '0' and '~'
and m1.CostCentre between f2.CostCBeg and f2.CostCEnd // this line ...


Order by RecIdent, EarnType, Entity Desc, EmpType Desc, EmpClass Desc,

EmpOrg Desc, BargUnit Desc, Sequence ;



Comments Comments and Workarounds
The problem was with the query join optimization and rewriting. Another workaround besides the one described above by Donat would be to use the NOJOINOPTIMIZE clause at the end of the problem query.


Resolution Resolution
Fixed Problem on 11/20/2003 in version 3.27 build 1
Image