31.08.2021 23:53, Mark Rotteveel wrote:
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).
Below is comment from code ArithmeticNode::divide2() at
src\dsql\ExprNodes.cpp:
* In the SQL standard, the precision and scale of the quotient of exact
* numeric dividend and divisor are implementation-defined: we have
defined
* the precision as 18 (in other words, an SINT64), and the scale as the
* sum of the scales of the two operands. To make this work, we have to
* multiply by pow(10, -2* (scale of divisor)).
...
* To maximize the amount of information in the result, we scale up
* the dividend as far as we can without causing overflow, then we
perform
* the division, then do any additional required scaling.
So, 127.13 / 3.4618 evaluates in a following way:
127.13 is NUMERIC(5, 2) with value 12713 and scale -2
3.4618 is NUMERIC(5, 4) with value 34618 and scale -4
12713 * 10^8 / 34618 = 36723669 (integer division, no rounding)
Result is NUMERIC(18, 6) with value 36723669 and scale -6, or 36.723669
Hope it helps,
Vlad
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
