Numeric Overflow with Numeric(18,7) in Firebird

Question

I need an accuracy of 7 houses in a given split calculation, but when the divider is very small, it generates an extensive number. When I try to give the cast, it gives Numeric overflow, even the number "fitting" in the cast. sql is the following:

select
   cast(2012 as numeric(18,7))
  ,cast(0.01 as numeric(18,7))
  ,cast(2012 as numeric(18,7)) / cast(0.01 as numeric(18,7)) VALOR
from ...

The first two fields generate correctly, but if you do the division returns the error. The result of the division would be 201.200.00, which fits in the Numeric(18,7).

I tested it that way:

select
   cast(2012 as numeric(18,7))
  ,cast(0.01 as numeric(18,7))
  ,cast(2012 as numeric(18,7)) / cast(0.01 as numeric(18,6)) VALOR
from ...

If you give the second cast with 18.6, it works, but in other calculations of the same table I will lose 1 digit of accuracy.

I’m using Firebird 2.5

Answer 1

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

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 DOUBLE PRECISION before doing the operation and converting again 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 major 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

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

http://www.firebirdfaq.org/faq79/

http://www.firebase.com.br/artigo.php?id=104

Answer 2

In Firebird literals with a dot decimal are of the type NUMERIC , nay DOUBLE PRECISION (or another type of floating point). This means that it will apply its exact numerical calculation rules.

Thus, with select 187/60.00 from rdb$database That means 187 is a INTEGER and 60,00 is a NUMERIC(18,2)

If two operands OP1 and OP2 are exact numeric with scale S1 and S2, respectively, then OP1 + OP2 and OP1-OP2 are exact numeric with an accuracy of 18 and dimensioning the greater of S1 and S2, while OP1 * OP2 and OP1 / OP2 are accurate numerical with 18 and S1 scale + S2 accuracy. (The scales of these operations, except division, are specified by SQL standard The standard makes the accuracy of all these operations, and the scale of Divison, defined implementation in:. We set the accuracy of 18 years, and the scale of division as S1 + S2, the same that is required by the standard in the case of multiplication.)

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

The fact that the number appears to be truncated is a result of the exact numerical calculation application: the calculation stops when the last digit has been calculated. The fact that there is a rest has no influence on the result of the calculation:

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

Looking at SQL: 2011 Foundation specification the fact that Firebird considers 60.00 to be an exact numeric is correct, as 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 rules of syntax:

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

Section 6.27 specifies the following syntax rules:

1) If the declared type of both operands of an arithmetic operator Dyadic is exact numeric, then the declared type of the result is an exact numerical type defined by the implementation, with precision and scale 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 multiplication is defined by the implementation, and the scale is S1 + S2. d) The accuracy and scale of the split result is defined by the implementation. In other words, Firebird’s behavior is in accordance with the SQL standard. It seems most of the other database you tried (with the possible exception of SQL Server), use a relatively large value for scale when performing division, or seem to use numerical approximate behavior (aka double precision).

An alternative solution would be to use an approximate numerical literal. Use of exponent zero or E0 will cause the number to double precision 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