Icon View Incident Report

Serious Serious
Reported By: Jon Lloyd Duerdoth
Reported On: 10/7/1999
For: Version 1.16 Build 1
# 475 Using Identical Column Names in WHERE Clause of Joined SQL Statement Returns Incorrect Results

If I assign the first SQL statement below to a query and open the Query in the IDE, the resultant table gives me the records I expect.
If I use the statement in the program statements below, it gives different run-time results (far fewer records). The statements are identical but the results are different.

{ SQL Text }

select * from "Accounts.dat" Accounts
   Left outer JOIN "contrecs.dat" Contrecs
      ON  (Accounts."AccID" = Contrecs."AccID")
   where (Accounts.AccID <= "1999-006" and ContribOrReceipt = "C")
      or (ContribOrReceipt = "R")
   order by AccID, ContribOrReceipt, QuarterID,
            FundReportOrder, ContributionDate, FundID

{ Program statements }

// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
procedure TForm1.Button2Click(Sender: TObject);
var SQLstr : string;
begin
   dbiqContRecs.close;
   SQLstr := 'select * from "Accounts.dat" Accounts ' +
             '   Left outer JOIN "contrecs.dat" Contrecs ' +
             '      ON  (Accounts."AccID" = Contrecs."AccID") ' +
             'where (Accounts.AccID <= "1999-006" and ContribOrReceipt =
"C") ' +
             '      or (ContribOrReceipt = "R") ' +
             '   order by AccID, ContribOrReceipt, QuarterID, ' +
             '             FundReportOrder, ContributionDate, FundID ';
   dbiqContRecs.SQL.Clear;
   dbiqContRecs.SQL.Add(SQLstr);
   dbiqContRecs.open;
end;
// ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



Comments Comments and Workarounds
This problem was caused by having identical column names in both joined tables, but not specifying table correlation names when referencing the columns. Internally the DBISAM engine was not handling this situation properly and would return incorrect results.


Resolution Resolution
Fixed Problem on 10/10/1999 in version 1.17 build 1
Image