Re: [HACKERS] Division by Zero - Mailing list pgsql-hackers

> > > > ...float types are divided by zero in a query, the
> > > > the query aborts (via elog(WARN))...
> > > > Also an integer divide by zero produces a result...
> > > I think a transaction abort is the only normal solution.
> > ... if you want to find out what it
> > takes to change the floating point exception handling to allow
> > divide-by-zero and to have integer overflows caught be an exception
> > handler, then we can discuss what the default behavior should be.
> ... our compiler does not trap divide by zero
> unless we provide an extra compile option. I did not realize that such
> an option even existed.

Yes, this is typical.

> > If it is a simple matter of throwing an exception and catching it,
> > then we can make it a compile-time or run-time option. With IEEE
> > arithmetic, infinity results for floats are possible. I don't really
> > like uncaught integer overflows which is what we have now...
> > Don't know where else integer overflows might be used in the
> > backend, so we would have to do extensive testing.
> I don't know if the SQL standard addresses division by zero or not.
> From MS Access, SQL Server
> returns NULL in the offending column of the result; ... Personal
> Oracle throws an exception with no result.  I'm no fan of MS, but I am
> partial to their solution to this problem. (Because it solves my
> problem.)
> Most fortunately, PostgreSQL allows me to rewrite these division
> function for my own solution.  PostgreSQL is good.  Now, if I can only
> get the NULL return value to propagate to the result set.

That has been an outstanding issue for a long time; the claim is that it
should be fairly easy to do since some hooks for this are already in the
backend. Look in the archives for some hints on where to look which were
posted a month or two ago.

The MS Access solution is bad in general, Oracle's is better. As you
point out, you can modify the behavior of the divide operator in your
installation by replacing the appropriate function with your own.

NULL is not the same as infinity; it means "unspecified" or "don't
know". We shouldn't hide divide-by-zero in NULL returns.

> This issue is obviously larger than my (float / 0.0) problem.
> At a minimum though, I must provide a solution for my site. I  wish to
> make my solution available.  However,  I am new to this list, and as
> such will adhere to the advice of its core activist.

Well, you might get differing opinions, but...

There are three issues:

1) Allowing functions to return NULL would be very nice, though not for
default behavior of divide-by-zero.

2) Throwing an error on an integer divide-by-zero on every platform
should be the default behavior. There are a few (well, at least one :)
active participants in Postgres development running on AIX; perhaps you
should work together on the right combination of compiler flags for all
versions of AIX (they have big library variations, don't know about the
compiler).

3) Allowing "Inf" results for floating point divide-by-zero could be an
installation option. SQL does not take advantage of all features of IEEE
arithmetic. However, note that a few of our supported platforms do not
use IEEE arithmetic (e.g. VAX), so we should have this as an option
only.

Have fun with it...

                         - Tom