Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Order of evaluation inside CASE WHEN #3555

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

Order of evaluation inside CASE WHEN #3555

monetdb-team opened this issue Nov 30, 2020 · 0 comments

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant