Date: 2016-10-10 16:23:46 +0200
From: @swingbit
To: MonetDB5 devs <>
Version: 11.23.7 (Jun2016-SP1)
CC: @mlkersten

Last updated: 2016-12-21 13:07:29 +0100

Comment 24477

Date: 2016-10-10 16:23:46 +0200
From: @swingbit

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

This is related to Bug #6072.

The output of this explain should be parsable in a MAL session, but it misses type information on a constant. Missing type information means that during parsing the type is guessed wrongly.

sql>set cache=false;
sql>\f raw
sql>explain select x.id from (select cast(id as double) as id from sys.functions) as x where x.id > 1;
% .explain table_name
% mal name
% clob type
% 61 length
function user.main():void;
barrier X_44 := language.dataflow();
X_20 := bat.new(nil:oid,nil:str);
X_28 := bat.append(X_20,"sys.x");
X_23 := bat.new(nil:oid,nil:str);
X_30 := bat.append(X_23,"id");
X_24 := bat.new(nil:oid,nil:str);
X_31 := bat.append(X_24,"double");
X_25 := bat.new(nil:oid,nil:int);
X_33 := bat.append(X_25,53);
X_27 := bat.new(nil:oid,nil:int);
X_35 := bat.append(X_27,0);
X_1 := sql.mvc();
X_5:bat[:int] := sql.bind(X_1,"sys","functions","id",0);
(C_8,r1_8) := sql.bind(X_1,"sys","functions","id",2);
X_11:bat[:int] := sql.bind(X_1,"sys","functions","id",1);
X_13 := sql.delta(X_5,C_8,r1_8,X_11);
X_14 := batcalc.dbl(X_13);
C_2:bat[:oid] := sql.tid(X_1,"sys","functions");
C_16 := algebra.thetasubselect(X_14,C_2,1,">");
X_18 := algebra.projection(C_16,X_14);
language.pass(X_14);
exit X_44;
sql.resultSet(X_28,X_30,X_31,X_33,X_35,X_18);
end user.main;

From here, we can already see that C_16 will fail, because X_14 is :bat[:dbl] and the constant is 1, which means 1:int

Here's the MAL session:

mal>sql.init();
mal>function user.main():void;
mal>barrier X_44 := language.dataflow();
mal> X_20 := bat.new(nil:oid,nil:str);
mal> X_28 := bat.append(X_20,"sys.x");
mal> X_23 := bat.new(nil:oid,nil:str);
mal> X_30 := bat.append(X_23,"id");
mal> X_24 := bat.new(nil:oid,nil:str);
mal> X_31 := bat.append(X_24,"double");
mal> X_25 := bat.new(nil:oid,nil:int);
mal> X_33 := bat.append(X_25,53);
mal> X_27 := bat.new(nil:oid,nil:int);
mal> X_35 := bat.append(X_27,0);
mal> X_1 := sql.mvc();
mal> X_5:bat[:int] := sql.bind(X_1,"sys","functions","id",0);
mal> (C_8,r1_8:bat[:int]) := sql.bind(X_1,"sys","functions","id",2);
mal> X_11:bat[:int] := sql.bind(X_1,"sys","functions","id",1);
mal> X_13 := sql.delta(X_5,C_8,r1_8,X_11);
mal> X_14 := batcalc.dbl(X_13);
mal> C_2:bat[:oid] := sql.tid(X_1,"sys","functions");
mal> C_16 := algebra.thetasubselect(X_14,C_2,1,">");
mal> X_18 := algebra.projection(C_16,X_14);
mal> language.pass(X_14);
mal>exit X_44;
mal> sql.resultSet(X_28,X_30,X_31,X_33,X_35,X_18);
mal>end user.main;
MAPI = (monetdb) /tmp/.s.monetdb.54500
QUERY = end user.main;
ERROR = !TypeException:user.main[19]:'algebra.thetasubselect' undefined in: C_16:any := algebra.thetasubselect(X_14:bat[:dbl],C_2:bat[:oid],1:int,">":str);
mal>

Indeed, constant 1 is re-parsed as i:int, when it was meant to be 1:dbl.

Notice that this is visible only when setting cache=false. Otherwise, the constant would be turned into a (correctly typed) function parameter A0:dbl.

Reproducible: Always

Comment 24478

Date: 2016-10-11 19:28:18 +0200
From: @mlkersten

Fixed in default branch

Comment 24479

Date: 2016-10-11 19:28:42 +0200
From: MonetDB Mercurial Repository <>

Changeset 2539534aa3d1 made by Martin Kersten mk@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Fixing bug #6073

Comment 24532

Date: 2016-10-18 11:51:17 +0200
From: @swingbit

I need to reopen it, because the fix was meant for Bug #6072 (which can be closed), not for this one.

Comment 24633

Date: 2016-10-26 23:32:09 +0200
From: @mlkersten

Re-parsing the MAL output also requires that the signatures of the SQL parts are known. This requires importing their signature files, e.g. include sql;

Furthermore, a SQL environment should be set up to enable their execution.

Comment 24648

Date: 2016-11-03 11:10:08 +0100
From: @swingbit

Martin, perhaps I'm not understanding well your last suggestion.

I used

include sql;
sql.init();

before defining the function, but that doesn't fix it.
Did you mean something different?

My impression is that constants are always printed with no type information.
Then type is guessed during parsing, but can fail (here, it should be 1:dbl but it is guessed as 1:int).

mal>include sql;
mal>sql.init();
mal>function user.main():void;
mal>barrier X_44 := language.dataflow();
mal> X_20 := bat.new(nil:oid,nil:str);
mal> X_28 := bat.append(X_20,"sys.x");
mal> X_23 := bat.new(nil:oid,nil:str);
mal> X_30 := bat.append(X_23,"id");
mal> X_24 := bat.new(nil:oid,nil:str);
mal> X_31 := bat.append(X_24,"double");
mal> X_25 := bat.new(nil:oid,nil:int);
mal> X_33 := bat.append(X_25,53);
mal> X_27 := bat.new(nil:oid,nil:int);
mal> X_35 := bat.append(X_27,0);
mal> X_1 := sql.mvc();
mal> X_5:bat[:int] := sql.bind(X_1,"sys","functions","id",0);
mal> (C_8,r1_8:bat[:int]) := sql.bind(X_1,"sys","functions","id",2);
mal> X_11:bat[:int] := sql.bind(X_1,"sys","functions","id",1);
mal> X_13 := sql.delta(X_5,C_8,r1_8,X_11);
mal> X_14 := batcalc.dbl(X_13);
mal> C_2:bat[:oid] := sql.tid(X_1,"sys","functions");
mal> C_16 := algebra.thetasubselect(X_14,C_2,1,">");
mal> X_18 := algebra.projection(C_16,X_14);
mal> language.pass(X_14);
mal>exit X_44;
mal> sql.resultSet(X_28,X_30,X_31,X_33,X_35,X_18);
mal>end user.main;
MAPI = (monetdb) /tmp/.s.monetdb.54500
QUERY = end user.main;
ERROR = !TypeException:user.main[19]:'algebra.thetasubselect' undefined in: C_16:any := algebra.thetasubselect(X_14:bat[:dbl],C_2:bat[:oid],1:int,">":str);

Comment 24683

Date: 2016-11-13 23:21:35 +0100
From: @mlkersten

You are right. It is better to keep the type name around as well in most cases.

Enhanced the mal_listing routine to show it.