EXPLAIN output *almost* re-parsable
Almost for fun, I did this little experiment: take the output of an EXPLAIN from sql, and execute it in MAL (mclient -l mal). I found only *1* detail which doesn't parse, here it is in a minimal example: * 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> Yay, it works. ** The reason I write this email:* - I suspect that the output of EXPLAIN is not meant to necessarily be re-parsable, but just to be informative - Nevertheless, it really is almost re-parsable. I tried a much larger query, (2000+ lines of MAL), and this was the only detail. It looks like fixing that small detail could be worth it. - Also, I wonder if this is, in fact, a bug. Binds to access types 0 and 1 do get the right type information. Why not access type 2?
Roberto Thanks for the experiment. Originally MAL was designed to allow for such actions, and since recent changes, has become easier. The ratio was to easily take snippets of a SQL query (including some wrapped up catalog stuff) and turn it into more controllable MAL tests. Consider it a bug when you cannot do what you did. We'll fix it in due time. regards, Martin On 10/10/16 15:12, Roberto Cornacchia wrote:
Almost for fun, I did this little experiment: take the output of an EXPLAIN from sql, and execute it in MAL (mclient -l mal).
I found only *1* detail which doesn't parse, here it is in a minimal example:
* 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>
Yay, it works.
** The reason I write this email:* - I suspect that the output of EXPLAIN is not meant to necessarily be re-parsable, but just to be informative - Nevertheless, it really is almost re-parsable. I tried a much larger query, (2000+ lines of MAL), and this was the only detail. It looks like fixing that small detail could be worth it. - Also, I wonder if this is, in fact, a bug. Binds to access types 0 and 1 do get the right type information. Why not access type 2?
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Oops, found another one :p
Filing both.
On 10 October 2016 at 15:19, Martin Kersten
Roberto
Thanks for the experiment. Originally MAL was designed to allow for such actions, and since recent changes, has become easier. The ratio was to easily take snippets of a SQL query (including some wrapped up catalog stuff) and turn it into more controllable MAL tests.
Consider it a bug when you cannot do what you did.
We'll fix it in due time.
regards, Martin
On 10/10/16 15:12, Roberto Cornacchia wrote:
Almost for fun, I did this little experiment: take the output of an EXPLAIN from sql, and execute it in MAL (mclient -l mal).
I found only *1* detail which doesn't parse, here it is in a minimal example:
* 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>
Yay, it works.
** The reason I write this email:* - I suspect that the output of EXPLAIN is not meant to necessarily be re-parsable, but just to be informative - Nevertheless, it really is almost re-parsable. I tried a much larger query, (2000+ lines of MAL), and this was the only detail. It looks like fixing that small detail could be worth it. - Also, I wonder if this is, in fact, a bug. Binds to access types 0 and 1 do get the right type information. Why not access type 2?
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (2)
-
Martin Kersten -
Roberto Cornacchia