On 29-10-2015 19:22, hamacker sirhamacker@... [firebird-support]
wrote:

> When I try:
> select (2183.48/(1-(37.25/100))) as valor from rdb$database
>
> As result Firebird: 3465,8412
> But other databases(MSSQL) and spreadshets(Excel and Calc) the correct
> result is 3479,6494023904
>
> If I change (37.25/100) to 0.3725 as follow:
> select (2183.48/(1-0.3725)) as valor from rdb$database
>
> Then Result is correct 3479,6494023904
>
> I would like to understand why this
> or if it´s a new bug to post.
>
> Firebird-2.5.4.26856_0_Win32

It is not a bug. Literals in Firebird (and the SQL standard) are of type
NUMERIC, unless they are an explicit double precision literal.

The SQL standard describes the behaviors for addition, subtraction and
multiplication, but leaves the behavior for division to the
implementation. This also explains why you see differences between
different database systems.

For Firebird NUMERIC division is specified that the result of is of
precision 18, and scale as the scale of both operands added together
(same as that of multiplication).

The division 37.25/100 is between a NUMERIC(4,2) and a NUMERIC(3,0)
resulting in a NUMERIC(18,2) (or 0.37). For the behavior you want, you
either need to use 37.25/100.00 (DECIMAL(4,2) and DECIMAL(5,2) ->
DECIMAL(18,4)) or 37.25/100E0 (DECIMAL(4,2) and DOUBLE PRECISION ->
DOUBLE PRECISION). See also
http://ibphoenix.com/resources/documents/design/doc_154

Comparison to spreadsheets might not be a good idea: all numbers in
spreadsheets are double precision.

Mark
--
Mark Rotteveel