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

COALESCE could be more optimized #6198

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

COALESCE could be more optimized #6198

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
bug Something isn't working normal SQL

Comments

@monetdb-team
Copy link

Date: 2017-02-02 10:39:15 +0100
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>
To: SQL devs <>
Version: 11.25.5 (Dec2016-SP1)
CC: @njnes

Last updated: 2017-03-03 10:24:12 +0100

Comment 24944

Date: 2017-02-02 10:39:15 +0100
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/55.0.2883.75 Safari/537.36
Build Identifier:

It seems that COALESCE get compiled to non optimal MAL code:

X_17:bat[:bit] := batcalc.isnil(X_16);
X_20:bat[:bit] := batcalc.not(X_17);
X_22:bat[:bit] := batcalc.isnil(X_20);
X_27:bat[:bit] := batcalc.ifthenelse(X_22,false,X_20);
X_31:bat[:int] := batcalc.ifthenelse(X_27,X_16,0:int);

X_17 cannot contains nil (right?), thus X_20 neither. From that X_22 is superfluous, and likewise for X_27.

I think it should be only:

X_17:bat[:bit] := batcalc.isnil(X_16);
X_20:bat[:bit] := batcalc.not(X_17);
X_31:bat[:int] := batcalc.ifthenelse(X_20,X_16,0:int);

This could even be further simplified into:

X_17:bat[:bit] := batcalc.isnil(X_16);
X_31:bat[:int] := batcalc.ifthenelse(X_17,0:int,X_16);

or:

X_17:bat[:bit] := batcalc.isnotnil(X_16);
X_31:bat[:int] := batcalc.ifthenelse(X_17,X_16,0:int);

Reproducible: Always

Comment 24945

Date: 2017-02-02 10:44:07 +0100
From: Frédéric Jolliton <<frederic.jolliton+monetdb>>

I forgot to say that it is for such an expression:

SELECT COALESCE(value, 0) FROM ..

where X_16 represents the `value' column (of int type) and X_31 is the resulting column.

Tested with the default_pipe.

Comment 24985

Date: 2017-02-08 12:40:53 +0100
From: @njnes

fixed in Dec2016 version. Indeed for COALESCE we did to many ifthen/isnil combo's. The additional optimization (isnotnil or ifelsethen should be handled (later) at the mal layer))

Comment 25109

Date: 2017-03-03 10:24:12 +0100
From: @sjoerdmullender

Dec2016-SP2 has been released, incorporating the fix.

@monetdb-team monetdb-team added bug Something isn't working normal SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working normal SQL
Projects
None yet
Development

No branches or pull requests

2 participants