July 2013 - users-list

Giving monetdb a fair chance
by Franck Routier 12 Jul '13

12 Jul '13

Hi, I am benchmarking different DB alternatives for our BI application. We are using Mondrian/JPivot on a dataset with ~300 millions row, and several parent/child dimensions. We are running some 50 real life scenario (ie 50 differents cubes using parts of this dataset) and measuring user response time on a server with several stripped Sata3 SSD, 32 GB ram and a i7-3820 CPU @ 3.60GHz (6 cores). During the tests, Monetdb performed by far the worst of Postgresql, VectorWise and (obvioulsy) MonetDB. Things that take 10 seconds with Postgresql take 450 second with monetdb... This was not expected at all, so looking at the server, I saw that MonetDB was consuming all memory (why not), including swap : bad idea I think. I was expecting MonetDB to memory mapped files when needed, real memory, but not use swap as it it was real memory... So here is my question : with a (quite) big dataset, 32GB ram, is there anything I can do to help MonetDB behave according to expectancies ? (maybe I should use ulimit ?). Thanks for your input, Franck

4 10

Problem with Mtest.py and monetdb command
by Bingyi Cao 11 Jul '13

11 Jul '13

Hey, I am new to MonetDB. I installed MonetDB yesterday. And I run Mtest.py. The results are as below: *!ERROR: Testing FAILED SIGNIFICANTLY (18 out of 1854 tests failed)* * * * 40 out of 1854 tests could not be executed* * 1 out of 1854 tests produced slightly different output* * sql/test/BugTracker-2012/predicate_select.Bug-3090* * 17 out of 1854 tests produced SIGNIFICANTLY different output* * monetdb5/optimizer/inlineFunction* * monetdb5/optimizer/ifthencst* * monetdb5/tests/BugTracker/kunion-and-nil.Bug-1667* * sql/jdbc/tests/Test_PSsomeamount* * sql/jdbc/tests/Test_PSlargebatchval* * sql/jdbc/tests/Test_PSmanycon* * sql/jdbc/tests/Test_PSmetadata* * sql/jdbc/tests/Test_PSsqldata* * sql/jdbc/tests/Test_Rsqldata* * sql/jdbc/tests/BugConcurrent_sequences* * sql/test/BugTracker-2010/incomplete-utf8-sequence.Bug-2575* * sql/test/BugTracker-2012/conditions_when_for_triggers_do_not_work.Bug-2073* * sql/test/BugTracker-2012/create_function.Bug-3172* * sql/test/BugTracker-2012/currenttime.Bug-2781* * sql/test/BugTracker-2012/null_except_null.Bug-3040* * sql/test/BugTracker-2012/day-of-month-localization.Bug-2962* * sql/test/Triggers/recursive_triggers* * * * * * First, check the testing results in /usr/local/mTests/index.html !* * * * Then, fix the problems by:* * - fixing sources and test scripts* * - fixing stable output by hand* * - approving test output by Mapprove.py (cf. Mapprove.py -?)* * * * After that, re-run Mtest.* I hasn't change any source code yet. I am not sure whether I installed it correctly. Also now I have no problem with command monetdbd, mserver5, mclient. However, I cannot use command with monetdb, the error information is always as below: monetdb: cannot find a control socket, use -h and/or -p I did a little bit research, to me it seems that it should automatically find a socket. Tell me if I am wrong. I also try to use the option -h -P, which doesn't work still. Do I actually install it correctly I tried to uninstall and reinstall it,but it hasn't solve the problem at all. Thanks very much!

2 3

Re: Fw:how are user defined function arguments passed ?
by integrity 10 Jul '13

10 Jul '13

Thanks, it's working, I am so excited, thank you all Best regards! Meng ------------------ Original ------------------ From: "Martin Kersten"<Martin.Kersten(a)cwi.nl>; Date: Wed, Jul 10, 2013 05:23 PM To: "users-list"<users-list(a)monetdb.org>; Subject: Re: Fw:how are user defined function arguments passed ? Yes, PATTERNs and COMMANDs have different interfaces. regards, Martin On 7/10/13 10:41 AM, Lefteris wrote: > try on your mal definition toinstead of the keyword pattern, to use > the keyword command. Patterns have a different signature if i remember > correctly. > > On Wed, Jul 10, 2013 at 10:28 AM, integrity <357416268(a)qq.com> wrote: >> Thanks very much for your instant reply,Lefteris! >> >> I wrote a simple test UDF, but it doesnt work. >> >> myudf.c >> #include "monetdb_config.h" >> #include "myudf.h" >> >> str >> addint(int *ret,int *one) >> { >> >> *ret=(*one)+10; >> >> return MAL_SUCCEED; >> >> } >> ------------------------------------------------------------------------------------------------------------ >> myudf.h >> /* In your own module, replace "UDF" & "udf" by your module's name */ >> >> #ifndef _SQL_MYUDF_H_ >> #define _SQL_MYUDF_H_ >> #include "sql.h" >> #include <string.h> >> >> /* This is required as-is (except from renaming "UDF" & "udf" as suggested >> * above) for all modules for correctly exporting function on Unix-like and >> * Windows systems. */ >> >> #ifdef WIN32 >> #ifndef LIBMYUDF >> #define myudf_export extern __declspec(dllimport) >> #else >> #define myudf_export extern __declspec(dllexport) >> #endif >> #else >> #define myudf_export extern >> #endif >> >> /* export MAL wrapper functions */ >> >> myudf_export str addint(int *ret, int *one); >> #endif /* _SQL_MYUDF_H_ */ >> ----------------------------------------------------------------------------------------------------- >> myudf.mal >> # scalar MAL signatures >> module myudf; >> >> pattern addint(one:int):int >> address addint >> comment "add one and two"; >> ----------------------------------------------------------------------------------------------------- >> 82_myudf.sql >> create function addint(one INT) >> returns int external name myudf.addint; >> ----------------------------------------------------------------------------------------------------- >> when i execute in sql: >> sql>select addint(76777); >> +---------------------+ >> | addint_single_value | >> +=====================+ >> | 0 | >> +---------------------+ >> 1 tuple (1.364ms) >> sql> >> sql>select addint(100); >> +---------------------+ >> | addint_single_value | >> +=====================+ >> | 0 | >> +---------------------+ >> 1 tuple (0.295ms) >> sql> >> sql> >> sql> >> sql>select addint(101); >> +---------------------+ >> | addint_single_value | >> +=====================+ >> | 0 | >> +---------------------+ >> 1 tuple (0.289ms) >> sql>select addint(1010000000000); >> overflow in conversion of 1010000000000 to int. >> sql> >> sql>select addint(1010000); >> +---------------------+ >> | addint_single_value | >> +=====================+ >> | 0 | >> +---------------------+ >> 1 tuple (1.087ms) >> sql> >> >> i checked every format of above files many times, i cannot find what's wrong >> with my program... >> it seems that the parameter didn't got passed to MAL.... >> >> Thanks again for your help! >> >> Sincerely, >> >> Meng >> ------------------ Original ------------------ >> From: "Lefteris"<lsidir(a)gmail.com>; >> Date: Wed, Jul 10, 2013 04:13 PM >> To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; >> Subject: Re: Fw:how are user defined function arguments passed ? >> >> The str *ret is you return value as described in the MAL signature >> reverse(ra1:str):str >> >> the ra1 is the name of a str variable in MAL, which is mapped to the >> str *src variable of the UDF. Naming here plays no role, just the >> types and the order of definition. Similarly, the :str end of the mal >> signature reverse(ra1:str):str means that it returns a str type, which >> in turn is mapped to the str *ret variable of the UDF signature. >> Naming plays no role again, just that the first variable of the >> signature in the udf is the return variable. Ofcourse it does not need >> to be always a str, it depends on the definition of the mal signature. >> >> On Wed, Jul 10, 2013 at 9:59 AM, integrity <357416268(a)qq.com> wrote: >>> Could anyone explain this question to me, please? >>> >>> I am still wondering on it.... >>> >>> Thanks! >>> >>> Meng >>> ------------------ Original ------------------ >>> From: "integrity"<357416268(a)qq.com>; >>> Date: Fri, Jul 5, 2013 03:42 PM >>> To: "users-list"<users-list(a)monetdb.org>; >>> Subject: how are user defined function arguments passed ? >>> >>> Hi everyone, >>> >>> In >>> >>> http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti… >>> In thesection about how to extend SQL with a simple scalar function to >>> reverse a string >>> >>> step 3. Extension starts with a definitin of the MAL signatures. See the >>> example given, or browse through the files in monetdb5/modules/mal/*.mal >>> to >>> get a glimpse on how to write them. The MonetDB kernel documentation >>> provides more details. The file contains the MAL snippet: >>> command reverse(ra1:str):str >>> address UDFreverse >>> comment "Reverse a string"; >>> >>> step 4. The signature says that it expects a command body implementation >>> under the name UDFreverse, shown below. The C-signature is a direct >>> mapping, >>> where arguments are passed by reference and the return value(s) >>> references >>> are the first in the arguments list. The body should return a (malloced) >>> string to denote an exception being raised or MAL_SUCCEED upon access. >>> #include "udf.h" >>> >>> static str >>> reverse(const char *src) >>> { >>> size_t len; >>> str ret, new; >>> >>> /* The scalar function returns the new space */ >>> len = strlen(src); >>> ret = new = GDKmalloc(len + 1); >>> if (new == NULL) >>> return NULL; >>> new[len] = 0; >>> while (len > 0) >>> *new++ = src[--len]; >>> return ret; >>> } >>> >>> str >>> UDFreverse(str *ret, str *src) >>> { >>> if (*src == 0 || strcmp(*src, str_nil) == 0) >>> *ret = GDKstrdup(str_nil); >>> else >>> *ret = reverse(*src); >>> return MAL_SUCCEED; >>> } >>> >>> >>> I don't know why the argument name of "command reverse(ra1:str):str" >>> different from function definition UDFreverse(str *ret, str *src)? >>> >>> how does ret come from? is it according to the naming rules that the first >>> argument is the result, must it be a string type? >>> >>> Thanks! >>> >>> Meng >>> >>> >>> _______________________________________________ >>> users-list mailing list >>> users-list(a)monetdb.org >>> http://mail.monetdb.org/mailman/listinfo/users-list >>> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> http://mail.monetdb.org/mailman/listinfo/users-list >> . >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> http://mail.monetdb.org/mailman/listinfo/users-list >> > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list > _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

1 0

Re: Fw:how are user defined function arguments passed ?
by integrity 10 Jul '13

10 Jul '13

Thanks very much for your instant reply,Lefteris! I wrote a simple test UDF, but it doesnt work. myudf.c #include "monetdb_config.h" #include "myudf.h" str addint(int *ret,int *one) { *ret=(*one)+10; return MAL_SUCCEED; } ------------------------------------------------------------------------------------------------------------ myudf.h /* In your own module, replace "UDF" & "udf" by your module's name */ #ifndef _SQL_MYUDF_H_ #define _SQL_MYUDF_H_ #include "sql.h" #include <string.h> /* This is required as-is (except from renaming "UDF" & "udf" as suggested * above) for all modules for correctly exporting function on Unix-like and * Windows systems. */ #ifdef WIN32 #ifndef LIBMYUDF #define myudf_export extern __declspec(dllimport) #else #define myudf_export extern __declspec(dllexport) #endif #else #define myudf_export extern #endif /* export MAL wrapper functions */ myudf_export str addint(int *ret, int *one); #endif /* _SQL_MYUDF_H_ */ ----------------------------------------------------------------------------------------------------- myudf.mal # scalar MAL signatures module myudf; pattern addint(one:int):int address addint comment "add one and two"; ----------------------------------------------------------------------------------------------------- 82_myudf.sql create function addint(one INT) returns int external name myudf.addint; ----------------------------------------------------------------------------------------------------- when i execute in sql: sql>select addint(76777); +---------------------+ | addint_single_value | +=====================+ | 0 | +---------------------+ 1 tuple (1.364ms) sql> sql>select addint(100); +---------------------+ | addint_single_value | +=====================+ | 0 | +---------------------+ 1 tuple (0.295ms) sql> sql> sql> sql>select addint(101); +---------------------+ | addint_single_value | +=====================+ | 0 | +---------------------+ 1 tuple (0.289ms) sql>select addint(1010000000000); overflow in conversion of 1010000000000 to int. sql> sql>select addint(1010000); +---------------------+ | addint_single_value | +=====================+ | 0 | +---------------------+ 1 tuple (1.087ms) sql> i checked every format of above files many times, i cannot find what's wrong with my program... it seems that the parameter didn't got passed to MAL.... Thanks again for your help! Sincerely, Meng ------------------ Original ------------------ From: "Lefteris"<lsidir(a)gmail.com>; Date: Wed, Jul 10, 2013 04:13 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: Fw:how are user defined function arguments passed ? The str *ret is you return value as described in the MAL signature reverse(ra1:str):str the ra1 is the name of a str variable in MAL, which is mapped to the str *src variable of the UDF. Naming here plays no role, just the types and the order of definition. Similarly, the :str end of the mal signature reverse(ra1:str):str means that it returns a str type, which in turn is mapped to the str *ret variable of the UDF signature. Naming plays no role again, just that the first variable of the signature in the udf is the return variable. Ofcourse it does not need to be always a str, it depends on the definition of the mal signature. On Wed, Jul 10, 2013 at 9:59 AM, integrity <357416268(a)qq.com> wrote: > Could anyone explain this question to me, please? > > I am still wondering on it.... > > Thanks! > > Meng > ------------------ Original ------------------ > From: "integrity"<357416268(a)qq.com>; > Date: Fri, Jul 5, 2013 03:42 PM > To: "users-list"<users-list(a)monetdb.org>; > Subject: how are user defined function arguments passed ? > > Hi everyone, > > In > http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti… > In thesection about how to extend SQL with a simple scalar function to > reverse a string > > step 3. Extension starts with a definitin of the MAL signatures. See the > example given, or browse through the files in monetdb5/modules/mal/*.mal to > get a glimpse on how to write them. The MonetDB kernel documentation > provides more details. The file contains the MAL snippet: > command reverse(ra1:str):str > address UDFreverse > comment "Reverse a string"; > > step 4. The signature says that it expects a command body implementation > under the name UDFreverse, shown below. The C-signature is a direct mapping, > where arguments are passed by reference and the return value(s) references > are the first in the arguments list. The body should return a (malloced) > string to denote an exception being raised or MAL_SUCCEED upon access. > #include "udf.h" > > static str > reverse(const char *src) > { > size_t len; > str ret, new; > > /* The scalar function returns the new space */ > len = strlen(src); > ret = new = GDKmalloc(len + 1); > if (new == NULL) > return NULL; > new[len] = 0; > while (len > 0) > *new++ = src[--len]; > return ret; > } > > str > UDFreverse(str *ret, str *src) > { > if (*src == 0 || strcmp(*src, str_nil) == 0) > *ret = GDKstrdup(str_nil); > else > *ret = reverse(*src); > return MAL_SUCCEED; > } > > > I don't know why the argument name of "command reverse(ra1:str):str" > different from function definition UDFreverse(str *ret, str *src)? > > how does ret come from? is it according to the naming rules that the first > argument is the result, must it be a string type? > > Thanks! > > Meng > > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list > _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list .

3 2

Fw:how are user defined function arguments passed ?
by integrity 10 Jul '13

10 Jul '13

Could anyone explain this question to me, please? I am still wondering on it.... Thanks! Meng ------------------ Original ------------------ From: "integrity"<357416268(a)qq.com>; Date: Fri, Jul 5, 2013 03:42 PM To: "users-list"<users-list(a)monetdb.org>; Subject: how are user defined function arguments passed ? Hi everyone, In http://www.monetdb.org/Documentation/Cookbooks/SQLrecipes/UserDefinedFuncti… In thesection about how to extend SQL with a simple scalar function to reverse a string step 3. Extension starts with a definitin of the MAL signatures. See the example given, or browse through the files in monetdb5/modules/mal/*.mal to get a glimpse on how to write them. The MonetDB kernel documentation provides more details. The file contains the MAL snippet: command reverse(ra1:str):str address UDFreverse comment "Reverse a string"; step 4. The signature says that it expects a command body implementation under the name UDFreverse, shown below. The C-signature is a direct mapping, where arguments are passed by reference and the return value(s) references are the first in the arguments list. The body should return a (malloced) string to denote an exception being raised or MAL_SUCCEED upon access. #include "udf.h" static str reverse(const char *src) { size_t len; str ret, new; /* The scalar function returns the new space */ len = strlen(src); ret = new = GDKmalloc(len + 1); if (new == NULL) return NULL; new[len] = 0; while (len > 0) *new++ = src[--len]; return ret; } str UDFreverse(str *ret, str *src) { if (*src == 0 || strcmp(*src, str_nil) == 0) *ret = GDKstrdup(str_nil); else *ret = reverse(*src); return MAL_SUCCEED; } I don't know why the argument name of "command reverse(ra1:str):str" different from function definition UDFreverse(str *ret, str *src)? how does ret come from? is it according to the naming rules that the first argument is the result, must it be a string type? Thanks! Meng

2 1

Bind variables and parameterising the expression in case when
by Dan 10 Jul '13

10 Jul '13

Hi, I seem to have come across a strange restriction in using bind variables in MonetDB. If i use bind variables in the normal way by saying where field=${var} then it works. But not in this case: Here is a sample: select case when ${Brand} = 'All' And here is the error: EXEC: wrong type for argument 1 of prepared statement: char, expected char This is using the latest version of the jdbc driver (2.9) via Pentaho. Not quite sure how to set up a test case of this via a normal SQL client though. Thanks for any suggestions! Dan

2 1

Connection monitoring
by Taka Yasuda 09 Jul '13

09 Jul '13

Hi, What is the best way to monitor the connections to the monetdb server? Regards, Taka

2 4

Problems executing custom aggregation function on large datasets
by Sebastian Dorok 09 Jul '13

09 Jul '13

Hi all, I'm developing a custom aggregation function. Everything works fine until I use it on large datasets (ca. 270 Mio. rows). Here is what my function signature looks like: str AGGSimpleGenotype(int *ret, int *bid, int *gid, int *eid, bit ignore_nils); I assume that the BATs identified by bid and gid have the same length. That's why my function checks this circumstance. On my test dataset (ca. 200 rows) everything works fine. When I use my production dataset my aggregation function returns an error complaining that the BATs have not the same length and stops aggregating (by design)! How can this happen? The SQL query is: select grpgenotype(base) from test.bases group by pos; I attached the MAL plan generated for the large dataset. When I execute it step by step I get an error in line 39 saying: mal>X_106 := sql.delta(X_56,X_70,X_71,X_8); MAPI = (monetdb) /tmp/.s.monetdb.50000 QUERY = X_106 := sql.delta(X_56,X_70,X_71,X_8); ERROR = !TypeException:user.main[1]:'sql.delta' undefined in: X_106:any := sql.delta(X_56:bat[:oid,:str], X_70:bat[:oid,:oid], X_71:bat[:oid,:str], X_8:bat[:oid,:any]) This error occurs before my function is entered but I think it must be related with my problem. Is it an internal MonetDB problem? I'm using the Feb2013-SP2 release. Any ideas? Thx.

4 4

how to debug and use user defined functions?
by hlfwm hlfwm 09 Jul '13

09 Jul '13

i defined a user defined function "binarycopy",when i use my function, there is error: sql>select binarycopy('starid','1111',1); TypeException:user.s1_1[5]:'svom.binarycopy' undefined in: _9:any := svom.binarycopy(_6:str, _7:str, _8:int) program contains errors i want to debug the error with mal, there also some error: [svomhpc@localhost /home/data1]$>mclient -l mal -u monetdb -d mydb4 password: Welcome to mclient, the MonetDB interactive terminal (unreleased) Type \q to quit, \? for a list of available commands mal> mal> mal>function test(i:int):str; mal>io.print(i); mal>i:=i*2; mal>b:=bat.new(:int,:int); mal>bat.insert(b,1,i); mal>io.print(b); mal>return test:= "ok"; mal>end test; MAPI = (monetdb) /tmp/.s.monetdb.50000 QUERY = end test; ERROR = !TypeException:user.test[3]:'bat.new' undefined in: b:any := bat.new(_5:int, _6:int) !TypeException:user.test[4]:'bat.insert' undefined in: _7:any := bat.insert(b:any, _8:int, i:int) mal> could anyone tell me how to debug my function ,please? Thanks!

8 28

create table as fails
by Franck Routier 08 Jul '13

08 Jul '13

Hi, I am trying to create a table as select, but it fails... Here is the statement: create table imagerie_ft as select dwhdim.*, dwhq135.q135 from dwhdim join dwhq135 on dwhdim.dwhcolcsu = dwhq135.dwhcolcsu; The select alone works fine. The same statement with create view instead of create table works fine. But the create table fails with: Error: syntax error, unexpected SCOLON, expecting WITH in: "create table imagerie_ft as SQLState: 42000 ErrorCode: 0 Error: select dwhdim.*, dwhq135.q135 SQLState: 22000 ErrorCode: 0 Error: from dwhdim join dwhq135 on dwhdim.dwhcolcsu = dwhq135.dwhcolcsu;" SQLState: 22000 ErrorCode: 0 Should I report a bug, or is using the * in the statement for a create table a non supported sql idiom? Franck

3 2