On Wed, May 20, 2015 at 2:05 PM, Anthony Damico <ajdamico@gmail.com> wrote:
> i'm just curious/thinking out loud if anything can be done to prevent
> division by zero from de-railing a huge query?  unlike most error messages,
> division by zero destroys a query *after* the bulk of the processing occurs
> - so you sit and wait and the command still fails, despite all the time and
> cpu revving.
>
> i assume it's impossible to check for this event *before* a query gets
> executed, but would it be possible to have a database option that
> essentially replaces zeroes with NULLs in the circumstances that this does
> occur so the query still returns the rest of the table?  or would doing that
> be sloppy database management?
>
> so maybe this fails
>
> select 1 / 0 as x;
>
> but you change some setting and it auto-coerces everything under a division
> operator to
>
> select 1 / ( CASE WHEN value = 0 THEN NULL ELSE value END ) as x;
>
> that way you lose x but maybe not other columns?

If you don't mind unportable code, use the div_noerror function
instead of default division:

sql>create function div_noerror(l double, r double) returns double
external name calc.div_noerror;
operation successful (223.503ms)
sql>select div_noerror(1, 0) as x;
+--------------------------+
| x                        |
+==========================+
|                     null |
+--------------------------+
1 tuple (132.864ms)

_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list