Date: 2014-08-25 19:01:28 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>
To: SQL devs <>
Version: 11.17.21 (Jan2014-SP3)
CC: @njnes

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

Comment 20090

Date: 2014-08-25 19:01:28 +0200
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:31.0) Gecko/20100101 Firefox/31.0
Build Identifier:

While a test is made to prevent a division by 0, the computation is performed nonetheless before the test causing the query to fail.

Reproducible: Always

Steps to Reproduce:

CREATE FUNCTION my_avg(total INT, cnt INT) RETURNS INT
BEGIN
RETURN CASE WHEN cnt <> 0 THEN total / cnt ELSE -1 END;
END;

Actual Results:

SELECT my_avg(0, 0);
division by zero.

EXPLAIN SELECT my_avg(0, 0);
..
| X_25 := calc.!=(A1,0:int); |
| X_26 := calc.isnil(X_25); |
| X_28 := calc.ifthenelse(X_26,false,X_25); |
| X_29 := calc./(A0,A1); |
| X_30 := calc.ifthenelse(X_28,X_29,-1:int); |
..

Expected Results:

-1 was expected because the condition should have prevented the division by 0.

I'm not sure if the SQL standard cover this detail.

Comment 20100

Date: 2014-08-27 21:38:22 +0200
From: @njnes

Fixed by handling the expressions from the persistent stored modules also in the rel_case_fixup optimizer.

Comment 20101

Date: 2014-08-27 21:40:30 +0200
From: MonetDB Mercurial Repository <>

Changeset f900af1e70a0 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=f900af1e70a0

Changeset description:

handle psm expressions in exp_print and exp_case_fixup (solving the
div_by_zero bug #3555)

Comment 20378

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

Oct2014 has been released.