Icon View Incident Report

Serious Serious
Reported By: Ralf Bertoldi
Reported On: 3/21/2013
For: Version 4.35 Build 1
# 3751 Queries with Joins Can Return Incorrect Results Depending upon the JOINOPTIMIZE Clause

In DBISAM 4.35, I noticed that some SQL statements with JOIN's return different results if they are optimized/not optimized (NOJOINOPTIMIZE).

SELECT
k.aid,
m1.products_options_values_name
,m1.products_options_values_id
 FROM atkombi k
 INNER JOIN athead ah ON k.artinr=ah.artinr
 LEFT OUTER JOIN products_options_values m1 ON 
   k.a1_products_options_values_id=m1.products_options_values_id 
   AND m1.products_options_id=ah.a1_products_options_id 
   AND m1.language_id=1
 WHERE k.artinr=124;

SELECT
k.aid,
m1.products_options_values_name
,m1.products_options_values_id
 FROM atkombi k
 INNER JOIN athead ah ON k.artinr=ah.artinr
 LEFT OUTER JOIN products_options_values m1 ON 
   k.a1_products_options_values_id=m1.products_options_values_id 
   AND m1.products_options_id=ah.a1_products_options_id 
   AND m1.language_id=1
 WHERE k.artinr=124
 NOJOINOPTIMIZE;



Comments Comments
This incident and incident report #3729 were both caused by some internal optimizations to the way that joins were being evaluted.


Resolution Resolution
Fixed Problem on 3/21/2013 in version 4.35 build 2


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 ODBC Trial
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source
DBISAM VCL Trial

Image