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

Thomas G. Lockhart wrote:

> Bruce Momjian wrote:
> >
> > >
> > > This is a multi-part message in MIME format.
> > > --------------0BE30DC54DE04265764E3F7C
> > > Content-Type: text/plain; charset=us-ascii
> > > Content-Transfer-Encoding: 7bit
> > >
> > > I have noticed that when float types are divided by zero in a query, the
> > > the query aborts (via elog(WARN)) with a complaint about divide by zero.
> > >
> > > Also an integer divide by zero produces a result.  On our AIX 4.1.4
> > > system 1 / 0 = 15.    And 10 / 0 = 31.   There is some pattern here with
> > > integers, but it is of little use.
> >
> > On BSDI:
> >
> > test=> select 1/0;
> > ERROR:  floating point exception! The last floating point operation
> > either exceeded legal ranges or was a divide by zero
> >
> > I think a transaction abort is the only normal solution.
>
> I get the same behavior on my Linux box, so at least we have consistant
> behavior across some platforms! David, 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.
>

I have since, discovered the that our compiler does not trap divide by zero
unless we provide an extra compile option.  Rats.   I did not realize that such
an option even existed.   I have not recompiled the backend with the options
turned on, but, I suspect this explains why Bruce gets the exception and I/we
don't.

> If it is a simple matter of throwing an exception and catching it, then
> perhaps 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...
>
> tgl=> select 2000000000*2;
> ----------
> -294967296
> (1 row)
>
> 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.   Nor, am
not sure what normal behavior is in this instance.   From MS Access, SQL Server
returns NULL in the offending column of the result;  From the monitor, 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.

This issue is obviously larger than my 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.