Re: Division by zero

Hello,

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.

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.

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

There may well be good reasons for not treating this. I've come across
comments such as 'I think everybody would agree that this would be a
bad thing to do!' but remain to be convinced.

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

Regards
Oliver Kohll

oliver(at)gtwm(dot)co(dot)uk / 0845 456 1810 / 07814 828608
www.gtwm.co.uk - company
www.gtportalbase.com - product

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

On 31 Jul 2009, at 19:49, Jeff Davis wrote:

> 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.

Great Idea, that's a very powerful feature, being able to redefine an
operator. I did that as you suggest and it seems to work fine. My
users access postgres through a web app layer so I modified the
application to replace any cases of / with // in calculations as
they're created.

In case there are any improvements to suggest and for the benefit of
anyone else who wants to swallow division by zero, the function and
operator are below. I only use integer and double precision numbers. I
assume that using the more general 'numeric' rather than all
combinations of these would have a performance penalty?

Regards
Oliver Kohll

oliver(at)gtwm(dot)co(dot)uk / 0845 456 1810 / 07814 828608
www.gtwm.co.uk - company
www.gtportalbase.com - product

---

-- This routine creates an alterantive division operator
-- that doesn't throw an error on a divide by zero
-- but rather returns null

CREATE OR REPLACE FUNCTION gtpb_divide(integer, integer) RETURNS integer
AS 'SELECT $1 / NULLIF($2,0);'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION gtpb_divide(double precision, double
precision) RETURNS double precision
AS 'SELECT $1 / NULLIF($2,0);'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION gtpb_divide(double precision, integer)
RETURNS double precision
AS 'SELECT $1 / NULLIF($2,0);'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION gtpb_divide(integer, double precision)
RETURNS double precision
AS 'SELECT $1 / NULLIF($2,0);'
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

CREATE OPERATOR // (
PROCEDURE = gtpb_divide,
LEFTARG = integer,
RIGHTARG = integer
);

CREATE OPERATOR // (
PROCEDURE = gtpb_divide,
LEFTARG = double precision,
RIGHTARG = double precision
);

CREATE OPERATOR // (
PROCEDURE = gtpb_divide,
LEFTARG = double precision,
RIGHTARG = integer
);

CREATE OPERATOR // (
PROCEDURE = gtpb_divide,
LEFTARG = integer,
RIGHTARG = double precision
);

On Sun, Aug 02, 2009 at 12:08:28PM +0100, Oliver Kohll - Mailing Lists wrote:
> -- This routine creates an alterantive division operator
> -- that doesn't throw an error on a divide by zero
> -- but rather returns null
>
> CREATE OR REPLACE FUNCTION gtpb_divide(integer, integer) RETURNS integer
> AS 'SELECT $1 / NULLIF($2,0);'
> LANGUAGE SQL
> IMMUTABLE
> RETURNS NULL ON NULL INPUT;

If I were you I'd remove the "RETURNS NULL ON NULL INPUT" flag. I used
to think of it as just a "hint" to the planner as to its behavior,
but it turns out that it's interpreted much more strongly by PG. The
interpretation means that the function doesn't end up getting be inlined
where I'd expect it to be and hence the optimizer doesn't get as much
freedom to rewrite your queries as you may want.

Admittedly it's going to be less of an issue with division that other
operators, but it's worth bearing in mind. The "IMMUTABLE" options is a
good one to specify though, keep that!

--
Sam http://samason.me.uk/

2009/8/2 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> On Sun, Aug 02, 2009 at 12:08:28PM +0100, Oliver Kohll - Mailing Lists wrote:
>> -- This routine creates an alterantive division operator
>> -- that doesn't throw an error on a divide by zero
>> -- but rather returns null
>>
>> CREATE OR REPLACE FUNCTION gtpb_divide(integer, integer) RETURNS integer
>> AS 'SELECT $1 / NULLIF($2,0);'
>> LANGUAGE SQL
>> IMMUTABLE
>> RETURNS NULL ON NULL INPUT;
>
> If I were you I'd remove the "RETURNS NULL ON NULL INPUT" flag.  I used
> to think of it as just a "hint" to the planner as to its behavior,
> but it turns out that it's interpreted much more strongly by PG.  The
> interpretation means that the function doesn't end up getting be inlined
> where I'd expect it to be and hence the optimizer doesn't get as much
> freedom to rewrite your queries as you may want.
>

I thing, it's not true - RETURNS NULL ON NULL INPUT is equal to STRICT
flag, and it means, don't run function, when any param is null. For
optimalisator it means only one - when any parameter is constant NULL,
then function evaluation should be replaced by NULL. But not too much
often optimalizer should detect this case, so this is shortcut for
evaluator. This flag doesn't change inlining.

> Admittedly it's going to be less of an issue with division that other
> operators, but it's worth bearing in mind.  The "IMMUTABLE" options is a
> good one to specify though, keep that!
>

There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug
> --
>  Sam  http://samason.me.uk/
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote:
> 2009/8/2 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> > On Sun, Aug 02, 2009 at 12:08:28PM +0100, Oliver Kohll - Mailing Lists wrote:
> >> CREATE OR REPLACE FUNCTION gtpb_divide(integer, integer) RETURNS integer
> >> AS 'SELECT $1 / NULLIF($2,0);'
> >> LANGUAGE SQL
> >> IMMUTABLE
> >> RETURNS NULL ON NULL INPUT;
> >
> > If I were you I'd remove the "RETURNS NULL ON NULL INPUT" flag. I used
> > to think of it as just a "hint" to the planner as to its behavior,
> > but it turns out that it's interpreted much more strongly by PG. The
> > interpretation means that the function doesn't end up getting be inlined
> > where I'd expect it to be and hence the optimizer doesn't get as much
> > freedom to rewrite your queries as you may want.
>
> I thing, it's not true - RETURNS NULL ON NULL INPUT is equal to STRICT
> flag, and it means, don't run function, when any param is null.

Yes, this is how PG interprets it.

> For
> optimalisator it means only one - when any parameter is constant NULL,
> then function evaluation should be replaced by NULL. But not too much
> often optimalizer should detect this case, so this is shortcut for
> evaluator. This flag doesn't change inlining.

No, not unless things have changed since this discussion:

http://archives.postgresql.org/message-id/20090604090045.GR5407@samason.me.uk

> > Admittedly it's going to be less of an issue with division that other
> > operators, but it's worth bearing in mind. The "IMMUTABLE" options is a
> > good one to specify though, keep that!
>
> There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug

Not in any tests I've done.

--
Sam http://samason.me.uk/

2009/8/2 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote:
>> 2009/8/2 Sam Mason <sam(at)samason(dot)me(dot)uk>:
>> > On Sun, Aug 02, 2009 at 12:08:28PM +0100, Oliver Kohll - Mailing Lists wrote:
>> >> CREATE OR REPLACE FUNCTION gtpb_divide(integer, integer) RETURNS integer
>> >> AS 'SELECT $1 / NULLIF($2,0);'
>> >> LANGUAGE SQL
>> >> IMMUTABLE
>> >> RETURNS NULL ON NULL INPUT;
>> >
>> > If I were you I'd remove the "RETURNS NULL ON NULL INPUT" flag. I used
>> > to think of it as just a "hint" to the planner as to its behavior,
>> > but it turns out that it's interpreted much more strongly by PG. The
>> > interpretation means that the function doesn't end up getting be inlined
>> > where I'd expect it to be and hence the optimizer doesn't get as much
>> > freedom to rewrite your queries as you may want.
>>
>> I thing, it's not true - RETURNS NULL ON NULL INPUT is equal to STRICT
>> flag, and it means, don't run function, when any param is null.
>
> Yes, this is how PG interprets it.
>
>> For
>> optimalisator it means only one - when any parameter is constant NULL,
>> then function evaluation should be replaced by NULL. But not too much
>> often optimalizer should detect this case, so this is shortcut for
>> evaluator.  This flag doesn't change inlining.
>
> No, not unless things have changed since this discussion:
>
>  http://archives.postgresql.org/message-id/20090604090045.GR5407@samason.me.uk
>
>> > Admittedly it's going to be less of an issue with division that other
>> > operators, but it's worth bearing in mind.  The "IMMUTABLE" options is a
>> > good one to specify though, keep that!
>>
>> There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug
>
> Not in any tests I've done.

I did it - and in this case immutable is wrong and strict not. It's an
new for me, because I used rules that are well only for plpgsql or C
language. What I see now, the rules for sql are totally different.

Pavel

>
> --
>  Sam  http://samason.me.uk/
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Sun, Aug 02, 2009 at 05:22:45PM +0200, Pavel Stehule wrote:
> 2009/8/2 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> > On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote:
> >> There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug
> >
> > Not in any tests I've done.
>
> I did it - and in this case immutable is wrong and strict not.

I'm not sure what you're responding to here, but I'm pretty sure the OP
wants IMMUTABLE and does not want STRICT/RETURNS NULL ON NULL INPUT.

> It's an
> new for me, because I used rules that are well only for plpgsql or C
> language. What I see now, the rules for sql are totally different.

SQL language functions are going to be different from anything else
because the can be. The planner has intimate knowledge of SQL and hence
will try hard to expand these out and optimize them (in a similar way to
how it handles views).

The semantics of these keywords shouldn't change between SQL, plpgsql
and C functions though, it's just that the optimizer can look inside an
SQL function and not other functions.

Maybe if you can say what you did and what result you got back?

--
Sam http://samason.me.uk/

2009/8/2 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> On Sun, Aug 02, 2009 at 05:22:45PM +0200, Pavel Stehule wrote:
>> 2009/8/2 Sam Mason <sam(at)samason(dot)me(dot)uk>:
>> > On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote:
>> >> There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug
>> >
>> > Not in any tests I've done.
>>
>> I did it - and in this case immutable is wrong and strict not.
>
> I'm not sure what you're responding to here, but I'm pretty sure the OP
> wants IMMUTABLE and does not want STRICT/RETURNS NULL ON NULL INPUT.
>

I checked if function was inlined or not. When I mark function as
strict then it was inlined. When I mark function as IMMUTABLE then it
wasn't inlined. That's all - you can check it too.

>> It's an
>> new for me, because I used rules that are well only for plpgsql or C
>> language. What I see now, the rules for sql are totally different.
>
> SQL language functions are going to be different from anything else
> because the can be.  The planner has intimate knowledge of SQL and hence
> will try hard to expand these out and optimize them (in a similar way to
> how it handles views).
>
> The semantics of these keywords shouldn't change between SQL, plpgsql
> and C functions though, it's just that the optimizer can look inside an
> SQL function and not other functions.
>
> Maybe if you can say what you did and what result you got back?
>
> --
>  Sam  http://samason.me.uk/
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Sun, Aug 02, 2009 at 06:03:11PM +0200, Pavel Stehule wrote:
> 2009/8/2 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> > On Sun, Aug 02, 2009 at 05:22:45PM +0200, Pavel Stehule wrote:
> >> 2009/8/2 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> >> > On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote:
> >> >> There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug
> >> >
> >> > Not in any tests I've done.
> >>
> >> I did it - and in this case immutable is wrong and strict not.
> >
> > I'm not sure what you're responding to here, but I'm pretty sure the OP
> > wants IMMUTABLE and does not want STRICT/RETURNS NULL ON NULL INPUT.
>
> I checked if function was inlined or not. When I mark function as
> strict then it was inlined. When I mark function as IMMUTABLE then it
> wasn't inlined. That's all - you can check it too.

I will be checking different things, please say what you're testing.

Different things are inlined in different places, its the different
places things get inlined that cause the optimizer to do different
things.

--
Sam http://samason.me.uk/

2009/8/2 Sam Mason <sam(at)samason(dot)me(dot)uk>:
> On Sun, Aug 02, 2009 at 06:03:11PM +0200, Pavel Stehule wrote:
>> 2009/8/2 Sam Mason <sam(at)samason(dot)me(dot)uk>:
>> > On Sun, Aug 02, 2009 at 05:22:45PM +0200, Pavel Stehule wrote:
>> >> 2009/8/2 Sam Mason <sam(at)samason(dot)me(dot)uk>:
>> >> > On Sun, Aug 02, 2009 at 02:20:18PM +0200, Pavel Stehule wrote:
>> >> >> There is paradox - IMMUTABLE function break inlinig :(. There is maybe bug
>> >> >
>> >> > Not in any tests I've done.
>> >>
>> >> I did it - and in this case immutable is wrong and strict not.
>> >
>> > I'm not sure what you're responding to here, but I'm pretty sure the OP
>> > wants IMMUTABLE and does not want STRICT/RETURNS NULL ON NULL INPUT.
>>
>> I checked if function was inlined or not. When I mark function as
>> strict then it was inlined. When I mark function as IMMUTABLE then it
>> wasn't inlined. That's all - you can check it too.
>
> I will be checking different things, please say what you're testing.
>

look on thread "IMMUTABLE break inlining simple SQL functions."

Pavel

> Different things are inlined in different places, its the different
> places things get inlined that cause the optimizer to do different
> things.
>
> --
>  Sam  http://samason.me.uk/
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Sam Mason <sam(at)samason(dot)me(dot)uk> writes:
> I'm not sure what you're responding to here, but I'm pretty sure the OP
> wants IMMUTABLE and does not want STRICT/RETURNS NULL ON NULL INPUT.

Yeah --- STRICT will prevent inlining. The function's expression
actually is strict, but the planner isn't smart enough about NULLIF
to realize that, so it doesn't inline.

regards, tom lane