DOUBLE PRECISION: values near 2.2250738585072009e-308 (greater or equal to this) must be stored w/o truncating to zero or raising exception

[pavel-zotov]

According to IEEE_754 and its explanation, double precision must support storage of ~2.2250738585072009e-308 ("largest subnormal number") which is repsesented as: [bin] 0 00000000000 1111111111111111111111111111111111111111111111111111

But this is not so in FB 4.x ... 6.x for value 2.2250738585072013e-308 which is greater than above mentioned limit. Consider script:

set sqlda_display on;
select
    2.2250738585072014e-308 as near_zero_1 ---------------- [ 1 ]
from rdb$database;

select 2.2250738585072013e-308 as near_zero_2 ------------ [ 2 ]
from rdb$database;

select cast('2.2250738585072013e-308' as double precision) as near_zero_3 ---- [ 3 ]
from rdb$database;

Statement "[ 1 ]" will pass OK. Statement "[ 2 ]" will also pass but shows

01: sqltype: 480 DOUBLE scale: 0 subtype: 0 len: 8
  :  name: CONSTANT  alias: NEAR_ZERO_2
  : table:   schema:   owner:

NEAR_ZERO_2                     0.000000000000000

Statement "[ 3 ]" raises exception:

01: sqltype: 480 DOUBLE scale: 0 subtype: 0 len: 8
  :  name: CAST  alias: NEAR_ZERO_3
  : table:   schema:   owner:

Statement failed, SQLSTATE = 22003
arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range

IMO, attempt to store values [ 2.2250738585072009e-308 ... 2.2250738585072013e-308 ] (including bounds) must cause them be truncated to zero nor raise exception.

Checked on 4.0.6.3214 ; 5.0.3.1668 ; 6.0.0.949

[mrotteveel]

must cause them be truncated to zero nor raise exception.

Did you mean "must not cause them to be truncated to zero nor raise exception."

[pavel-zotov]

Yes, of course must not. Sorry for the inaccuracy.

[pavel-zotov]

It seems that 'value is out of range' raises when we have any kind of cast (implicit or explicit) for a string that represents any value from half-intervals: [ -2.999999999999999e-308 ... 0) and (0 ... 2.999999999999999e-308 ] (i.e. excluding -0 and +0)

SQL> insert into test values('-3e-308') returning cast(x as double precision);
-3.000000000000000e-308

SQL> insert into test values('-2.999999999e-308') returning cast(x as double precision);
Statement failed, SQLSTATE = 22003
arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range

SQL> insert into test values('3e-308') returning cast(x as double precision);
 3.000000000000000e-308

SQL> insert into test values('2.9999999999e-308') returning cast(x as double precision);
Statement failed, SQLSTATE = 22003
arithmetic exception, numeric overflow, or string truncation
-numeric value is out of range

SQL> insert into test values('-0e-308') returning cast(x as double precision);
     -0.000000000000000

SQL> insert into test values('0e-308') returning cast(x as double precision);
      0.000000000000000