Re: Infinity vs Error for division by zero

On Fri, Mar 1, 2019 at 4:51 PM Chapman Flack <chap(at)anastigmatix(dot)net> wrote:

> On 3/1/19 3:49 PM, Matt Pulver wrote:
>
> > In many applications, I would much rather see calculations carried out
> > via IEEE 754 all the way to the end, with nans and infs, which
> > provides much more useful diagnostic information than an exception that
> > doesn't return any rows at all. As Andres Freund pointed out, it is also
> > more expensive to do the intermediate checks. Just let IEEE 754 do its
> > thing! (More directed at the SQL standard than to PostgreSQL.)
>
> I wanted to try this out a little before assuming it would work,
> and there seems to be no trouble creating a trivial domain over
> float8 (say, CREATE DOMAIN ieeedouble AS float8), and then creating
> operators whose operand types are the domain type.
>
> So it seems an extension could easily do that, and supply happily
> inf-returning and NaN-returning versions of the operators and
> functions, and those will be used whenever operands have the domain
> type.
>
> It might even be useful and relatively elegant, while leaving the
> SQL-specified base types to have the SQL-specified behavior.
>

That would be very useful. I've been wanting this for years, and I'm sure
the data users I work with will appreciate it (but don't directly
understand this to be the solution).

There are issues relating to ordering and aggregation that perhaps are
already transparent to you, but I'll mention anyway for the record.
Conceptually, there would be different contexts of ordering:

1. When writing mathematical functions, <, =, and > are all false when
comparing to NaN (NaN != NaN is true.)
2. In SQL when sorting or aggregating, NaN=NaN. Consider that there are
2^53-2 different double precision representations of NaN at the bit level.
Under the same floating point ordering logic used for finite numbers, when
applied to inf and nan, we get the following ordering: -nan < -inf < (all
finite numbers) < inf < nan. When the bit patterns are taken into
consideration, an efficient sort algorithm can be implemented. (Forgive me
for stating the obvious, but just mentioning this for whoever is going to
take this on.)

I would be most interested to hear of and discuss any other unforeseen
complications or side-effects.

Best regards,
Matt