COALESCE could be more optimized

[monetdb-team]

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.