Icon View Incident Report

Serious Serious
Reported By: Clive
Reported On: 4/24/2006
For: Version 4.23 Build 2
# 2182 Joins with Interdepencies That Span More than One Join Cause Join Optimization Problems

This query does not produce any rows in 4.22B4, but does in older versions of DBISAM, for example 4.04 which I have a copy of DBSYS for. In order to make this query generate rows I need to change the join on scenario stream detail period column to ssd.scenario_code = 335.

select ssd.scenario_code,*
from clive  
inner join category c on c.category_code = clive.category_code  
inner join scenario_detail s on s.period = clive.period 
inner join scenario_stream_detail ssd on
ssd.category_code = c.category_code and
ssd.period = clive.period and ssd.scenario_code = s.scenario_code
where s.scenario_code = 335;

Comments Comments and Workarounds
This bug is very difficult to explain, but it basically revolves around having interdependencies in joins that cause the join optimizer to reorder the joins in a way that causes incorrect execution. The workaround is to put the NOJOINOPTIMIZE clause at the end of the affected query.

Resolution Resolution
Fixed Problem on 4/24/2006 in version 4.24 build 1

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