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
Pefeito, I gave the cast to double Precision in the execution of the calculation and back stop Numeric(18,7) later, It was right. Thanks!
– Vinicius
@Vinicius good that helped. Just make sure to do the necessary tests. This part of precision is always complicated.
– EMBarbosa