Does Firebird support Infinity as FLOAT value?

Question

Firebird supports the special floating point value Infinity and -Infinity as DOUBLE PRECISION values:

select log(1, 1), log(1, 0.5), log(1, 1.5) from RDB$DATABASE

This yields

|LOG|LOG      |LOG      |
|---|---------|---------|
|NaN|-Infinity|+Infinity|

The trick using LOG() with base 1 is documented here. While NaN also exists as a FLOAT value:

select cast(log(1, 1) as float) from RDB$DATABASE

The infinities can't be cast to FLOAT:

select cast(log(1, 1.5) as float) from RDB$DATABASE

I'm getting:

SQL Error [335544916] [22003]: arithmetic exception, numeric overflow, or string truncation; numeric value is out of range [SQLState:22003, ISC error code:335544916]

Is there any documented or undocumented way to get infinities of type FLOAT in Firebird?

Answer 1

The answer is, it's complicated. Although Firebird's floating point types can hold/store NaN and Infinity in some cases, it doesn't support Infinity in casts to FLOAT (and as a result, neither for assignment conversions), because the cast to float performs a range-check, and -Infinity and +Infinity is out of range.

I'm not sure if that is intentional or not, but it probably is given how CAST is specified in the standard (though the behaviour for NaN is then questionable). In fact, I'm not even sure the support for NaN and Infinity is intentional behaviour, or just a side-effect of the underlying implementation supporting NaN and Infinity.

For example, it is possible to store infinity:

public static void main(String[] args) throws SQLException {
    try (var connection = DefaultDb.createDefaultConnection();
         var pstmt = connection.prepareStatement("insert into withfloat(floatval) values (?)")) {
        pstmt.setFloat(1, Float.POSITIVE_INFINITY);
        pstmt.execute();
    }
}

However, as far as I'm aware, there is no way to produce these values in DSQL itself.

I would recommend reporting this as a bug on http://tracker.firebirdsql.org/browse/CORE, but I'm not sure whether the bug is that it is possible to produce and store NaN/Infinity at all, or the behaviour of CAST. And I'm afraid fixing either is also problematic: disallowing is backwards incompatible, while allowing the CAST would violate the standard.

For the record, the SQL standard does not specify anything about NaN and Infinity for floating point types, but the rest of the specification make clear that NaN and Infinity should not be supported. For example, for LOG it specifies:

11. If <general logarithm function> is specified, then let VB be the value of the <general logarithm base> and let VA be the value of the <general logarithm argument>.
    Case:
    a) If at least one of VA and VB is the null value, then the result is the null value.
    b) If VA is negative or 0 (zero), then an exception condition is raised: data exception — numeric value out of range.
    c) If VB is negative, 0 (zero), or 1 (one), then an exception condition is raised: data exception — numeric value out of range._ d) Otherwise, the result is the logarithm with base VB of VA.

In other words LOG(1, _) should raise a data exception — numeric value out of range instead of producing NaN or +/-Infinity.