May 2016 - users-list

[MonetDB-users] Proofreading your professional documents
by Richard Hounslow 24 Feb '24

24 Feb '24

Dear Professor/Lecturer Please excuse us for contacting you directly. We are offering an efficient service to help you ensure your academic and professional work is written in perfect English. We will check the grammar and style of your work and return it to you to meet your requirements and deadlines. Proof-Reading-Service.com can provide you with a professional proofreading service at a very reasonable rate. All our proofreaders are highly qualified native English speakers. Many work as leading academics in their fields and all have extensive experience of proofreading to the highest standards. If you are interested in our service, please take a look at our website: www.proof-reading-service.com All you have to do is send us your document as a word attachment with the deadline and we will guarantee delivery of a perfectly written document to give you complete confidence when you submit your work. The fee is worked out on a flat rate (£6.95 per thousand words or 0.695 pence per word), so you know exactly how much the proofreading will cost in advance. Once we have received your document(s) we will confirm the word count, the price and the deadline. If you have not heard from us within 2 hours during normal business hours after you have sent your work please resend it from a different email account or send the file to proofreadings(a)gmail.com. We look forward to hearing from you! Yours faithfully Proof-Reading-Service.com

2 2

SUM() OVER (ORDER BY..)
by Anthony Damico 13 Dec '16

13 Dec '16

hi, monetdb does not support SUM() OVER commands.. does anyone have a smart alternative to implement this? thanks SELECT batch_id, job_count, SUM(job_count) OVER (ORDER BY duration) as cumjobs, SUM(job_count) over () as totjobs, duration FROM test_data ;

5 13

[Low level] Understanding duplicate entries in delta BAT (delete BAT)
by Frédéric Jolliton 08 Aug '16

08 Aug '16

Hi, As per MonetDB Solution suggestion, we are asking here about this topic. We are studying how MonetDB store its database on disk. So far we were able to understand pretty much all the files (BAT, journal, BBP.dir, heap, hash, imprints, and so on). We have custom tools to decode and dump them (experimental at this point). There is one thing however that is unclear: we thought that "delta BAT", which are the BAT assigned to an SQL tables to list the OIDs of rows that were deleted (named "D_<schema>_<table>" internaly), would contains only *uniques* values (since you can never delete twice the same row). But on several databases that we are running, we found that some of theses BATs contain duplicates. Lot of duplicates actually. Especially on D_sys__columns and D_sys__tables BATs (respectively for the sys._columns and sys._tables system tables). Some databases do not have this "issue" (while they all have the same schema, and process the same kind of data as the other ones). Can someone explain if duplicates are expected in theses BATs ? Here is an excerpt of D_sys__tables: # hexdump -C 02/210.tail 00000000 2e 00 00 00 00 00 00 00 2f 00 00 00 00 00 00 00 |......../.......| 00000010 30 00 00 00 00 00 00 00 31 00 00 00 00 00 00 00 |0.......1.......| 00000020 32 00 00 00 00 00 00 00 33 00 00 00 00 00 00 00 |2.......3.......| 00000030 34 00 00 00 00 00 00 00 35 00 00 00 00 00 00 00 |4.......5.......| 00000040 36 00 00 00 00 00 00 00 37 00 00 00 00 00 00 00 |6.......7.......| 00000050 42 00 00 00 00 00 00 00 43 00 00 00 00 00 00 00 |B.......C.......| 00000060 44 00 00 00 00 00 00 00 45 00 00 00 00 00 00 00 |D.......E.......| 00000070 46 00 00 00 00 00 00 00 47 00 00 00 00 00 00 00 |F.......G.......| 00000080 48 00 00 00 00 00 00 00 49 00 00 00 00 00 00 00 |H.......I.......| 00000090 4a 00 00 00 00 00 00 00 4b 00 00 00 00 00 00 00 |J.......K.......| 000000a0 4c 00 00 00 00 00 00 00 4d 00 00 00 00 00 00 00 |L.......M.......| 000000b0 4e 00 00 00 00 00 00 00 4f 00 00 00 00 00 00 00 |N.......O.......| 000000c0 50 00 00 00 00 00 00 00 50 00 00 00 00 00 00 00 |P.......P.......| 000000d0 51 00 00 00 00 00 00 00 50 00 00 00 00 00 00 00 |Q.......P.......| 000000e0 51 00 00 00 00 00 00 00 52 00 00 00 00 00 00 00 |Q.......R.......| 000000f0 50 00 00 00 00 00 00 00 51 00 00 00 00 00 00 00 |P.......Q.......| 00000100 52 00 00 00 00 00 00 00 53 00 00 00 00 00 00 00 |R.......S.......| 00000110 50 00 00 00 00 00 00 00 51 00 00 00 00 00 00 00 |P.......Q.......| 00000120 52 00 00 00 00 00 00 00 53 00 00 00 00 00 00 00 |R.......S.......| ... You can clearly see duplicates OID (the first one being 0x50 at 0xc8). Its entry in BBP.dir (split into sections): 136 32 tmp_210 tmpr_210 02/210 610523782 2 171807 0 0 171807 172032 0 0 0 0 void 0 1 1793 0 0 0 0 0 1000651 0 0 0 oid 8 0 1024 24 25 27 1 46 773603235 1374456 1376256 1 We are using: MonetDB 5 server v11.21.14 (64-bit, 64-bit oids, 128-bit integers). -- Frédéric Jolliton Sécuractive

4 3

Why aren't hashes preserved by joins?
by Roberto Cornacchia 10 Jun '16

10 Jun '16

I noticed a difference between - a hash-based string selection from a persistent, read-only table - a hash-based join on the same table and same column They both build a hash on the same string column (verified with gdb), but the select can reuse the hash (second call is almost free), while the join keeps rebuilding the hash. Is this expected? Roberto

2 4

group_concat in MAL not working due to algebra.uselect
by Puthick Hok 04 Jun '16

04 Jun '16

Hi, Last year, I was looking for the MAL definition of group_concat and Nik Schuiling gave me the MAL definition which is below. We compile MonetDB from source and this definition was working on MonetDB-11.19.9 (Oct2014-SP2). Since this version, there has been a number of newer versions of MonetDB. So, I was trying to upgrade our system to the latest version of MonetDB (11.21.13). After the installation of the newer version, I ran: select "DataSetId", group_concat("FieldName") from datasetmarkermeta group by "DataSetId"; from mclient interface and got the following error message: TypeException:kddart.subgroup_concat[5]:'algebra.uselect' undefined in: TIDs:any := algebra.uselect(g:bat[:oid,:oid],grpid:oid); MALException:kddart.subgroup_concat[0]:Error in cloned function TypeException:user.s2_1[15]:'kddart.subgroup_concat' undefined in: X_27:bat[:oid,:str] := kddart.subgroup_concat(X_26:bat[:oid,:str],X_16:bat[:oid,:oid],r1_16:bat[:oid,:oid],true:bit); program contains errors It says algebra.uselect function is not defined. How do I fix this problem? group_concat MAL definition start ------------------------------------------- module kddart; command group_concat(b:bat[:oid,:str]):str address KDDARTgroup_concat comment "Mal kddart"; function group_concat(b:bat[:oid, :str]):str; value := ""; barrier (h, t) := iterator.new(b); value := value + t; value := value + ""; redo (h, t) := iterator.next(b); exit (h, t); return value; end group_concat; function subgroup_concat(b:bat[:oid,:any_1],g:bat[:oid,:oid],e:bat[:oid,:any_2],skip_nils:bit) :bat[:oid,:str]; nw := aggr.count(e); nl := calc.lng(nw); bn := bat.new(:oid, :str, nl); # 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 are aggregating over barrier (grpid, t) := iterator.new(e); # select GID from TID->GID map to get matching TIDs TIDs := algebra.uselect(g,grpid); # get DATA for matching TIDs b_data := algebra.kintersect(b,TIDs); # aggregate grpval := group_concat(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 subgroup_concat;

2 3

Query database inside embedded R
by Stefano Fioravanzo 01 Jun '16

01 Jun '16

Hello, We are trying to transition from a Postgres database to MonetDB. We had a few R function calls, so we decided to try embedded R. One of our R functions is designed to query the postgres database to get some more data to process. The problem, now, is how to integrate this function in Monet. We are using embeddedR to reduce latency, so using MonetDB.R inside the embedded function (if it is even possible) to query the database would be a total waste. Clearly the ultimate solution would be to make all the queries outside the function, but it would be really really hard to achieve this result given how the function is designed. What I am asking is the is some solution to this, like having some way to query the database inside embedded R avoiding the overhead of MonetDB.R. Thank you, Stefano

2 5

Query database inside embedded R
by Stefano Fioravanzo 31 May '16

31 May '16

Hello, We are trying to transition from a Postgres database to MonetDB. We had a few R function calls, so we decided to try embedded R. One of our R functions is designed to query the postgres database to get some more data to process. The problem, now, is how to integrate this function in Monet. We are using embeddedR to reduce latency, so using MonetDB.R inside the embedded function (if it is even possible) to query the database would be a total waste. Clearly the ultimate solution would be to make all the queries outside the function, but it would be really really hard to achieve this result given how the function is designed. What I am asking is the is some solution to this, like having some way to query the database inside embedded R avoiding the overhead of MonetDB.R. Thank you, Stefano

1 0

embedded R function not working
by Stefano Fioravanzo 30 May '16

30 May '16

Hello, I have the following query: CREATE TABLE mytable AS ( SELECT <fields….> FROM table1 AS a, my_R_function(a.field1, ...) AS asd WHERE <some conditions> ) WITH DATA; MonetDB says: SELECT: no such column ‘a.field1’ The issue is in how I call the R function, since when I remove it everything’s just fine. My aim is to call the function for each row of table1. The new table will have a couple of fields from table1 and a bunch of other fields computed from the R function. How am I supposed to do that? Thank you, Stefano

2 1

MonetDB and KNIME compatibility
by Roman S 26 May '16

26 May '16

Hello. Maybe someone have experience with this kind of problem: I’ve downloaded drivers from http://dev.monetdb.org/downloads/Java/Jul2015-SP4/ <http://dev.monetdb.org/downloads/Java/Jul2015-SP4/> added them to KNIME and now, when i’m trying to make something more difficult than just counting rows in table, I see an error: ERROR Database Reader 0:3 SQL Exception reading Object of type "2005": Operation getCharacterStream() currently not supported - all further errors are suppressed and reported on debug level only ERROR Database Reader 0:3 Execute failed: Cell at index 1 is null!

1 0

storing -1 in a tinyint
by Lynn Carol Johnson 25 May '16

25 May '16

Hi all - I apologize for all the emails, but I am banging on monetdb a lot lately. Today I've run into a problem storing a -1 in a "tinyint" column. My java code writes bytes to a binary file. This file will be loaded via binary bulk load into a "tinyint" column. The values in this column range from -1 to 3. I am writing the negative value to the byte file as below: byte missingData = (byte)-1; writer.writeByte(missingData); The data loads correctly into monetdb, and I can print it: sql>\d minusonetest CREATE TABLE "sys"."minusonetest" ( "chr" INTEGER, "pos" INTEGER, "coelorachis" TINYINT ); sql> sql>select * from minusonetest where chr=2 limit 10; +------+------+-------------+ | chr | pos | coelorachis | +======+======+=============+ | 2 | 1 | -1 | | 2 | 2 | -1 | | 2 | 3 | -1 | | 2 | 4 | -1 | | 2 | 5 | -1 | | 2 | 6 | -1 | | 2 | 7 | -1 | | 2 | 8 | -1 | | 2 | 9 | -1 | | 2 | 10 | -1 | +------+------+-------------+ 10 tuples (10.7s) sql> BUT, when I run the select statement below, it tosses me out of the DB and prints errors to the log file: select * from minusone test where chr=2 and coelorachis > -1; The select fails for any value in the "coelorachis > x" clause above (i.e., > -1, > 0, > 1, etc) If I run the above select and have NOT written any negative numbers to the column, all is good. It only fails if there are negative numbers stored in the tinyint column. The merovingian.log file has these messages: 2016-05-25 12:47:23 ERR testDB[21730]: *** Error in `/usr/bin/mserver5': free(): invalid next size (fast): 0x00007fb0ac003920 *** 2016-05-25 12:47:23 ERR testDB[21730]: ======= Backtrace: ========= 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libc.so.6(+0x7d023)[0x7fb0df3bc023] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(GDKfree+0x13)[0x7fb0e1b21233] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(BATsubunique+0x18a9)[0x7fb0e1d23f69] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(BATsubunique+0xbe7)[0x7fb0e1d232a7] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(BATimprints+0x581)[0x7fb0e1cef791] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(BATsubselect+0x4ecf)[0x7fb0e1ac532f] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libbat.so.12(BATthetasubselect+0x14b)[0x7fb0e1ac78eb] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libmonetdb5.so.19(ALGthetasubselect2+0x1c4)[0x7fb0e210db64] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libmonetdb5.so.19(runMALsequence+0xdeb)[0x7fb0e20ac55b] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libmonetdb5.so.19(+0x45e5b)[0x7fb0e20ade5b] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libpthread.so.0(+0x7dc5)[0x7fb0df707dc5] 2016-05-25 12:47:23 ERR testDB[21730]: /lib64/libc.so.6(clone+0x6d)[0x7fb0df43528d] 2016-05-25 12:47:23 ERR testDB[21730]: ======= Memory map: ======== 2016-05-25 12:47:23 ERR testDB[21730]: 00400000-00405000 r-xp 00000000 08:03 22036485 /usr/bin/mserver5 2016-05-25 12:47:23 ERR testDB[21730]: 00604000-00605000 r--p 00004000 08:03 22036485 /usr/bin/mserver5 2016-05-25 12:47:23 ERR testDB[21730]: 00605000-00606000 rw-p 00005000 08:03 22036485 /usr/bin/mserver5 2016-05-25 12:47:23 ERR testDB[21730]: 00606000-00608000 rw-p 00000000 00:00 0 2016-05-25 12:47:23 ERR testDB[21730]: 00892000-01e3e000 rw-p 00000000 00:00 0 [heap] 2016-05-25 12:47:23 ERR testDB[21730]: 7fabab28a000-7fabab29f000 r-xp 00000000 08:03 22020111 /usr/lib64/libgcc_s-4.8.3-20140911.so.1 2016-05-25 12:47:23 ERR testDB[21730]: 7fabab29f000-7fabab49e000 ---p 00015000 08:03 22020111 /usr/lib64/libgcc_s-4.8.3-20140911.so.1 2016-05-25 12:47:23 ERR testDB[21730]: 7fabab49e000-7fabab49f000 r--p 00014000 08:03 22020111 /usr/lib64/libgcc_s-4.8.3-20140911.so.1 2016-05-25 12:47:23 ERR testDB[21730]: 7fabab49f000-7fabab4a0000 rw-p 00015000 08:03 22020111 /usr/lib64/libgcc_s-4.8.3-20140911.so.1 Any ideas? There is no problem if I use Byte.MIN_VALUE instead of -1. This translates to NULL and is selectable. Lynn

1 1