August 2011 - users-list

[MonetDB-users] Current Pathfinder list/team
by Anton Kolev 21 Aug '11

21 Aug '11

Gentlemen, Which is the proper current list (or personal contact) for the Pathfinder related questions? Are there recent work after Mar2011 branch [1]? Thank you in advance, Anton [1] http://dev.monetdb.org/hg/MonetDB/file/932a58fecbde/pathfinder

2 1

[MonetDB-users] Does MonetDB support regular expression predicates?
by Viktor Rosenfeld 21 Aug '11

21 Aug '11

Hi, does MonetDB support regular expression matching on strings? It's supposedly part of SQL:99, but I couldn't find anything about this on the homepage or the mailing list. I only found the Data Manipulation reference, which lists LIKE predicates. Cheers, Viktor

2 1

[MonetDB-users] Are there any unit test for each checkin/fix
by Ken Cheng 20 Aug '11

20 Aug '11

Hi All, Are there any unit test for regression test for each checkin/fix? Seems I can not find any test related source check in history. Yours sincerely, Ken Cheng

2 1

[MonetDB-users] Compiling MonetDB with JDBC on OS X 10.6
by Viktor Rosenfeld 18 Aug '11

18 Aug '11

Hi, I've tried building the latest MonetDB sources on OS X 10.6.8 and it fails with the following error message: checking for ant... /usr/bin/ant checking for java... /usr/bin/java checking for javac... /usr/bin/javac checking for jar... /usr/bin/jar checking for javadoc... /usr/bin/javadoc configure: error: MonetDB JDBC requires ant and Java 1.5 I used the following build options: ./configure --prefix=/Users/he-sk/unix --enable-strict --disable-assert --disable-debug --enable-optimize --enable-jdbc Searching on the mailing list, I found a post saying that the MonetDB JDBC driver will not build with Java 1.6 (the default on OS X 10.6), because the JDBC API changed from 1.5 to 1.6. However, the post is from September 2008, almost 3 years ago. Is this still the case? Or am I running into a different problem? List post: http://old.nabble.com/Installation-Problems-to19607306.html#a19645299 If MonetDB JDBC does require 1.5, I'm thinking of following the steps outlined here: http://wiki.oneswarm.org/index.php/OS_X_10.6_Snow_Leopard Does anybody have experience with this procedure or does anybody know an alternative? Also, I'm about to upgrade to OS X Lion (10.7). Is anybody running the MonetDB JDBC driver on Lion yet? Are you running it from sources or did you install the binary MonetDB distribution? Cheers, Viktor

2 2

[MonetDB-users] How to update one table from another
by Eugene Prokopiev 12 Aug '11

12 Aug '11

Hi, I have two tables: table1 ----------- id data ----------- 0 0 1 1 2 2 ----------- table2 ----------- id data ----------- 2 2 3 3 ----------- I need to increment data in big table1 by data from small table2 if it exists or insert absent data from table2 to table1. So result must looks like: table1 ----------- id data ----------- 0 0 1 1 2 4 3 3 ----------- I used loop by table2 rows and inserts or updates table1 in this loop in PostgreSQL. What is the most effective way to do it in MonetDB? -- Thanks, Eugene Prokopiev

2 2

[MonetDB-users] JOIN for functions
by Eugene Prokopiev 09 Aug '11

09 Aug '11

Hi, Is it possible to join functions which returns tables? I've got error: sql>select sumdate from summary_ip_in('wan') limit 1; +------------+ | sumdate | +============+ | 2-08-11 | +------------+ 1 tuple (1m 14s) sql>select sumdate from summary_ip_out('wan') limit 1; +------------+ | sumdate | +============+ | 2-08-11 | +------------+ 1 tuple (10m 59s) sql>select sumdate from summary_ip_in('wan') inner join summary_ip_out('wan') on summary_ip_in.sumdate = summary_ip_out.sumdate limit 1; SELECT: no such column 'summary_ip_in.sumdate' sql>select sumdate from summary_ip_in('wan') inner join summary_ip_out('wan') on sumdate = sumdate limit 1; SELECT: identifier 'sumdate' ambiguous What's wrong? -- Thanks, Eugene Prokopiev

2 2

[MonetDB-users] Check for inet equals
by Eugene Prokopiev 06 Aug '11

06 Aug '11

Hi, How to select from table by value in column with INET type? For example, I have table: $ msqldump -d billing -- msqldump dump database Mon Aug 1 11:03:13 2011 -- MonetDB v11.3.3, 'b0' START TRANSACTION; SET SCHEMA "sys"; CREATE TABLE "sys"."transfer_ip" ( "packetdate" TIMESTAMP, "proto" INTEGER, "src" INET, "sport" INTEGER, "sintf" VARCHAR(15), "dst" INET, "dport" INTEGER, "dintf" VARCHAR(15), "packets" BIGINT, "bytes" BIGINT, "prefix" VARCHAR(5), "processed" BOOLEAN ); COMMIT; COPY 10293965 RECORDS INTO "sys"."transfer_ip" FROM stdin USING DELIMITERS '\t','\n','"'; ^C I tried to select in such ways: # mclient -d billing Welcome to mclient, the MonetDB/SQL interactive terminal (Apr2011-SP1) Database: MonetDB v11.3.3, 'billing' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select * from transfer_ip where src = '10.7.1.10' limit 10; SQLException:str_cast:value too long for type (var)char(9) sql>select * from transfer_ip where src = '10.7.1.10':inet limit 10; unexpected symbol (:) sql>select * from transfer_ip where src << '10.7.1.10' limit 10; types inet(0,0) and tinyint(8,0) are not equal for column 'src' What it the right way? -- Thanks, Eugene Prokopiev

2 5

[MonetDB-users] openssl not found when it is present
by Medha Atre 04 Aug '11

04 Aug '11

I am trying to compile MonetDB-11.3.7 on Ubuntu 10.04 distribution (2.6.32-33 64 bit kernel). It fails with "openssl" not found error: I am running configure script with following options: $ ./configure --prefix=/work2/MonetDB-install-11.3.7 --enable-optimize --enable-sql --enable-monetdb5 <snip> checking for iconv... yes checking for working iconv... yes checking for iconv declaration... extern size_t iconv (iconv_t cd, char * *inbuf, size_t *inbytesleft, char * *outbuf, size_t *outbytesleft); checking for library containing uuid_generate... no checking for openssl... no configure: error: OpenSSL library not found but required for MonetDB5 $ openssl libraries are present on my system. I tried: $ cd /lib $ ll *ssl* -rw-r--r-- 1 root root 333856 2011-02-09 20:50 libssl.so.0.9.8 $ $ $ sudo apt-get install openssl Reading package lists... Done Building dependency tree Reading state information... Done openssl is already the newest version. 0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded. $ Can someone please let me know if I am missing any step or need to do something else for the configure script to find SSL libraries? Thanks in anticipation. Medha

4 5

[MonetDB-users] Long running query with count(distinct ...)
by Eugene Prokopiev 02 Aug '11

02 Aug '11

Hi, I have table with 81 887 010 rows. Select count from this table works fine, but I can't wait for the results of select count(distinct ...) from this table. Why can it be? sql>explain select count(src) from transfer_ip; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +==================================================================================================================================================================================+ | function user.s1_1{autoCommit=true}():void; | | barrier _55 := language.dataflow(); | | _2 := sql.mvc(); | | _39:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",0,0,2); | | _43 := algebra.selectNotNil(_39); | | _35:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",2,0,2); | | _45 := algebra.kdifference(_43,_35); | | _41 := algebra.selectNotNil(_35); | | _47 := algebra.kunion(_45,_41); | | _6:bat[:oid,:oid] := sql.bind_dbat(_2,"sys","transfer_ip",1); | | _9 := bat.reverse(_6); | | _49 := algebra.kdifference(_47,_9); | | _51 := aggr.count(_49); | | _40:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",0,1,2); | | _44 := algebra.selectNotNil(_40); | | _38:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",2,1,2); | | _46 := algebra.kdifference(_44,_38); | | _42 := algebra.selectNotNil(_38); | | _48 := algebra.kunion(_46,_42); | | _52 := aggr.count(_48); | | _10:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",1); | | _11 := algebra.selectNotNil(_10); | | _53 := aggr.count(_11); | | _50 := mat.pack(_51,_52,_53); | | _54 := algebra.selectNotNil(_50); | | _14 := aggr.sum(_54); | | exit _55; | | sql.exportValue(1,"sys.transfer_ip","L4","wrd",64,0,6,_14,""); | | end s1_1; | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 29 tuples (12.925ms) sql>explain select count(distinct src) from transfer_ip; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | mal | +==================================================================================================================================================================================+ | function user.s2_1{autoCommit=true}():void; | | barrier _56 := language.dataflow(); | | _2 := sql.mvc(); | | _42:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",0,0,2); | | _46 := algebra.selectNotNil(_42); | | _38:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",2,0,2); | | _48 := algebra.kdifference(_46,_38); | | _44 := algebra.selectNotNil(_38); | | _50 := algebra.kunion(_48,_44); | | _6:bat[:oid,:oid] := sql.bind_dbat(_2,"sys","transfer_ip",1); | | _9 := bat.reverse(_6); | | _52 := algebra.kdifference(_50,_9); | | _53 := bat.reverse(_52); | | _43:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",0,1,2); | | _47 := algebra.selectNotNil(_43); | | _41:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",2,1,2); | | _49 := algebra.kdifference(_47,_41); | | _45 := algebra.selectNotNil(_41); | | _51 := algebra.kunion(_49,_45); | | _54 := bat.reverse(_51); | | _10:bat[:oid,:inet] := sql.bind(_2,"sys","transfer_ip","src",1); | | _11 := algebra.selectNotNil(_10); | | _55 := bat.reverse(_11); | | _14 := mat.pack(_53,_54,_55); | | _15 := algebra.kunique(_14); | | _16 := bat.reverse(_15); | | _17 := aggr.count(_16); | | exit _56; | | sql.exportValue(1,"sys.transfer_ip","L5","wrd",64,0,6,_17,""); | | end s2_1; | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 30 tuples (1.238ms) -- Thanks, Eugene Prokopiev

3 4

[MonetDB-users] Vacuum command missing
by Eugene Prokopiev 01 Aug '11

01 Aug '11

Hi, I can't find VACUUM command in http://www.monetdb.org/Documentation, I see only vacuum cleaning algorithm mention in http://www.monetdb.org/Documentation/Manuals/SQLreference/Transactions. So, how can phisicaly delete rows after marking them as deleted? -- Thanks, Eugene Prokopiev

2 3