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?
sorry if this is stupid..and thank you
On 20/05/15 23:05, Anthony Damico 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?
Interesting idea. You could encode that in your SQL query using subqueries, but, yes, it could also be much easier to have a specific optimizer step that injects that code in specific places.
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?
sorry if this is stupid..and thank you
users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Anthony -
It is not stupid - this is exactly what Tableau does for all databases when compiling a divide operator.
On May 20, 2015, at 14:05 , Anthony Damico <ajdamico@gmail.commailto: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?
sorry if this is stupid..and thank you _______________________________________________ users-list mailing list users-list@monetdb.orgmailto:users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Best regards, ------------------------------------------------- Richard Wesley Research Scientist Tableau Software
t: 206.633.3400 x6335249 f: 206.633.3004 e: hawkfish@tableau.commailto:hawkfish@tableau.com
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)
this is **exactly** what i was looking for. thanks everyone!
On Wed, May 20, 2015 at 8:55 PM, Stefan O'Rear stefan@gudtech.com wrote:
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
participants (4)
-
Anthony Damico -
Martin Kersten -
Richard Wesley -
Stefan O'Rear