- users-list - monetdb.org

varchar split function
by imad hajj chahine 17 Dec '13

17 Dec '13

Hi, I am trying to implement a function that split a varchar and returns a table with the splitted values. this is what i have so far but i dont know how to complete the implementation. CREATE FUNCTION EV_SPLIT(stringtosplit varchar(255), chartospliton char) RETURNS TABLE (id int, val varchar(255), hashval BIGINT) BEGIN --if(EV_TABLEEXISTS('tmp_evsplitres') = 0) --then -- create local temporary table tmp_evsplitres (id int, val varchar(255), hashval BIGINT); --end if; declare idx int, tmpVal varchar(255); set idx = 0; --start transaction; while (position(chartospliton in str)>0) do set tmpVal = substring(str, 0, position(',' in str)-1); insert into tmp_evsplitres values(idx, tmpVal, hash(tmpVal)); set str = substring(str, position(',' in str), length(str)); set idx = idx + 1; end while; insert into tmp_evsplitres values(idx, str, hash(str)); RETURN TABLE ( select * from tmp_evsplitres ); --commit; END;

2 1

Inline function much faster than UDF in C?
by Alastair McKinley 13 Dec '13

13 Dec '13

Hi everyone, I have only been using MonetDB for a few weeks now and I have come across a very unexpected result when using User Defined Functions in C. I have a query which does essentially this: TABLE small_table ( int col1; float p; ); TABLE big_table ( int col1; int col2; float p; ); SELECT col1,1+SUM(MY_FUNCTION(big.p,small.p)) FROM small_table small inner join big_table big on big.col2 == small.col1 group by big.col1; My function is f(x,y) = 0.7213*(x*ln(x/x+y) + y*ln(y/x+y)) I wrote an optimised version of this function using sse intrinsics in C and compiled it into MonetDB. To my surprise, I discovered that writing this function inline in the query like so: 1+SUM(0.7213475*(big.p*LOG(big.p/(big.p+small.p))+small.p*LOG(small.p/(big.p+small.p)))) is a lot faster! The query takes approx 2 seconds with the C version and less than 0.5 seconds with the inline version in SQL! Can anyone explain this strange result? Best regards, Alastair

4 12

BUG 3259 should be re-opened
by Roberto Cornacchia 12 Dec '13

12 Dec '13

Hello, I cannot re-open bug 3259, but I believe t should be. After the bug had been fixed by Niels, it seems to have been re-introduced. The changeset below (after the fix) approves a MAL plan that in fact is the wrong one the original report refers to: the join has again been replaced by a CROSS-PRODUCT followed by inequality selections. Changeset d0b65a3e8eb6<http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d0b65a3e8eb6>, made by Stefan Manegold <Stefan.Manegold(a)cwi.nl> in the MonetDB repo, refers to this bug. For complete details, see http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=d0b65a3e8eb6 Changeset description: between.Bug-3259: approved new MAL plan after changeset 79e66f677113 <http://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=79e66f677113>

1 1

create a database
by Gaetano Mele 12 Dec '13

12 Dec '13

Hello can you tell me how do i create a new database with monetdb? Thanks

4 7

Dump database with user != monetdb
by Klaus Kruse 10 Dec '13

10 Dec '13

Hello, I posted my issues with dumping a database from MonetDB a while ago. The issue is still ongoing. It reduces to the fact, that no other user than the default monetdb-user has enough rights to dump a database. I played around with the various tables regarding users and schemas (sys.auths , sys.user_role ) but it's still failing on 'sys.db_user_info' : msqldump -u dump test password: -- msqldump dump database Tue Dec 10 17:19:43 2013 -- MonetDB v11.15.7 (Feb2013-SP2), 'mapi:monetdb://dev01.picalike.corpex-kunden.de:50000/test' START TRANSACTION; MAPI = (dump) /tmp/.s.monetdb.50000 QUERY = SELECT "name" FROM "sys"."auths" WHERE "name" NOT IN (SELECT "name" FROM "sys"."db_user_info") AND "grantor" <> 0 ORDER BY "name" ERROR = !SELECT: access denied for dump to table 'sys.db_user_info' So, my question to all subscribers: Does anybody managed to create another user with full rights to dump the database and when so, how? Any advice is greatly apprecitated. Best regards Klaus Kruse -- Corpex Internet GmbH * Schauenburgerstrasse 6 * D-20095 Hamburg Tel: +49 40 822268-0 * Fax: +49 40 822268-100 * http://www.corpex.de/ HRB 78752, Amtsgericht Hamburg, Sitz: Hamburg, USt.-Id: DE 213001412 Geschäftsführer: Daniel Hanelt

1 0

Moving monetdb data to another directory
by Rémi Charpentier 10 Dec '13

10 Dec '13

I am trying to change the default data directory of MonetDB. I installed monetdb using the ubuntu package, by default the data is stored in: /var/lib/monetdb I am running out of space, and I like to migrate the data to another folder. Does anyone know how to do that? I would welcome a solution that doesn't involve compiling monetdb from sources... Thanks in advance Remi

2 2

Listing database objects in schema
by Lee Hachadoorian 06 Dec '13

06 Dec '13

Is there a way to list tables in a particular schema or even in all schemas? \d and \dt seem to only return user tables in the sys schema. Also, what does \ds return? I have \dS system tables \dt user tables \dv views \ds ????? \df functions \dn schemas Best, --Lee -- Lee Hachadoorian Asst Professor of Geography, Dartmouth College http://geospatial.commons.gc.cuny.edu/<http://freecity.commons.gc.cuny.edu/>

2 2

Bulk loading file with non-UTF8 characters
by Lee Hachadoorian 06 Dec '13

06 Dec '13

I need to bulk load some files with characters like ñ and é. I would do this using the LATIN1 encoding. I read that bulk loading from file assumes UTF8, but bulk loading from STDIN will use encoding of mclient. I would like to know: (a) How do I determine and change the client encoding? (b) What is an example for piping STDIN to mclient? Regarding the latter, I tried: cat /my/file.csv | mclient -d mydb -u monetdb -s "COPY INTO mytable FROM (STDIN) USING DELIMITERS ',', '\n' , '"' NULL AS '';" I wasn't sure how the embedded " would be handled, but the terminal basically became unresponsive until I ctl-C'ed out of it. Note the the file was similar in size to several others (without non-UTF8 characters) that loaded in seconds within mclient. Best, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://geospatial.commons.gc.cuny.edu http://freecity.commons.gc.cuny.edu

3 4

Load file MonetDB Client
by Gaetano Mele 06 Dec '13

06 Dec '13

Hello i'm an italian student. I am loading a file .sql with MonetDB Client. But, during the file's loading, happens that the Client suddenly stops working. Why does this happen? Thanks

3 6

Strange behavior: dumping results from SELECT query (or inserting it into a table) does not contain the proper data until table is dumped/reloaded
by Bas Kaptijn 06 Dec '13

06 Dec '13

Hi, I can reproduce a MonetDB 2013 - SP5 database which gets in such a state the following first query does not return a result with a record containing '20130510199500000099' as value for the field "ida" whereas the second query (with only a little extra at the end) does while clearly this should never be the case. Moreover: the problem disappears when I dump, drop and reload the table analysis_04_1 ! testdpe99.sql : SELECT A.identificatierecord AS idA, B.identificatierecord AS idB, A.identificatierecord||'-'||B.identificatierecord AS ctxid, 'D.P.E.99.99' AS testcode FROM analysis_04_1 AS A INNER JOIN analysis_04_1 AS B ON A.instellingscode = B.instellingscode AND A.verzekerdennummer = B.verzekerdennummer WHERE NOT A.identificatierecord = B.identificatierecord AND (B.Zorgtypecode = '11' OR B.Zorgtypecode = '21') AND ( (A.BegindatumPrestatie <= B.EinddatumPrestatie AND A.EinddatumPrestatie >= B.BegindatumPrestatie AND NOT ( A.EinddatumPrestatie > A.BegindatumPrestatie AND B.EinddatumPrestatie > B.BegindatumPrestatie AND ( A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie ) )) OR (A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie OR A.BegindatumPrestatie=(B.EinddatumPrestatie + INTERVAL '1' day) OR B.BegindatumPrestatie=(A.EinddatumPrestatie + INTERVAL '1' day) ) ); testdpe99-2.sql : SELECT A.identificatierecord AS idA, B.identificatierecord AS idB, A.identificatierecord||'-'||B.identificatierecord AS ctxid, 'D.P.E.99.99' AS testcode FROM analysis_04_1 AS A INNER JOIN analysis_04_1 AS B ON A.instellingscode = B.instellingscode AND A.verzekerdennummer = B.verzekerdennummer WHERE NOT A.identificatierecord = B.identificatierecord AND (B.Zorgtypecode = '11' OR B.Zorgtypecode = '21') AND ( (A.BegindatumPrestatie <= B.EinddatumPrestatie AND A.EinddatumPrestatie >= B.BegindatumPrestatie AND NOT ( A.EinddatumPrestatie > A.BegindatumPrestatie AND B.EinddatumPrestatie > B.BegindatumPrestatie AND ( A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie ) )) OR (A.BegindatumPrestatie = B.EinddatumPrestatie OR B.BegindatumPrestatie = A.EinddatumPrestatie OR A.BegindatumPrestatie=(B.EinddatumPrestatie + INTERVAL '1' day) OR B.BegindatumPrestatie=(A.EinddatumPrestatie + INTERVAL '1' day) ) ) AND A.identificatierecord = '20130510199500000099'; The table analysis_04_1 is the result of a SELECT query after which it is altered and updated quite some times adding extra fields etc. No errors seem to be reported in that process. Before reloading the table analysis_04_1 I get: bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99-2.sql | grep 20130510199500000099 | 20130510199500000099 | 20130313587700000003 | 20130510199500000099-20130313587700000003 | D.P.E.99.99 | bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99.sql | grep 20130510199500000099 bkaptijn@DevRef1:~/DDDB$ But after reloading the table (using in mclient: \> dumpfile, \D analysis_04_1, DROP TABLE analysis_04_1;, \< dumpfile): bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99.txt | grep 20130510199500000099 | 20130510199500000099 | 20130313587700000003 | 20130510199500000099-20130313587700000003 | D.P.E.99.99 | bkaptijn@DevRef1:~/DDDB$ mclient -d cacis < ../testdpe99-2.txt | grep 20130510199500000099 | 20130510199500000099 | 20130313587700000003 | 20130510199500000099-20130313587700000003 | D.P.E.99.99 | bkaptijn@DevRef1:~/DDDB$ Is there a known issue which does play a role here or what can I do to investigate this further / give more information? Sincerely, Bas Kaptijn

1 0