--- In firebird-support@yahoogroups.com, "vincent_kwinsey"
<vincent_kwinsey@...> wrote:

>
> I have the following test case (all mentioned fields are defined as
> double precision), the results are the same in both cases - when the
> same fdb file is accessed by Interbase 6.0.1 or Firebird 1.5.3 server
> service:
>
> select field1, field2, field1-field2 from fact_table
> ====
> 154,59 153.64 0.949999999999932
>
> I expected 0.95 in the last column... So - there is no problem when
> this result set is shown on GUI (whatever), but it is hard to decide
> what to do when the result of query is assigned to some other variable
> (in SP or trigger, e.g.) - should I make non-clean code and put
> rounding in every place or it is possible to choose more appropriate
> datatype to avoid this? Anyway numeric(...,...) is using double
> precision.
>
> I have heard about some bug in some processors' FPU, can this be
> attributed to this strange behavior?

Extremely unlikely.

This does not occur on my install FB 1.5.3 providing both Field1 and
Field2 are of type numeric.

My guess is that you have defined these two fields as a floating point
datatype. Most floating point numbers can not be expressed as exact
decimal numbers, and Firebird may have to round them to display, so
154.59 might actually be 154.59000000043903038487483498532823849.....

When you subtract two floating point numbers, it is not the same as
rounding them and then subtracting the rounded amounts.

Here is your example in a simple SQL script (be careful it will drop
the FACT_TABLE table at the end).

---

CREATE TABLE FACT_TABLE
(
FIELD1 NUMERIC(9,2),
FIELD2 NUMERIC(9,2)
);
COMMIT;
INSERT INTO FACT_TABLE (FIELD1, FIELD2) VALUES (154.59, 153.64);
COMMIT;
select field1, field2, field1-field2 from fact_table;
COMMIT;
DROP TABLE FACT_TABLE;
COMMIT;

---

The output in iSQL looks like:


FIELD1 FIELD2
============ ============ =====================

154.59 153.64 0.95

Or you can do it yourself if you don't believe me to convince yourself
your CPU is not crazy.

Now lets now repeat the same experiment but accidentally use a
floating point number in the definitions.

Again, be careful it will drop the FACT_TABLE table at the end

---

CREATE TABLE FACT_TABLE
(
FIELD1 FLOAT,
FIELD2 FLOAT
);
COMMIT;
INSERT INTO FACT_TABLE (FIELD1, FIELD2) VALUES (154.59, 153.64);
COMMIT;
select field1, field2, field1-field2 from fact_table;
COMMIT;
DROP TABLE FACT_TABLE;
COMMIT;

---

Looking at the iSQL output now:

FIELD1 FIELD2
============== ============== =======================

154.59000 153.64000 0.9499969482421875

Which looks suspiciously like the numbers you reported.

Lesson:

Floats are a good thing, but they give you a huge range of possible
values at the expense of accuracy, so they are not a simple substitute
for numerics.

Adam