Division by zero in CASE statement that should avoid it

[monetdb-team]

Date: 2014-08-19 15:30:19 +0200
From: @hannesmuehleisen
To: SQL devs <>
Version: 11.17.13 (Jan2014-SP1)
CC: @njnes

Last updated: 2014-10-31 14:14:59 +0100

Comment 20056

Date: 2014-08-19 15:30:19 +0200
From: @hannesmuehleisen

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/38.0.2125.0 Safari/537.36
Build Identifier:

Run the following statements

create table t(r integer,a integer);
insert into t values (1,2),(2,0),(3,null);
select case when not (a is null) and a > 0.0 then r/a else -1 end as s from t;

Reproducible: Always

Actual Results:

Error message 'division by zero.'

Expected Results:

Result should be returned.

Also happens in the Oct2014 branch.

Weirdly,

select case when not (a is null) and a > 0.0 then r/a else -1 end as s from t;

works fine.

Comment 20057

Date: 2014-08-19 15:32:01 +0200
From: MonetDB Mercurial Repository <>

Changeset 2a4c32b79774 made by Hannes Muehleisen hannes@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=2a4c32b79774

Changeset description:

Test for Bug #3546 and mc R connector

Comment 20058

Date: 2014-08-19 15:33:07 +0200
From: @hannesmuehleisen

Correction, the breaking query is

select case when not (a is null) and a > 0.0 then 1.0*r/a else -1 end as r from t;

Comment 20062

Date: 2014-08-20 10:57:11 +0200
From: MonetDB Mercurial Repository <>

Changeset 9698a5f83bf6 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=9698a5f83bf6

Changeset description:

generalized div detection/rewrites which fixes bug #3546

Comment 20102

Date: 2014-08-28 23:33:45 +0200
From: MonetDB Mercurial Repository <>

Changeset 440f4e5a5586 made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=440f4e5a5586

Changeset description:

ifthenelse.Bug-3546 & round.Bug-3542: approved int128 output

Comment 20400

Date: 2014-10-31 14:14:59 +0100
From: @sjoerdmullender

Oct2014 has been released.