Re: Infinity vs Error for division by zero - Mailing list pgsql-hackers
| From | Matt Pulver |
|---|---|
| Subject | Re: Infinity vs Error for division by zero |
| Date | |
| Msg-id | CAHiCE4X-e10Xie08NZ4NApWUWYBVEywMc8so5gy3wU4UNNVP8A@mail.gmail.com Whole thread Raw |
| In response to | Re: Infinity vs Error for division by zero ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Responses |
Re: Infinity vs Error for division by zero
|
| List | pgsql-hackers |
On Fri, Mar 1, 2019 at 12:59 PM Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>>>>> "Matt" == Matt Pulver <mpulver@unitytechgroup.com> writes:
Matt> ERROR: division by zero
Matt> Question: If Infinity and NaN are supported, then why throw an
Matt> exception here, instead of returning Infinity?
Spec says so:
4) The dyadic arithmetic operators <plus sign>, <minus sign>,
<asterisk>, and <solidus> (+, -, *, and /, respectively) specify
addition, subtraction, multiplication, and division, respectively.
If the value of a divisor is zero, then an exception condition is
raised: data exception -- division by zero.
Thank you, that is what I was looking for. In case anyone else is looking for source documentation on the standard, there is a link from https://en.wikipedia.org/wiki/SQL:2003#Documentation_availability to a zip file of the SQL 2003 draft http://www.wiscorp.com/sql_2003_standard.zip where one can confirm this (page 242 of 5WD-02-Foundation-2003-09.pdf).
On Fri, Mar 1, 2019 at 2:26 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
But if someone wanted to write a user-defined division function or
operator that would return Inf for (anything > 0) / 0 and for
(anything < 0) / -0, and -Inf for (anything < 0) / 0 and for
(anything > 0) / -0, and NaN for (either zero) / (either zero), I think
that function or operator would be fully in keeping with IEEE 754.Upon further reading you are correct - IEEE 754 has chosen to treat n/0 differently for n=0 and n<>0 cases. I'm sure they have their reasons but within the scope of this database, and the core arithmetic functions it provides, those distinctions don't seeming meaningful and having to add query logic to deal with both cases would just be annoying. I don't use, or have time for the distraction, to understand why such a decision was made and how it could be useful. Going from an exception to NaN makes sense to me, going instead to infinity - outside of limit expressions which aren't applicable here - does not.For my part in the queries I have that encounter divide-by-zero I end up transforming the result to zero which is considerably easier to present/absorb along side other valid fractions in a table or chart.
In heavy financial/scientific calculations with tables of data, using inf and nan are very useful, much more so than alternatives such as throwing an exception (which row(s) included the error?), or replacing them with NULL or 0. There are many intermediate values where using inf makes sense and results in finite outcomes at the appropriate limit: atan(1.0/0)=pi/2, erf(1.0/0)=1, exp(-1.0/0)=0, etc.
In contrast, nan represents a mathematically indeterminate form, in which the appropriate limit could not be ascertained. E.g. 0.0/0, inf-inf, 0.0*inf, etc. 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.)
Best regards,
Matt
pgsql-hackers by date: