Icon View Incident Report

Serious Serious
Reported By: Don Patrick
Reported On: 6/2/2004
For: Version 4.07 Build 1
# 1746 Using a Constant in a Non-Comparison Expression in an SQL WHERE Clause Can Cause Incorrect Results

I've just started looking at V4 and ran across this problem. This query works with 3.27 but fails under 4.07. The table has open invoices spanning 6 months so there are records for it to pull up. The query below brings up no records. Now if I change the where clause to read CURRENT_DATE-InvDate <30 AND DatePaid IS Null then it pulls up every open invoice, I can change the
<30 to any number and the results are the same. But change it to >30 or any number and I get 0 records.

   with Query1 do
        SQL.Add('Select InsID, InsuranceCo, '+
                'File_Number, InvDate, [Claim Number],');
        SQL.Add('TotalFee, Owner, CityCode, SuppTotal, '+
                'SuppInvDate, SuppDatePaid Into PDue');
        SQL.Add('From Claims');
        SQL.Add('Where CURRENT_DATE-InvDate '+
                'Between 31 AND 60 AND DatePaid IS Null');
        SQL.Add('Order By InsuranceCo');

Comments Comments
The problem was the CURRENT_DATE-InvDate expression. 4.07 added some short-circuiting to constant evaluation in order to simplify expressions as much as possible that broke this.

Resolution Resolution
Fixed Problem on 6/3/2004 in version 4.08 build 1