Thread: Division by Zero
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. I have two assertions that I would like to make. 1. The result of these numeric division queries should be consistent. If one aborts, then they probably should both abort. 2. I don't think that division by zero should abort. This problem was brought to my attention by a user the was computing "Percent Profit". Profit / Net = %Profit. It is considered reasonable, in sales circles, to offer a free line item on an invoice. Thus, the calculation becomes (Profit / 0). I am suggesting that something be returned on a divide by zero. Possible return values for float types include NULL and INFINITY. An elog(NOTICE) may also be sent. Of the two possibilities NULL would be relativity easy. Simply detect the offending division, send a NOTICE, and return null. INFINITY, on the other hand, would be a bit more tricky. This may involve some platform porting issues. Plus INFINITY would have to be handled by each function that processes float numbers. Integer type functions, however, appear not to be capable of returning anything other than a legal integer. They are passed by value. I can only come up with one possibility. That would be to reserve one of the boundary values, such as MAX_INT, to represent INFINITY (or NULL for that matter) and handle the max value in each integer function. I would think, though, that on a detected divide by zero there should at least be an elog(WARN). I must resolve the problem at my site. And I would like to contribute these change, assuming they are acceptable to the other hackers. Suggestions?
Attachment
> > 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. -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
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.
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.
- Tom
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.
Attachment
> > > > ...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