Re: Division by zero

On Fri, 2009-07-31 at 18:27 +0100, Oliver Kohll - Mailing Lists wrote:
> Divide by zero errors have come up a couple of times on this list
> (once raised by me). I wonder if I could propose a feature for
> discussion. Could the result of a division by zero be treated as
> infinity or null, rather than raising an error? Floating point types
> already have the concept of infinity.

The SQL spec seems to require that an exception be raised in 6.26
General Rules 4.

(Disclaimer: I'm not an expert on the SQL standard. This was based on a
quick text search of the document).

> I'd have thought that there's no reason why a /0 in one row
> necessarily has to be fatal for the whole view. In many cases, you can
> imagine that returning infinity makes more sense. Strictly, I suppose,
> 1/0 should return infinity, 0/0 null and -1/0 negative infinity.
> Alternatively, all could return NaN. At least there could be a
> configuration option to turn on this behaviour.

For every situation in which coming up with some kind of result "makes
sense", there is another situation in which the result would be wrong or
hides some subtle logical flaw in the query. For instance:

"Find all store locations which have not achieved an average sale price
of $100."

SELECT store_name FROM sales WHERE totalsales/nsales < 100;

The person running that query might be missing stores with no sales at
all, and they might prefer an ERROR to the silent omission of results.

> The concern stems from the fact that when a divide by zero occurs in a
> view, no rows at all are returned, just the error message. This makes
> it very difficult to work out where the problem value is, compared to
> other tools like spreadsheets, which return a cell error. A view can
> be very fragile. Further, the Postgres error doesn't give any details
> of the field and of course can't point to the row, it just says
> ERROR: division by zero

I agree that it may be useful to show you the values that are causing
the expression to be throwing an error. However, we shouldn't say that
it's a problem with the field itself -- it may be a problem with the
query.

> I know you can use CASE and NULLIF but if you have complex
> calculations, that makes them a lot less readable.

Yes -- you can do what you want anyway. If you want it to be more
readable, you can redefine the division operator yourself -- it's just a
function and operator that happens to be named "/" (although that might
not be wise). Maybe you can call it "//" to avoid confusion with people
used to the SQL standard behavior.

Regards,
Jeff Davis