On 2021-08-31 21:10, Dimitry Sibiryakov wrote:
Mark Rotteveel wrote 31.08.2021 19:05:
What does SQL standard say about it?
Not much, beyond "iv) The precision and scale of the result of
division are implementation-defined."
So theoretically result of division of two exact numbers can be
inexact number like DOUBLE PRECISION but it has not enough precision
to make 1/3*3=1 anyway.
No, the result of the division between two exact numeric values must be
an exact numeric value ("the declared type of both operands of a dyadic
arithmetic operator is exact numeric and the declared type of the result
is an implementation-defined exact numeric type", same section of
SQL:2016-2).
The dialect 1 behaviour is not compliant because the result is not an
exact numeric value, but an approximate numeric value (double
precision).
The only debatable feature of dialect 3 division is the fact the
calculation stops (equivalent to floor rounding), while reduction of
scale through assignment or cast applies half-up rounding, on the other
hand, this behaviour is consistent with integer division, otherwise
NUMERIC(18,0) division and BIGINT division would have to behave
differently (e.g. 14/3 = 5 for NUMERIC(18,0) vs = 4 for BIGINT), which
would be confusing as hell, though technically you could solve that by
applying the "The precision and scale of the result of division are
implementation-defined." (there is no requirement that BIGINT/BIGINT is
BIGINT or NUMERIC/DECIMAL with a scale of 0).
Mark
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
