- users-list - monetdb.org

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

aggregate function in MAL
by Scott Mathieson 05 Jul '13

05 Jul '13

Hi, I am trying to create a simple aggregate function entirely in MAL and am now struggling at the final hurdle. I have created the aggregate function such that it aggregates across an entire table but in the sub aggregate function I can't work out how to link the group to data BAT (g) to the data. The aggregate is designed to simply collect a sample of the values from the named column and return them as a pipe delimited string value. Can anyone tell me how to use the 'g' bat to join to the 'b' bat in the sub... aggregate function to get only the data rows for a specific group - currently I only get 2 rows of data per group even though I know that there should be 100k+ rows in each group? Here's the code: function bin_sample(b:bat[:oid, :any]):str; value := ""; bsamp := sample.uniform(b, 1000:wrd); barrier (h, t) := iterator.new(bsamp); tmp := "" + t; type := bat.getTailType(bsamp); test := str.startsWith(type, "str"); # Force the string type to be cast to a string or you just end up with numeric values barrier xxxx := test; tmp := str.str(t); exit xxxx; value := value + tmp; value := value + "||"; redo (h, t) := iterator.next(bsamp); exit (h, t); return value; end bin_sample; function subbin_sample(b:bat[:oid,:any_1],g:bat[:oid,:oid],e:bat[:oid,:any_2],skip_nils:bit) :bat[:oid,:str]; bn:=bat.new(:oid, :str); # check 'e' has some values - this is the list of group IDs in the head # 'g' is the group to the data BAT head ID mapping # 'b' is the data column BAT that we're aggregating over barrier (grpid, t) := iterator.new(e); # create a single row BAT of the group id we're processing bn_gid := bat.new(:oid, :oid); bat.insert(bn_gid, grpid, grpid); # join this to the GID->DATA map BAT to get the Group DATA oids b_g2dr := bat.reverse(g); b_gid2data := algebra.leftjoin(bn_gid, b_g2dr); grpval := ""; # Get the BAT of the actual data to be processed # # !!! HOW TO GET THIS TO WORK???????? # b_data := algebra.join(b_gid2data, b); grpval := bin_sample(b_data); # Store the result for this group bat.insert(bn, grpid, grpval); redo (grpid, t) := iterator.next(e); exit (grpid, t); return bn; end subbin_sample; Scott Mathieson, ________________________________

2 3

Failing to load a table
by Franck Routier 05 Jul '13

05 Jul '13

Hi all, I am trying to copy a table from a postgresql database into monetdb, but it fails, with monetdb filling the disks. I am using latest version of Monetdb (Feb2013-SP3) on Ubuntu Linux, with 32 GB Ram and SSD disks. To copy into Monetdb, I have a java program that runs mclient as: mclient --language=sql --databas=database --host=host --user=user /tmp/command.ctl with command.ctl : COPY nnn RECORDS INTO table FROM STDIN USING DELIMITERS'|', '\n', '\' NULL AS '' Then the Postgresql resultset is read from jdbc, formatted and written into STDIN. So far so good, it work with my dimensions tables (small), and with a 295 millions rows fact table that has 109 columns. This fact table takes 86 GB on the disk for Postgresql storage (plus indices). I have another form of the fact table, with less rows (184 millions) but more columns (505). While the first form of the table has a measureId column and only one value column, the other form of the table has one value column per measure. (It is a "sum(case when measureId=X then value else null ... group by" version of the first table). Loading this "columnar" table fails, with monetdb filling the disks (something like 500 GB was left before the loading process) and quitting (not sure what happens exactly here on the server, but I get a broken pipe error in java, and the monetdb database is lost). The table size on disk for Postgresql is 37 GB. Using VectorWise, the table loads, and take only 7 GB on disks, but the loading process (very similar to what we do with MonetDB) takes a very long time( 25 hours), much longer than what happens with the 109 columns tables. I am trying to make sense of that and understand why this table is giving a (very) hard time to monetdb (and somehow VectorWise as well). Does anyone have an explanation ? thanks, Franck

4 12

how are user defined function arguments passed ?
by integrity 05 Jul '13

05 Jul '13

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

1 0

Announcement: New Feb2013-SP3 Bugfix release of MonetDB suite
by Hannes Muehleisen 02 Jul '13

02 Jul '13

The MonetDB team at CWI/MonetDB BV is pleased to announce the Feb2013-SP3 bugfix release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>.

2 1

SQL question
by Rémi Daniel 28 Jun '13

28 Jun '13

Hello, I'm a French user of MonetDB, and I have a SQL question, I want to execute on MonetDB equivalent of this mysql request, and i don't find any solution : "SELECT field1, field2 FROM table GROUP BY field3 LIMIT 5;" I want to do a GROUP BY of a field3 which is not in the SELECT to get a limit of request results by this field3, like the "first" function of some databases. Does anyone have any idea on how I can do that ? Thanks for your help, Remi -- <http://inbox.fr> Rémi Daniel Développeur Web rdaniel(a)inbox.fr 30, rue Vincent Moris 92240 Malakoff - France Fax : +33 (0)1 55 48 02 79

5 7