Date: 2016-10-10 15:47:32 +0200
From: @swingbit
To: SQL devs <>
Version: 11.23.7 (Jun2016-SP1)
CC: martin.van.dinther, @njnes, qehy, sagarharish999, shawpolakcrax12, shery41, zihu

Last updated: 2020-11-26 15:31:54 +0100

Comment 24476

Date: 2016-10-10 15:47:32 +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:

• Got the explain of a trivial query:

sql>\f raw
sql>explain select name from sys.functions;
% .explain table_name
% mal name
% clob type
% 94 length
function user.s4_1():void;
X_32:void := querylog.define("explain select name from sys.functions;","default_pipe",21);
X_16 := bat.new(nil:oid,nil:str);
X_24 := bat.append(X_16,"sys.functions");
X_19 := bat.new(nil:oid,nil:str);
X_26 := bat.append(X_19,"name");
X_20 := bat.new(nil:oid,nil:str);
X_27 := bat.append(X_20,"varchar");
X_21 := bat.new(nil:oid,nil:int);
X_29 := bat.append(X_21,256);
X_23 := bat.new(nil:oid,nil:int);
X_31 := bat.append(X_23,0);
X_1 := sql.mvc();
C_2:bat[:oid] := sql.tid(X_1,"sys","functions");
X_5:bat[:str] := sql.bind(X_1,"sys","functions","name",0);
(C_8,r1_8) := sql.bind(X_1,"sys","functions","name",2);
X_11:bat[:str] := sql.bind(X_1,"sys","functions","name",1);
X_13 := sql.delta(X_5,C_8,r1_8,X_11);
X_14 := algebra.projection(C_2,X_13);
sql.resultSet(X_24,X_26,X_27,X_29,X_31,X_14);
end user.s4_1;
optimizer.mitosis()
optimizer.dataflow()

• Opened a MAL session, initialized sql, and pasted the function above:

mal>sql.init();
mal>function user.s4_1():void;
mal> X_32:void := querylog.define("explain select name from sys.functions;","default_pipe",21);
mal> X_16 := bat.new(nil:oid,nil:str);
mal> X_24 := bat.append(X_16,"sys.functions");
mal> X_19 := bat.new(nil:oid,nil:str);
mal> X_26 := bat.append(X_19,"name");
mal> X_20 := bat.new(nil:oid,nil:str);
mal> X_27 := bat.append(X_20,"varchar");
mal> X_21 := bat.new(nil:oid,nil:int);
mal> X_29 := bat.append(X_21,256);
mal> X_23 := bat.new(nil:oid,nil:int);
mal> X_31 := bat.append(X_23,0);
mal> X_1 := sql.mvc();
mal> C_2:bat[:oid] := sql.tid(X_1,"sys","functions");
mal> X_5:bat[:str] := sql.bind(X_1,"sys","functions","name",0);
mal> (C_8,r1_8) := sql.bind(X_1,"sys","functions","name",2);
mal> X_11:bat[:str] := sql.bind(X_1,"sys","functions","name",1);
mal> X_13 := sql.delta(X_5,C_8,r1_8,X_11);
mal> X_14 := algebra.projection(C_2,X_13);
mal> sql.resultSet(X_24,X_26,X_27,X_29,X_31,X_14);
mal>end user.s4_1;
MAPI = (monetdb) /tmp/.s.monetdb.54500
QUERY = end user.s4_1;
ERROR = !TypeException:user.s4_1[17]:'sql.delta' undefined in: X_13:any := sql.delta(X_5:bat[:str],C_8:bat[:oid],r1_8:bat[:any],X_11:bat[:str]);
mal>

The error is easy to find: sql.delta() expects r1_8 to be of type :bat[:str], but it gets :bat[:any] instead.

• If I fix it manually:

mal>function user.s4_1():void;
mal> X_32:void := querylog.define("explain select name from sys.functions;","default_pipe",21);
mal> X_16 := bat.new(nil:oid,nil:str);
mal> X_24 := bat.append(X_16,"sys.functions");
mal> X_19 := bat.new(nil:oid,nil:str);
mal> X_26 := bat.append(X_19,"name");
mal> X_20 := bat.new(nil:oid,nil:str);
mal> X_27 := bat.append(X_20,"varchar");
mal> X_21 := bat.new(nil:oid,nil:int);
mal> X_29 := bat.append(X_21,256);
mal> X_23 := bat.new(nil:oid,nil:int);
mal> X_31 := bat.append(X_23,0);
mal> X_1 := sql.mvc();
mal> C_2:bat[:oid] := sql.tid(X_1,"sys","functions");
mal> X_5:bat[:str] := sql.bind(X_1,"sys","functions","name",0);
mal> (C_8,r1_8:bat[:str]) := sql.bind(X_1,"sys","functions","name",2);
mal> X_11:bat[:str] := sql.bind(X_1,"sys","functions","name",1);
mal> X_13 := sql.delta(X_5,C_8,r1_8,X_11);
mal> X_14 := algebra.projection(C_2,X_13);
mal> sql.resultSet(X_24,X_26,X_27,X_29,X_31,X_14);
mal>end user.s4_1;
mal>

This works.

I am not sure whether the issue is only in how the type information is displayed by EXPLAIN, or if really isn't computed correctly.

Reproducible: Always

Comment 24551

Date: 2016-10-19 14:30:05 +0200
From: @njnes

backported fix to Jun2016