On 05-08-2020 10:09, Ivan Přenosil wrote:
I want numeric values returned as LEGACY, i.e. BIGINT instead of INT128.
However SET BIND converts only NUMERIC or DECIMAL, not NUMERIC with
subtype 0.
(I tried with FB4 Beta 2 and snapshot Firebird-4.0.0.2126)
I am not sure whether this is a bug or I am just using SET BIND
incorrectly?
Here is example - first SELECT returns all columns with INT128 as base
type,
second SELECT will return BIGINTs, except columns CC and MM (they will
remain INT128).
CREATE TABLE T(
NN NUMERIC(34,2),
DD DECIMAL(34,2),
CC COMPUTED BY (NN*DD));
INSERT INTO T(NN,DD) VALUES(2,3);
COMMIT;
SET SQLDA_DISPLAY ON;
SELECT NN, DD, CC, CAST(123.456 AS NUMERIC(34,2)) AS NN2, 5.5*6.6 AS MM
FROM T;
SET BIND OF NUMERIC TO LEGACY; -- or SET BIND OF NUMERIC(34) TO LEGACY;
SELECT NN, DD, CC, CAST(123.456 AS NUMERIC(34,2)) AS NN2, 5.5*6.6 AS MM
FROM T;
If I try to execute that on 4.0.0.2126, then only NN and NN2 are mapped
to BIGINT, because contrary to the release notes, the SET BIND OF
NUMERIC TO LEGACY seems to only map NUMERIC (subtype 1). After also
executing SET BIND OF DECIMAL TO LEGACY, both DD and CC (DECIMAL,
subtype 2) are also mapped to BIGINT. Only the column MM is not mapped
because it - in my opinion incorrectly - does not have a subtype of 1 or
2 (this is - I think - a historic error that fixed precision literals
have subtype 0).
If you want to map that one as well, you will have to execute SET BIND
OF INT128 TO LEGACY (which will also cover NUMERIC and DECIMAL)
I have to say, I am a bit surprised that 5.5 * 6.6 leads to an INT128
with scale 2. This is definitely something that we either need to change
back or it needs to be explicitly documented in the release notes both
in the changes and in the compatibility notes.
Currently the release notes does say:
"""
As a side-effect of implementing the internal 128-bit integer data type,
some improvements were made to the way Firebird handles the precision
of intermediate results from calculations involving long NUMERIC and
DECIMAL data types. In prior Firebird versions, numerics backed
internally by the BIGINT data type (i.e. with precision between 10 and
18 decimal digits) were multiplied/divided using the same BIGINT data
type for the result, which could cause overflow errors due to limited
precision available. In Firebird 4, such calculations are performed
using 128-bit integers, thus reducing possibilities for unexpected
overflows.
"""
However the use of "intermediate results" does not suggest that this
affects the final data type.
On the one hand, I like this increase of precision, on the other hand I
think this will bring a lot of pain.
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
