SET BIND OF DECFLOAT TO LEGACY does not always work

[Virgo Pärna]

It is my understanding, that after executing
SET BIND OF DECFLOAT TO LEGACY
in connection query results should have double precision columns instead
of decfloat columns.
But I run into case, where I will get DECFLOAT (sqltype 32752) results
even after this.

Specific queries do SUM(integerfield)/1000.000 or
MAX(integerfield*4)/1000.000 calculations...
But even SELECT 1/1000.000 FROM RDB$DATABASE seems to have same issue.



--
Virgo Pärna
Gaiasoft OÜ
vi...@gaiasoft.ee

[Virgo Pärna]

On 06.10.2025 14:50, Virgo Pärna wrote:
>     It is my understanding, that after executing
> SET BIND OF DECFLOAT TO LEGACY
> in connection query results should have double precision columns instead
> of decfloat columns.
> But I run into case, where I will get DECFLOAT (sqltype 32752) results
> even after this.
>
>     Specific queries do SUM(integerfield)/1000.000 or
> MAX(integerfield*4)/1000.000 calculations...
>     But even SELECT 1/1000.000 FROM RDB$DATABASE seems to have same issue.
>

Figured it out... 32752 is INT128, not DECFLOAT. I was missing SET BIND
OF INT128 TO LEGACY. Haviung division made me think of decfloat.

--
Virgo Pärna
vi...@gaiasoft.ee

[Mark Rotteveel]

On 06/10/2025 13:50, Virgo Pärna wrote:
>     It is my understanding, that after executing
> SET BIND OF DECFLOAT TO LEGACY
> in connection query results should have double precision columns instead
> of decfloat columns.
> But I run into case, where I will get DECFLOAT (sqltype 32752) results
> even after this.

It is actually a NUMERIC(38, s) (which is backed by an INT128, type code
32752).

>     Specific queries do SUM(integerfield)/1000.000 or
> MAX(integerfield*4)/1000.000 calculations...
>     But even SELECT 1/1000.000 FROM RDB$DATABASE seems to have same issue.

And that is expected. 1/1000.000 is a calculation with between an
INTEGER literal a NUMERIC(18,3) literal, and thus the result is a
NUMERIC(38, s), where s is calculated as the sum of the scale of the
numerator and the denominator (i.e. s = 0 + 3 = 3, so NUMERIC(38, 3).

In Firebird 3.0 and older, that calculation would have resulted in a
NUMERIC(18, 3) as that was the biggest type available then.

Similarly, SUM(integerfield)/1000.000 and calculations between a BIGINT
and a NUMERIC(18,3), resulting in a NUMERIC(38,3).

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 07/10/2025 11:44, 'Mark Rotteveel' via firebird-general wrote:
>
> Similarly, SUM(integerfield)/1000.000 and calculations between a BIGINT
> and a NUMERIC(18,3), resulting in a NUMERIC(38,3).

That should have been:

Similarly, SUM(integerfield)/1000.000 and MAX(integerfield*4)/1000.000
are calculations ....

Mark
--
Mark Rotteveel