> TIBOQuery's Edit Query shows in design time the value as
> 1.63, but when shown in QReport the value is 1.64

> I casted the average result:
> cast (avg(AVERAGE) as numeric(15,4)) as aver,min(average)
> and now the results are equal (1.64) everywere.

> The offending value was 1.6355.

Curious. This, it appears to me, is a bug in dialect 1 (the
same result/problem you describe also occurs in FB v1.5.2 if
using dialect 1).

I describe it as a bug because the appropriate use of explicit
rounding (truncation) inside the engine would make the double
appear more like a real scaled integer. Whereas the problem
you are seeing shows a descrepency in the behaviour of the
(apparently) same data type between the two dialects.

It is possible that the developers do not consider this a bug,
it may be seen as "working as designed" because that is the way
floating point values work - although if that is true there are
only limited reasons for choosing the numeric data type under
dialect 1.

That is;

AVERAGE is NUMERIC(15,2)
AFAICT AVG returns a value of the same data type as the field
So AVG(AVERAGE) should be of type NUMERIC(15,2).

So if FB was doing the correct thing with AVG there should not
be any additional digits to expand. So:
cast(avg(AVERAGE) as numeric(15,4))
should result in: nn.nn00 (always '00' as last two digits)

As opposed to:
avg(cast(AVERAGE as numeric(15,4))) which should be able to
result in additional relevant decimal places.

Here is a matching test case:

SELECT
CAST(163.55 AS NUMERIC(15,2)) AS F1,
(CAST(163.55 AS NUMERIC(15,2)) / 100) AS F2,
((CAST(163.55 AS NUMERIC(15,2)) / 100) * 100) AS F3,

CAST((CAST(163.55 AS NUMERIC(15,2)) / 100) AS NUMERIC(15,2)) AS F4,

CAST((CAST((CAST(163.55 AS NUMERIC(15,2)) / 100) AS
NUMERIC(15,2)) * 100) AS NUMERIC(15,2)) AS F5

FROM RDB$DATABASE

(The F4 and F5 fields are the same as F2 and F3 defined to show
the same results occur even with additional explicit casts.)

Note: the basic "163.55 / 100" value is an attempt to simply
emulate the average of 100 entries that sum to 163.55.


Under dialect 1 this produces:
F1 = 163.55, F2 = 1.6355, F3 = 163.55
if F2 is displayed to two decimal places (by most normal
implementations of double) it would show as 1.64

Under dialect 3 this produces:
F1 = 163.55, F2 = 1.63, F3 = 163.00

Which is the strictly correct interpretation using integer
rules for multiply and divide (ie. truncation not rounding)
as is required for scaled integers. Of course truncation is
not always desirable (at least not in the uncontrolled fashion
offered by things like AVG).

The most nasty aspect of all of this is the problem with
upgrading dialect 1 to dialect 3. If done through a gfix or
whatever then you will end up with the dialect 1 problem
showing in dialect 3 because the underlying data type remains
the same. I even ran an explicit test on this and the problem
does indeed carry over.

Probably much more than anyone wanted to know.

--
Geoff Worboys
Telesis Computing