On 29-10-2015 19:22, hamacker [email protected] [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
