- users-list - monetdb.org

[MonetDB-users] more sql blob
by Ronald Oussoren 03 Oct '06

03 Oct '06

Another problem with SQL Blob's is that they cannot be NULL :-(. (using the same testblob table as my previous mail) sql>insert into testblob(id, b) VALUES (NULL, NULL); MAPI = monetdb@localhost:50000 QUERY = insert into testblob(id, b) VALUES (NULL, NULL); ERROR = !ERROR: interpret_params s0_0(param 2): invalid sqlblob. I can probably work around this, but using empty blobs is less elegant than NULL values. Ronald

1 0

[MonetDB-users] SQL blob's
by Ronald Oussoren 03 Oct '06

03 Oct '06

My database scheme requires usage of blobs, but something seems wrong there: CREATE TABLE testblob (id INT, b BLOB); INSERT INTO testblob (id, b) VALUES ( 1, BLOB '1234'); SELECT * FROM testblob; # sys.testblob, sys.testblob # table_name # id, b # name # int, blob # type # 1, 0 # length [ 1, 0203 ] DROP TABLE testblob; It turns out that the conversion of a SQL blob literal to an array of bytes is buggy, the patch below fixes that. Ronald $ diff -u ../MonetDB-4.12.1/src/modules/plain/blob.c.org ../ MonetDB-4.12.1/src/modules/plain/blob.c --- ../MonetDB-4.12.1/src/modules/plain/blob.c.org 2006-10-03 16:04:05.000000000 +0200 +++ ../MonetDB-4.12.1/src/modules/plain/blob.c 2006-10-03 16:15:04.691486914 +0200 @@ -370,14 +370,15 @@ s++; res <<= 4; if (*s >= '0' && *s <= '9') { - res = *s - '0'; + res |= *s - '0'; } else if (*s >= 'A' && *s <= 'F') { - res = 10 + *s - 'A'; + res |= 10 + *s - 'A'; } else if (*s >= 'a' && *s <= 'f') { - res = 10 + *s - 'a'; + res |= 10 + *s - 'a'; } else { GDKerror("sqlblob_fromstr: Illegal char '%c' in blob\n", *s); } + s++; result->data[i] = res; } Ronald

1 0

[MonetDB-users] sql/jdbcclient...Exception in thread "main' java.lang.OutOfMemoryError
by George H 02 Oct '06

02 Oct '06

Fabian: I'm way out of you guys' league. I wouldn't know a stack trace from my elbow, but I'll do what I can to help. Just say so and tell me how. As far as whether I can tell if the MapiClient displayed all the rows or just a subset? Well, I actually did the MapiClient runs with 40million rows. It took awhile for it to role through them all, and of course, without the exception. Query results with less rows took less time. I can't say for sure if it actually showed all the rows or a subset, but I just assumed it was all of them because of how long it took to work through it. I'm afraid I am unaware of how to know if it showed all the rows or a subset. I only saw the beginning and the end of the runs, and that much of it seemed right. If there is any way I can help, just let me know. George

2 2

Re: [MonetDB-users] Recent bugs and question about modules
by Stefan Manegold 29 Sep '06

29 Sep '06

Maurice, finally, here's my reply --- at least as far as I Think I can answers your question right now... On Fri, Sep 29, 2006 at 10:02:51AM +0200, Maurice van Keulen wrote: > Hi Stefan, > > Thanks for resolving some of my bugs. I'm trying to develop an XQuery > module for probabilistic XML. This involves highly recursive functions > and XML fragments being constructed and then queried upon again in the > next function. I'm impressed that I got so far already (322 lines of > really complex XQuery code). Every time I do stumble onto a bug, I try > to isolate it and report it nicely. Currently, the bug that handers my > progress most is "1562868: Complex XQuery fails with error in > "batbat_lng_add_inplace". It is hard to already give the correct answer > to the test you created. I suggest to simply approve the result when the > error message does not occur anymore. And, if you are ready to pick one > of my bugs I reported, please have a look at this one if you will. Well, Jan spent quite some time on this one (see the bug report; Thanks, Jan!) and it appears to be a tircky one --- I'll consider applying Jan's "fast and ugly" solution for the time being, and *try* to find a better one as soon as time permits... > I also have a question. Eventually, my functions need to become part of > a module for Probabilistic XML. Besides XQuery functions, I also have a > few fundamental functions that are much better dealt with in MIL. I > have, for example, a function product that, given a sequence of numbers, > multiplies all these numbers: > > declare function product($seq as xs:decimal*) as xs:decimal > { if (count($seq) gt 0) then exactly-one($seq[1]) * > product($seq[position() gt 1]) else 1.0 }; > > This recursive definition of the function is for sure much less > efficient than something I could write in MIL. > > My question is: how can I develop a module with additional functions > that are implemented in MIL and usable in XQuery? Well, obviously(?), there are to ways to go. The first is to indeed add a new built-in function and provide a MIL implementation as we do for all other built-in functions --- I guess, this is not what you intended, right? The second one is to provide an XQuery module. In fact, MonetDB/XQuery allows both "plain" XQuery module definitions and MIL module definitions --- as far as I know, the best (only? for now?) way to create a MIL modules (template) is to have the pathfinder compiler translate an XQuery module to MIL (basically, Peter added the support for MIL modules to have pre-compiled modules, saving the XQuery to MIL translation...). Here's my approach to your example: ======== $ cat /tmp/mvk.xq module namespace mvk = "http://wwwhome.cs.utwente.nl/~keulen/"; declare function mvk:product($seq as xs:decimal*) as xs:decimal { if (count($seq) gt 0) then exactly-one($seq[1]) * mvk:product($seq[position() gt 1]) else 1.0 }; -------- $ pf /tmp/mvk.xq > /tmp/mvk.mil -------- $ echo 'import module namespace mvkXQ = "http://wwwhome.cs.utwente.nl/~keulen/" at "/tmp/mvk.xq"; mvkXQ:product((1,2,3,4))' | MapiClient -lx <?xml version="1.0" encoding="utf-8"?> <XQueryResult>24.000000</XQueryResult> -------- $ echo 'import module namespace mvkMIL = "http://wwwhome.cs.utwente.nl/~keulen/" at "/tmp/mvk.mil"; mvkMIL:product((1,2,3,4))' | MapiClient -lx <?xml version="1.0" encoding="utf-8"?> <XQueryResult>24.000000</XQueryResult> ======== well, if you look at the attached mvk.mil, you'll see that it looks quite complicated, and using it as a template to replace the recursive product implementation by a more efficient version will be quite tricky. Hence, here's another approach: While there is (AFAIK) not product aggregation in XQUery (yet?), there is one in MIL; and there is a sum aggregation in both. Hence, simply take a fn:sum in XQuery have it translated to MIL, which results in usage of {sum}() in MIL, and replace the latter by {prod}(): ======== $ cat /tmp/stm.xq module namespace stm = "http://www.cwi.nl/~manegold/"; declare function stm:product($seq as xs:decimal*) as xs:decimal { sum($seq) }; -------- $ pf /tmp/stm.xq > /tmp/stm.mil -------- $ egrep '{sum}|{prod}' /tmp/stm.mil var iter_aggr := {sum}(item.leftfetchjoin(dec_values), iter_grp, reverse(loop000)).tmark(0@0); -------- $ perl -i -p -e 's|{sum}|{prod}|' /tmp/stm.mil -------- $ egrep '{sum}|{prod}' /tmp/stm.mil var iter_aggr := {prod}(item.leftfetchjoin(dec_values), iter_grp, reverse(loop000)).tmark(0@0); -------- $ echo 'import module namespace stmXQ = "http://www.cwi.nl/~manegold/" at "/tmp/stm.xq"; stmXQ:product((1,2,3,4))' | MapiClient -lx <?xml version="1.0" encoding="utf-8"?> <XQueryResult>10.000000</XQueryResult> -------- $ echo 'import module namespace stmMIL = "http://www.cwi.nl/~manegold/" at "/tmp/stm.mil"; stmMIL:product((1,2,3,4))' | MapiClient -lx <?xml version="1.0" encoding="utf-8"?> <XQueryResult>24.000000</XQueryResult> ======== Seem to work well ;-) As said, I don't know a better way to produce a MonetDB/XQuery conforming MIL module by hand, but this way, you can create a template and modify it even further than I did here. There's one(?) caveat, though: Modules created this way are not necessairyly protable from one version of MonetDB/XQuery to another one, as the requires/expected signature of the respective MIL procedure and/or format/content of its arguments might change... Hope this helps you further, anyway... More suggestions are of course welcome (that's why I moved this discussion to this mailing list ;-)) Stefan > Thanks again for your effort! > Maurice. > > -- > ---------------------------------------------------------------------- > Dr.Ir. M. van Keulen - Assistant Professor, Data Management Technology > Univ. of Twente, Dept of EEMCS, POBox 217, 7500 AE Enschede, Netherlands > Email: m.vankeulen(a)utwente.nl, Phone: +31 534893688, Fax: +31 534892927 > Room: INF3039, WWW: http://www.cs.utwente.nl/~keulen -- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

1 0

Re: [MonetDB-users] sql sequence
by George H 27 Sep '06

27 Sep '06

Hey Guys: I hate to bother you, but I just downloaded and installed the updated builds presently (Sept 27) posted on http://monetdb.cwi.nl/testing/projects/monetdb/Stable/.DailyBuilds./RPMs/Su… I still get: monetdb-> create table test (name varchar(10), seq serial); Operation successful monetdb-> create table test1 (name varchar(10)); Operation successful monetdb-> insert into test1 values ('a'), ('b'), ('c'); 3 affected rows monetdb-> insert into test (name) select (name) from test1; Error: ERROR: INSERT INTO: PRIMARY KEY constraint 'test.test_seq_pkey' violated What do you think? George

1 0

[MonetDB-users] Obtain the career you have always wanted with the University Degree you deserve. Bo6p8HFZV7ZcX
by monet＠isp4mail.com 27 Sep '06

27 Sep '06

1 0

[MonetDB-users] sql/jdbcclient...Exception in thread "main' java.lang.OutOfMemoryError
by George H 26 Sep '06

26 Sep '06

I'm using Suse 10.1 with an AMD dual core processor and 850MB memory (note: There's 1000MB of ram but 150MB is shared with the video card thus leaving the system to recognize only 850MB) MonetDB softwares used are the packages presently posted on: http://monetdb.cwi.nl/testing/projects/monetdb/Stable/.DailyBuilds./RPMs/Su… With the table, "test" having 4 million rows using sql and jdbcclient: geo->select column1 from test; It displays the column1 query results for about 10 seconds and then the jdbcclient-1.4.jar crashes leaving the following message before it bugs out: Exception in thread "main" java.lang.OutOfMemeoryError I tried many many combinations of java options concerning memory and gc to try to fix it but in my hands, no luck. I worked around this by switching to the MapiClient which works just fine. Just thought there might be some interest in these results. Thanks to everyone again.....George

2 1

Re: [MonetDB-users] sql sequence
by George H 26 Sep '06

26 Sep '06

Thanks Fabian and Niels. A fix of this bug is greatly welcomed. That was quick action. I'm using SUSE10.1 and I just installed the build presently posted on: http://monetdb.cwi.nl/testing/projects/monetdb/Stable/.DailyBuilds./RPMs/Su… However, this build still carries the problem. Will the fix appear on this web page and if so when? Or is there some other way I should get this fix? Thanks again..........George

2 1

Re: [MonetDB-users] sql sequence
by George H 26 Sep '06

26 Sep '06

Thanks Fabian and Niels. A fix of this bug is greatly welcomed. That was quick action. I'm using SUSE10.1 and I just installed the build presently posted on: http://monetdb.cwi.nl/testing/projects/monetdb/Stable/.DailyBuilds./RPMs/Su… However, this build still carries the problem. Will the fix appear on this web page and if so when? Or is there some other way I should get this fix? Thanks again..........George

1 0

Re: [MonetDB-users] sql sequence
by George H 26 Sep '06

26 Sep '06

Hey Fabian: Thanks so much for your response. I really appreciate the help. I don't think I can use the idea of creating a table with a sequence column and later doctoring that column with an ALTER SEQUENCE statement because I use a COPY INTO command to populate the table with a text file. The table must have the same number of columns as the test file for the COPY INTO statement to work and the text file has no sequence column. Do you know of some other way to populate a table from a text file that might get around this? I tried to use an INSERT statement to populate a table that already had a sequence column, but that didn't work either. Here's what I did. sql->create table test (name varchar(10), seq serial); sql->create table test2 (name varchar(10)); sql->insert into test2 values ('a), ('b'), ('c'); [ 3 ] sql->insert into test (name) select (name) from test2; ERROR = !ERROR: INSERT INTO: PRIMARY KEY constraint 'test.test_seq_pkey' violated So if I use the COPY INTO statement to populate a table from my text file, I can't use the resulting table in an INSERT/SELECT statement to populate another table that has a sequence column. Is there some course of action you can think of to get a sequenced table from a text file? With 40million+ rows it sure can't be done by hand. Again, thanks for your help and also thanks for all your postings on this list. I've found them interesting and instructive. Best......George

3 2