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
"Whether to round or truncate when performing division is
implementation-defined." (c) SQL spec ;-)
However, being compliant does not necessarily mean being useful, the
standard leaves too many gaps here.
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
This is what PostgreSQL does. They use integral division only if both
arguments are integers (not numerics!). However, this is not very
confusing for them, because their "implementation-defined scale" is
always the maximum possible (instead of our scaleA+scaleB), so they get:
select 14::bigint / 3::bigint = 4
select 14::numeric(18, 0) / 3::numeric(18, 0) = 4.666666666666667
the latter may produce 5 when casted to BIGINT explicitly, but from
another side it will produce 4.67 when casted to NUMERIC(18, 2).
Dmitry
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
