Date: 2013-03-12 01:44:24 +0100
From: Masood Mortazavi <>
To: SQL devs <>
Version: 11.15.3 (Feb2013-SP1)
CC: masoodmortazavi, @njnes

Last updated: 2013-03-21 21:22:45 +0100

Comment 18604

Date: 2013-03-12 01:44:24 +0100
From: Masood Mortazavi <>

User-Agent: Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:19.0) Gecko/20100101 Firefox/19.0
Build Identifier:

11.16.0 / development/unreleased branch, probably @ changeset: 47036:a6120285222e

MAL code generated for simple selects on VIEWs does not work in the mal interpreter while the MAL generated for the base tables on which VIEWs are built seems to work fine.

$ mserver5 --version
MonetDB 5 server v11.16.0 (64-bit, 64-bit oids)
This is an unreleased version
Copyright (c) 1993-July 2008 CWI
Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved
Visit http://www.monetdb.org/ for further information
Found 15.5GiB available memory, 8 available cpu cores
Libraries:
libpcre: 8.30 2012-02-04 (compiled with 8.30)
openssl: OpenSSL 1.0.1c 10 May 2012 (compiled with OpenSSL 1.0.1c 10 May 2012)
libxml2: 2.8.0 (compiled with 2.8.0)
Compiled by: root@masood-ThinkCentre-M91p (x86_64-unknown-linux-gnu)
Compilation: gcc -g -Werror -Wall -Wextra -W -Werror-implicit-function-declaration -Wpointer-arith -Wdeclaration-after-statement -Wundef -Wformat=2 -Wno-format-nonliteral -Winit-self -Winvalid-pch -Wmissing-declarations -Wmissing-format-attribute -Wmissing-prototypes -Wold-style-definition -Wpacked -Wunknown-pragmas -Wvariadic-macros -fstack-protector-all -Wstack-protector -Wpacked-bitfield-compat -Wsync-nand -Wjump-misses-init -Wmissing-include-dirs -Wlogical-op -Wunreachable-code
Linking : /usr/bin/ld -m elf_x86_64

Reproducible: Always

Steps to Reproduce:

1. Create database described
2. Load data
3. Generate MAL using Explain in the SQL interpreter for "select *" queries on either tables or views
4. Try to run these generated MAL code in the interpreter, and observe that while the MAL generated for the base tables (i.e. parents1/2, teachers, students1/2) work, the MAL for parents (a view) doesn't.

START TRANSACTION;
CREATE TABLE Students1 (id INT NOT NULL, name VARCHAR(64) NOT NULL, parent_id INT NOT NULL, teacher_id INT NOT NULL) ;
CREATE TABLE Students2 (id INT NOT NULL, name VARCHAR(64) NOT NULL, parent_id INT NOT NULL, teacher_id INT NOT NULL) ;
CREATE TABLE Parents1 (id INT NOT NULL, name VARCHAR(64) NOT NULL) ;
CREATE TABLE Parents2 (id INT NOT NULL, name VARCHAR(64) NOT NULL) ;
CREATE TABLE Teachers (id INT NOT NULL, name VARCHAR(64) NOT NULL) ;
create view Students as
(select * from Students1
union all
select * from Students2);
create view Parents as
(select * from Parents1
union all
select * from Parents2);
COMMIT;

$ cat parents1.txt
10|Gillian|
1|Moore|
2|Thompson|
9|Lee|

$ cat parents2.txt
5|Gore|
7|McIntyre|
8|Lee|
======================== Working MAL
$ cat simple-select-from-parents1.txt
function user.s1_1{autoCommit=true}():void;
X_15 := nil:bat[:oid,:int];
X_22 := nil:bat[:oid,:str];
barrier X_42 := language.dataflow();
X_2 := sql.mvc();
X_3:bat[:oid,:oid] := sql.tid(X_2,"TS","parents1");
X_6 := sql.bind(X_2,"TS","parents1","id",0);
(X_9,r1_9) := sql.bind(X_2,"TS","parents1","id",2);
X_12 := sql.bind(X_2,"TS","parents1","id",1);
X_14 := sql.delta(X_6,X_9,r1_9,X_12);
X_15 := algebra.leftfetchjoin(X_3,X_14);
X_16 := sql.bind(X_2,"TS","parents1","name",0);
(X_18,r1_22) := sql.bind(X_2,"TS","parents1","name",2);
X_20 := sql.bind(X_2,"TS","parents1","name",1);
X_21 := sql.delta(X_16,X_18,r1_22,X_20);
X_22 := algebra.leftfetchjoin(X_3,X_21);
language.pass(X_3);
exit X_42;
X_23 := sql.resultSet(2,1,X_15);
sql.rsColumn(X_23,"TS.parents1","id","int",32,0,X_15);
sql.rsColumn(X_23,"TS.parents1","name","varchar",64,0,X_22);
X_31 := io.stdout();
sql.exportResult(X_31,X_23);
end s1_1;
========= Non-working MAL generated by EXPLAIN select * from parents;
$ cat simple-select-from-parents.txt
function user.s3_1{autoCommit=true}():void;
X_28 := nil:bat[:oid,:int];
X_53 := nil:bat[:oid,:str];
barrier X_80 := language.dataflow();
X_3:bat[:oid,:int] := bat.new(nil:oid,nil:int);
X_2 := sql.mvc();
X_6:bat[:oid,:oid] := sql.tid(X_2,"TS","parents1");
X_9 := sql.bind(X_2,"TS","parents1","id",0);
(X_12,r1_12) := sql.bind(X_2,"TS","parents1","id",2);
X_15 := sql.bind(X_2,"TS","parents1","id",1);
X_17 := sql.delta(X_9,X_12,r1_12,X_15);
X_18 := algebra.leftfetchjoin(X_6,X_17);
X_19 := bat.append(X_3,X_18,true);
X_20:bat[:oid,:oid] := sql.tid(X_2,"TS","parents2");
X_22 := sql.bind(X_2,"TS","parents2","id",0);
(X_23,r1_28) := sql.bind(X_2,"TS","parents2","id",2);
X_25 := sql.bind(X_2,"TS","parents2","id",1);
X_26 := sql.delta(X_22,X_23,r1_28,X_25);
X_27 := algebra.leftfetchjoin(X_20,X_26);
X_28 := bat.append(X_19,X_27,true);
X_30:bat[:oid,:str] := bat.new(nil:oid,nil:str);
X_33 := sql.bind(X_2,"TS","parents1","name",0);
(X_38,r1_52) := sql.bind(X_2,"TS","parents1","name",2);
X_41 := sql.bind(X_2,"TS","parents1","name",1);
X_43 := sql.delta(X_33,X_38,r1_52,X_41);
X_44 := algebra.leftfetchjoin(X_6,X_43);
X_45 := bat.append(X_30,X_44,true);
X_46 := sql.bind(X_2,"TS","parents2","name",0);
(X_48,r1_67) := sql.bind(X_2,"TS","parents2","name",2);
X_50 := sql.bind(X_2,"TS","parents2","name",1);
X_51 := sql.delta(X_46,X_48,r1_67,X_50);
X_52 := algebra.leftfetchjoin(X_20,X_51);
X_53 := bat.append(X_45,X_52,true);
language.pass(X_6);
language.pass(X_20);
exit X_80;
X_55 := sql.resultSet(2,1,X_28);
sql.rsColumn(X_55,".parents","id","int",32,0,X_28);
sql.rsColumn(X_55,".parents","name","varchar",64,0,X_53);
X_66 := io.stdout();
sql.exportResult(X_66,X_55);
end s3_1;

Comment 18605

Date: 2013-03-12 01:52:34 +0100
From: Masood Mortazavi <>

Created attachment 186
DD for the test case

Attached file: TS-shell.sql (text/x-sql, 647 bytes)
Description: DD for the test case

Comment 18606

Date: 2013-03-12 01:53:34 +0100
From: Masood Mortazavi <>

Created attachment 187
What appears to be faulty MAL generated by explain (for a VIEW)

Attached file: simple-select-from-parents.txt (text/plain, 2773 bytes)
Description: What appears to be faulty MAL generated by explain (for a VIEW)

Comment 18607

Date: 2013-03-12 01:54:21 +0100
From: Masood Mortazavi <>

Created attachment 188
What appears to be working MAL for a base table

Attached file: simple-select-from-parents1.txt (text/plain, 1608 bytes)
Description: What appears to be working MAL for a base table

Comment 18608

Date: 2013-03-12 01:54:48 +0100
From: Masood Mortazavi <>

Created attachment 189
Datafile for one base table

Attached file: parents1.txt (text/plain, 40 bytes)
Description: Datafile for one base table

Comment 18609

Date: 2013-03-12 01:55:15 +0100
From: Masood Mortazavi <>

Created attachment 190
Datafile for another base table

Attached file: parents2.txt (text/plain, 27 bytes)
Description: Datafile for another base table

Comment 18633

Date: 2013-03-21 20:11:39 +0100
From: @njnes

Could you also include the error? Generated mal (explain output) requires a
sql context.

Comment 18637

Date: 2013-03-21 21:04:01 +0100
From: Masood Mortazavi <>

When running the attached (non-working) mal on the "parents" view, I get the following error returned by the mclient:

ERROR = !TypeException:user.s3_1[12]:'bat.append' undefined in: X_19:any := bat.append(X_3:bat[:oid,:int], X_18:bat[:oid,:any], _1:bit)
!TypeException:user.s3_1[19]:'bat.append' undefined in: X_28:bat[:oid,:int] := bat.append(X_19:any, X_27:bat[:oid,:any], _1:bit)
!TypeException:user.s3_1[26]:'bat.append' undefined in: X_45:any := bat.append(X_30:bat[:oid,:str], X_44:bat[:oid,:any], _43:bit)
!TypeException:user.s3_1[32]:'bat.append' undefined in: X_53:bat[:oid,:str] := bat.append(X_45:any, X_52:bat[:oid,:any], _43:bit)
mal>

after it ingests the mal from the file and prints it out exactly as in the attached.

If you need further information, please do let me know.

Comment 18638

Date: 2013-03-21 21:22:45 +0100
From: @njnes

MAL is a fully type language. The explain output however leaves out some of the type specifics. The working example the output type of the leftfetchjoin is known
while in the non working this isn't the case. We however do not support using the output of explain directly as mal input, its there just for debugging.