Icon View Incident Report

Minor Minor
Reported By: Robert Eastlack
Reported On: 10/5/2005
For: Version 4.21 Build 10
# 2123 Mixing Float and Integer Columns in Joins Can Cause Incorrect Results in Some Cases

Is it possible for NOJOINOPTIMIZE to actually alter the results of a query? I have some queries that are returning very different
results depending on whether or not NOJOINOPTIMIZE is being used.

SELECT   ORDERS.OrderNo,
         ORDERS.OperatorID,
         ORDERS.OrderDt,
         ITEMS.ItemNo,
         ITEMS.ProductNo,
         ITEMS.Quantity,
         ITEMS.Price,
         CUSTOMER.Company,
         OrderDet.OrderMessage1
    FROM ORDERS,
         ITEMS,
         CUSTOMER,
         PRODUCTS,
         OrderDet,
         ProcessInvoices
   WHERE ((ORDERS.OrderNo = ITEMS.OrderNo)
     AND (ORDERS.OrderNo = ProcessInvoices.OrderNo)
     AND (ORDERS.CustNo = CUSTOMER.CustNo)
     AND (ITEMS.ProductNo = PRODUCTS.ProductNo)
     AND (OrderDet.OrderNo = ProcessInvoices.OrderNo))
NOJOINOPTIMIZE



Comments Comments and Workarounds
The problem was with the join between the processinvoices table and the orderdet table due to mixed types (integer and float) combined with the existence of a NULL for the initial column value for one of the tables. Internally, the NULL value messed up the conversion between the two types, resulting in the join finding no records.


Resolution Resolution
Fixed Problem on 10/16/2005 in version 4.21 build 11


Products Affected Products Affected
DBISAM Additional Software and Utilities
DBISAM CLX Client-Server
DBISAM CLX Client-Server with Source
DBISAM CLX Standard
DBISAM CLX Standard with Source
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