Well, to me this looks like a serious bug. I have to admit, the steps to reproduce are not exactly simple, but I will have a go at explaining it and we'll go from there:

The problem first started when we do a mass-extraction of our data for reporting. One of the computed columns is returning a massively negative value: -92233720368547760.00

This is a computed column, which is defined as follows:

ALTER TABLE CONTRACT ADD TOTALTAX COMPUTED BY ((SELECT SUM(BE.TAXAMOUNT) FROM BUDGETENTRY BE JOIN BUDGET B ON (B.BUDGETID = BE.BUDGETID) WHERE B.CONTRACTID = CONTRACT.CONTRACTID));

The BudgetEntry.TaxAmount columns is also a computed column, based on a stored procedure:

ALTER TABLE BUDGETENTRY ADD TAXAMOUNT COMPUTED BY ((SELECT TAXAMOUNT FROM P_TAX_AMOUNT(TOTALVALUE_EXTAX, TAXRATEID)));

And if I still have your atention :) you probably want to see what this SP looks like:

ALTER PROCEDURE P_TAX_AMOUNT (
AMOUNT MONEY,
TAXRATEID FOREIGNKEY )
RETURNS (
TAXAMOUNT MONEY )
AS
DECLARE VARIABLE RATE TYPE OF PERCENTAGE;
BEGIN
/* given an amount and a tax rate id, calculates the tax amount */
SELECT RATE FROM V_TAXRATE
WHERE TAXRATEID = :TAXRATEID
INTO :RATE;

IF (RATE IS NULL) THEN
TAXAMOUNT = 0;

/* casting here is done for best accuracy http://www.firebirdfaq.org/faq207/ */
TAXAMOUNT = (CAST(RATE AS DOUBLE PRECISION) / 100) * CAST(AMOUNT AS DOUBLE PRECISION);

SUSPEND;
END^


Now, if I do a simple selection and grab the value from this column, it has never been incorrect, So for example a query like this will be fine:

Select TotalTax From Contract Where ContractID = 1234

In addition, if I have no where clause and just do something like this:
Select TotalTax From Contract
the values are fine.

Finally, if I do a query like this:
Select * from Contract Where ContractID = 1234
everything is fine.

It is when I start to extract records on mass that the calcs go crazy.

So for example, this evil query will return incorrect results:

Select * from Contract

The records that are wrong are very inconsistent. For example, let's say I throw on an Order By clause...

Select * from Contract
Order By ContractID

From this, I can hand-pick a given ID (let's say ContractID 1234 returns a bad TotalTax) If I modify the query as such:

Select * from Contract
Order By TotalTax

And then find ContractID 1234, chances are the value will be correct.

This last query is most interesting because you would expect that if the FB engine is the culprit, then the massively negative values would be at the very top of the list right? But they aren't. In fact they fall exactly where they should fall, given the Order By clause.

So this makes me think my client tool (or FBClient) is getting it wrong.

I can reproduce this using Delphi 7 against FB 2.1.3 using the Delphi InterBase components. I can also produce it via FlameRobin. I don't know what data-access components it uses but since FR is cross-platform that would rule out the Delphi Interbase components as the problem. I haven't tried any other front end tools.

So this leaves me scratching my head how I can work around this issue. And if this is a known bug.

If it isn't a know bug, then I'm happy to supply test data so it can be fixed properly. At the moment I'm pointing the finger at FBClient.dll since both Delphi and FlameRobin would be talking to it. And the fact that my OrderBy test is returning the values in the correct order, which means FBServer seems to be working corretly. But I don't know its internals well enough to say for sure. Perhaps it's sorting algo and data distribution routines differ in which case FB Server could still be the culprit.

ideas anyone? Should I copy past all this into some FB Bugzilla DB?

thanx
-randall sell




[Non-text portions of this message have been removed]