On Fri, Jan 26, 2018 at 8:52 PM Helen Borrie helebor@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

Salim Naufal wrote:

> I have noticed the following inconsistencies in Firebird 3.02:

[..]

> I then tried:
> SELECT CAST(10000 AS NUMERIC(18,2)) * (1.00 - (1.00/1.11)) FROM
> RDB$DATABASE and got a much better result of 991.00000

> Finally, the correct result is retrieved using:

> SELECT CAST(10000 AS NUMERIC(18,2)) * (CAST(1 AS DOUBLE PRECISION)
> - (CAST(1 AS DOUBLE PRECISION)/1.11)) FROM RDB$DATABASE

> I am not familiar with the details of the SQL standard, is this the
> proper was to round and calculate numeric values in SQL? 

You really do need to familiarise yourself with the way SQL handles
numbers, especially division. In short, with implicit casting:

- the result of division of an integer by a number of any type is
always integer, with banker's rounding

- the result of fixed numeric/fixed numeric gives FN where the scale
of the decimal part is the sum of the scales of the operands

- the result of FN/floating point or FN/FP gives FP

Hence the reason why these last two examples got closer to the result
you wanted while performing that integer division messed it up. I
suggest you play around with your formula in isql until you get the
precision and scale that you desire in your result. Don't try to use
a GUI tool for such testing as it will probably "tidy up" the output
for you and confuse the outcome.

Note, if you match the scale of your operands throughout, you might
get away with not having to cast anything. Implicit casting casts
non-integer numbers as double precision. Any operands that are
database columns are not cast implicitly - they use the type defined
for them.

Helen