View Incident Report

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
       begin
        Close;
        SQL.Clear;
        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');
        ExecSQL;

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

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

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap