Icon View Incident Report

Serious Serious
Reported By: John Hay
Reported On: 3/17/2010
For: Version 2.03 Build 9
# 3174 Query Optimizer May Re-Order Joins in a Way that Invalidates the Query Result

The following query produces 0 records and appears to be a problem with join optimization. If I add nojoinoptimize it returns 351 "duplicate" records. I tried the same query replacing the dervived table with a "physical table" and got the same results. This was tested with b9.

SELECT DISTINCT c._id,_fkContacts,_Surname, _Forename,_fkCompanies,_fkSites
FROM Career C
JOIN Contacts C1 ON C._fkContacts=Contacts._ID
(SELECT _Surname, _Forename,_fkCompanies,_fkSites,count(*) as cnt
FROM Career
JOIN Contacts C ON _fkContacts = C._ID
GROUP BY _Surname, _Forename,_fkCompanies,_fkSites
HAVING cnt > 1) t1
ON C1._Surname=t1._Surname AND C1._Forename= t1._Forename AND
C._fkCompanies=t1._fkCompanies AND
C._fkSites=t1._fkSites order by _surname,_forename

Comments Comments and Workarounds
The problem only occurs with queries that have joins that reference 3 or more tables in the join condition, making them especially dependent upon the join ordering. Most joins only reference the 2 tables involved in the join. The workaround is as-indicated - use the NOJOINOPTIMIZE clause.

Resolution Resolution
Fixed Problem on 3/18/2010 in version 2.03 build 10

Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial