Firebird 4 Release, sum() numeric(18,2) = RESULT NUMERIC(38,2) ?

[Antônio Gomes]

sum numeric fields with double precision result,...

See the table:

CREATE TABLE TESTE (

    CODIGO     INTEIRO_NOTNULL NOT NULL /* INTEIRO_NOTNULL = INTEGER DEFAULT 0 NOT NULL */,

    DATA       DATA /* DATA = DATE CHECK ((VALUE IS NULL) OR (VALUE >='01/01/1900')) */,

    HORA       HORA /* HORA = TIME */,

    VALOR15    NUM15_2 /* NUM15_2 = NUMERIC(15,2) DEFAULT 0 NOT NULL */,

    VALOR18    NUM18_2 /* NUM18_2 = NUMERIC(18,2) DEFAULT 0 NOT NULL */,

    VALORDEC6  NUM18_6 /* NUM18_6 = NUMERIC(18,6) DEFAULT 0 NOT NULL */,

    VALOR10    NUM10_2 NOT NULL /* NUM10_2 = NUMERIC(10,2) DEFAULT 0 NOT NULL */,

    VALOR38    NUM38_2 NOT NULL /* NUM38_2 = DECFLOAT(34) DEFAULT 0 NOT NULL */,

    NUM_18_2   NUMERIC(18,2) DEFAULT 0 NOT NULL

);

/******************************************************************************/

/*                                Primary keys                                */

/******************************************************************************/

ALTER TABLE TESTE ADD CONSTRAINT RDB$PRIMARY_TESTE PRIMARY KEY (CODIGO);

--- INSERT

UPDATE OR INSERT INTO TESTE (CODIGO, DATA, HORA, VALOR15, VALOR18, VALORDEC6, VALOR10, VALOR38, NUM_18_2) VALUES (1, '2021-06-03', '01:00:00', 150.52, 160.65, 100, 150, 100.55115, 300.25) MATCHING (CODIGO);

UPDATE OR INSERT INTO TESTE (CODIGO, DATA, HORA, VALOR15, VALOR18, VALORDEC6, VALOR10, VALOR38, NUM_18_2) VALUES (2, '2021-06-04', NULL, 150, 65, 98, 300, 100.365521, 200) MATCHING (CODIGO);

UPDATE OR INSERT INTO TESTE (CODIGO, DATA, HORA, VALOR15, VALOR18, VALORDEC6, VALOR10, VALOR38, NUM_18_2) VALUES (3, '2021-06-05', NULL, 450, 600, 10000, 400.33, 1234567890123459781.4541, 150) MATCHING (CODIGO);

UPDATE OR INSERT INTO TESTE (CODIGO, DATA, HORA, VALOR15, VALOR18, VALORDEC6, VALOR10, VALOR38, NUM_18_2) VALUES (4, '2021-06-05', NULL, 15000, 16544.54, 12345.123457, 1540.55, 123456789012345678901234567.1254190, 150) MATCHING (CODIGO);

COMMIT WORK;

--- SELECT

SELECT SUM(TESTE.VALOR15)   N15_2,

       SUM(TESTE.VALOR18)   N18_2,

       SUM(TESTE.VALORDEC6) N18_6,

       SUM(TESTE.VALOR10)   N10_2,

       SUM(TESTE.VALOR38)   dec_float34  ,

       SUM(TESTE.NUM_18_2)  NUM_SEM_DOMINIO

FROM TESTE

RESULT: 

for field TESTE.valor15, field type is numeric(38,2) wrong...

for field TESTE.VALOR38, result field type  is ok.

is there any configuration, which can be done, to be compatible with firebird 2.5?

obrigado,.

[Dmitrii Kuzmenko]

Hello!

 

Please read

https://ib-aid.com/download/docs/fb4migrationguide.pdf

 

03.06.2021, 18:24, "Antônio Gomes" <acgu...@gmail.com>:

is there any configuration, which can be done, to be compatible with firebird 2.5?

-- 

 

Sincerely,

Dmitry Kuzmenko

[Mark Rotteveel]

Your question would have been more suitable for firebird-support than
for firebird-general

On 03-06-2021 15:31, Antônio Gomes wrote:
> sum numeric fields with double precision result,...

There is no double precision result anywhere in your example, please
clarify the problem.

> RESULT:
> for field TESTE.valor15, field type is numeric(38,2) wrong...
> for field TESTE.VALOR38, result field type  is ok.
>

> firebird4.png

This is not "wrong", this is the expected result in Firebird 4.0.
However, this doesn't seem to have been documented in the release notes.

Summing values of NUMERIC/DECIMAL that are backed by a BIGINT will
result in a NUMERIC/DECIMAL backed by an INT128 (that is, precision 38).

> is there any configuration, which can be done, to be compatible with
> firebird 2.5?

You can set the DataTypeCompatibility option of firebird.conf (or for
the specific database in databases.conf), see
https://www.firebirdsql.org/file/documentation/release_notes/html/en/4_0/rlsnotes40.html#rnfb40-config-datatypecompat

Mark
--
Mark Rotteveel