Numeric Overflow with Numeric (18,7) in Firebird

Your problem is common when dealing with this kind of data. It turns out that the result of a Numeric(18,7) * Numeric(18,7) multiplication is greater than the type Numeric can accept. At a%% multiplication, the result is a Numeric(a,b) * Numeric(a,b) type. In your case this means that the result is the below you can see that it is impossible to execute:

SELECT
  cast( 2012/0.01 as numeric(18,14)) 
FROM rdb$database

In these cases, it is often best to change the order of operations or convert to Numeric(a,b+b) before doing the operation and convert back to the desired type at the end. Example:

SELECT
  CAST(CAST(CAST(2012 AS NUMERIC(18,7)) AS DOUBLE PRECISION) / CAST(CAST(0.01 AS NUMERIC(18,6)) AS DOUBLE PRECISION) AS NUMERIC(18,6)) AS valor
FROM rdb$database

This would not cause more problems to lose accuracy:

SELECT
   CAST(1 AS NUMERIC(18,6)) / CAST(3 AS NUMERIC(18,6))
   ,CAST(CAST(CAST(1 AS NUMERIC(18,7)) AS DOUBLE PRECISION) / CAST(CAST(3 AS NUMERIC(18,6)) AS DOUBLE PRECISION) AS NUMERIC(18,6))
FROM rdb$database

Update: The following links can help you to understand more about this problem:

link

link

In Firebird literals with a% point of% are of type decimal , not NUMERIC DOUBLE (or other type of floating point). This means that it will apply your exact numerical rules of calculation.

So, with PRECISION this means that 187 is select 187/60.00 from rdb$database and 60% is INTEGER

  

If two operands OP1 and OP2 are exact numeric with scaling S1 and S2,   respectively, then OP1 + OP2 and OP1-OP2 are exact numeric   with an accuracy of 18 and the largest dimension of S1 and S2, while OP1 *   OP2 and OP1 / OP2 are accurate numerically with precision 18 and S1 scale + S2.   (Scales of these operations except division are specified by the   The standard makes the precision of all these operations, and the   of Divison, defined implementation in :. We defined the precision of 18   years, and the division scale as S1 + S2, the same as required by   standard in the case of multiplication.)

When one of the operands is an integral type, it is considered as a numeric with a scale of 0. Therefore, in this case you have NUMERIC(18,2) and based on the above rules, the result is NUMERIC(18,0)/NUMERIC(18,2)

The fact that the number appears to be truncated is a result of accurate numerical calculation: calculation stops when the last digit was calculated. The fact that there is a remainder has no influence on the calculation result:

60.00 / 187 \ 3.11 180 --- 70 60 -- 100 60 -- (stop) 40 

Looking at the SQL: 2011 specification Foundation the fact that Firebird considers 60.00 to be an exact numeric is correct, since it has the following production rules for literals in section 5.3:

<literal> ::=
    <signed numeric literal>
  | <general literal>

<unsigned literal> ::=
    <unsigned numeric literal>
  | <general literal>

<signed numeric literal> ::=
    [ <sign> ] <unsigned numeric literal>

<unsigned numeric literal> ::=
    <exact numeric literal>
  | <approximate numeric literal>

<exact numeric literal> ::=
    <unsigned integer> [ <period> [ <unsigned integer> ] ]
  | <period> <unsigned integer>

<sign> ::=
    <plus sign>
  | <minus sign>

<approximate numeric literal> ::=
    <mantissa> E <exponent>

<mantissa> ::=
    <exact numeric literal>

<exponent> ::=
    <signed integer>

<signed integer> ::=
    [ <sign> ] <unsigned integer>

<unsigned integer> ::=
    <digit>...

And syntax rules:

  

21) One without an implied one   after the last. 22) The declared type of an ENL is an exact numerical type defined by the implementation whose   scale is the number of s to the right of the. There will be a type   exact numeric able to represent the value of ENL exactly.

Section 6.27 specifies the following syntax rules:

  

1) If the declared type of both operands of an arithmetic operator   dyadic is exact numerical, then the declared type of result is   an exact numerical type defined by the implementation, accurately and   determined as follows: a) Let S1 and S2 be the scale of the   first and second operands respectively. (b) the accuracy of the   result of addition and subtraction is defined by the implementation, and   scale is the maximum of S1 and S2. c) The accuracy of the result of the   multiplication is defined by the implementation, and the scale is S1 + S2. (d)   The accuracy and scale of the division result is defined by   Implementation. In other words, the behavior of Firebird is   in accordance with the SQL standard. Apparently most of the other   database that you tried (with the possible exception of SQL Server),   use a relatively large value for the scale when performing   division, or appear to use approximate numerical behavior (aka   double precision).

A workaround would be to use an approximate numeric literal. Use of exponent zero or E0 will make the double precision number without powers of ten. For example:

select 187E0/60.00 from rdb$database; -- result: 3.116666666666667
    -- or 
select 187/60.00E0 from rdb$database; -- result: 3.116666666666667

stackoverflow Gringo