On 02-09-2021 10:08, Omacht András wrote:
Summary: for our point of view mysql and oracle is the easiest to use as a
programmer (and maybe a data analyst who has the right to write queries).
Postre, mssql bring the solution what at least we expected and what Attila
outlined.
Firebird is working completly different as others even if you divide two
numeric values.
Yes, and that is because in those systems, the scale of the result of
division is different from Firebird (they generally either have more
digits, or possibly use floating point as well, I haven't verified this).
If you had changed the order of calculation to n1 * 100 / n2 you would
get 66.67 in Firebird (because then the result is 66.6666, rounded to
66.67 when fit into scale 2, while in the initial calculation, you first
get n1/n2 = 0.6666, 0.6666 * 100 = 66.6600, fit into scale 2 = 66.66).
Or n1 / (n2/100), as you'd get 2.00 / (3.00/100) is 2.00/0.03 is
66.6666, fit in scale 2 is 66.67
And yes, I agree this is something that needs getting used to, or
requires you to fiddle with order of evaluation and things)
But so far, you just keep repeating that the results don't match your
expectations, but this is well-known and has been like this for 21+
years. That in and of itself is not a reason to change it, nor for the
project to spend valuable development time to make this configurable
(and as a result increase complexity of the codebase).
I think we need a more solid, real world use case and argument for why
this would need to be added.
I find the suggestion to make this configurable an interesting one, but
this wouldn't fundamentally resolve the issue you have, it just would
change the compound error of calculation. For example, we followed your
suggestion and provide an option to use double precision semantics for
division or change the calculation so it provides half-up rounding
instead of - effectively - floor rounding), then the result of 2.00 /
3.00 would be 0.6667 (what you want), but the result of - for example -
2.00 / 3.00 * 100 would be 66.6700, and I guess you would expect it to
be 66.6667 and would prefer if the entire calculation happens in double
precision (which brings along its own host of problems, because
fundamentally double precision is not usable for exact calculations and
has its own set of precision problems and compound errors depending on
order of evaluation and actual values).
With all that said, I suggest you create a feature request ticket
("type: new feature") for making it configurable, so it can be tracked
and evaluated (and voted on by the community), but I wouldn't hold my
breath (though I'm not a Firebird core developer, which means this is
not my decision).
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
