On 31-08-2021 16:19, Molnár Attila wrote:
127.13 / 3.4618 = 36,72366976717315
It isn't, taken literally in dialect 3, the result is 36,723669 ;).
EXECUTE BLOCK
RETURNS (
c0 NUMERIC(15,10),
c1 NUMERIC(15,10),
c2 NUMERIC(15,6),
c3 NUMERIC(15,5),
c4 NUMERIC(15,4))
AS
DECLARE VARIABLE a NUMERIC(15,2);
DECLARE VARIABLE b NUMERIC(15,4);
BEGIN
a = 127.13;
b = 3.4618;
c0 = a / CAST(b AS DOUBLE PRECISION);--36,7236697672, this is OK
c1 = a / b;--36,7236690000, this shoud be 36,7236700000
c2 = a / b;--36,723669, this shoud be 36,723670
c3 = a / b;--36,72367, this is OK
c4 = a / b;--36,7237, this is OK
SUSPEND;
END
If return variable scale is bigger or equal than operation result scale,
then computed value is truncated, but when return variable scale is
smaller than operation result scale then computed value is rounded.
I think in all case value should be rounded, this behaviour is
inconsistent, and also truncation is unexpected behaviour.
I opened a GitHub issue , but was closed :
https://github.com/FirebirdSQL/firebird/issues/6928
<https://github.com/FirebirdSQL/firebird/issues/6928>
Mark suggested to start a conversation here.
I didn't suggest you start a discussion about this behaviour, I
suggested you start a discussion about your wish that dialect 1 would
not be dropped ("If you want to discuss retention of dialect 1, then
please take that to firebird-devel.").
This has been the behaviour of NUMERIC/DECIMAL division in dialect 3
since InterBase 6.0. Changing that behaviour now would break or
invalidate all applications that have been built with dialect 3
semantics of NUMERIC/DECIMAL division in mind.
It complies with the SQL standard. Though to be honest, that is a pretty
low bar, as the specification doesn't specify much for NUMERIC/DECIMAL
division other than that precision and rounding is implementation
specific ("iv) The precision and scale of the result of division are
implementation-defined." - SQL:2016-2, 6.29 <numeric value expression>)
#1 : This behaviour should be documented, I found nothing regarding
this, and this result is unexpected. Calculation do not happens in
double precision, and calculation stops when result scale is reached.
The result is unexpected *to you* and does not match what you've been
relying on in dialect 1. Yes, it is not explicitly documented other than
the resulting precision, but neither is the behaviour of division in
dialect 1 other than that it results in a DOUBLE PRECISION. You can
think of NUMERIC/DECIMAL division in dialect 3 as a form of integer
division like in most programming language. That also stops when the
final digit has been calculated. For example, 14/3 = 4, not 5, 14.0/3 =
4.6, not 4.7, 14.0/3.0 = 4.66, not 4.67, etc, in dialect 1, all those
calculations produce 4.666666666666667. For dialect 3, you can think of
14.0/3.0 as doing 14000/30 and then shifting the decimal by two
positions (14000/30 = 466, shift decimal by 2 = 4.66).
You haven't brought forth any real arguments why 21+ years of dialect 3
behaviour should be thrown out the window, other than that you don't
like it because it isn't the same as in dialect 1.
I understand this is a bitter pill, but this difference is likely the
reason why you've been using dialect 1 all this time.
#2 : Calculation method should be cofigurable
#2/A: possible configuration modes
-default: current mode
-double precision: operands converted to double, calculations happens in
double, then result rounded and converted back to result type
If you want such behaviour, then you need to either stay on dialect 1,
or cast one of the operands explicitly to double precision.
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
