Thnax for reply,

I tested this on my machine (on FB service) - and results are as
were described (and select returns strange non-rounded nummbers like
154,5899963378906 even
for simple select f1 from fact_table_float (when field is defined as
float and even in the case when 154.5900000 is inserted))... Well as
I am using double precision fields in many places then it is hard to
decide what to do... So far it was worked mostly.

--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:
>
> --- 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
>