June 2016 - users-list

Segfault when using current_date() in SELECT query
by Dennis Pallett 10 Jun '16

10 Jun '16

Hi all, We seem to be getting a segfault on our databases when we try to use the CURRENT_DATE() function in a simple select query. For example: SELECT CURRENT_DATE(), groep.* FROM groep; Is this a (known) bug in MonetDB? Best regards, Dennis Pallett

1 0

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

function IFNULL equivalent?
by Alberto Ferrari 09 Jun '16

09 Jun '16

Hello everybody, I know this question was alreaday made, but I can´t find an answer... Is there a function equivalent to mysql IFNULL() ? If no... has someone created any similar user function? Thanks in advanced

2 2

Constant segmentation faults
by Nikita Salnikov-Tarnovski 07 Jun '16

07 Jun '16

Hello. I am evaluating MonetDB to use in our project, but so far have some real problems with MonetDB constantly having segmentation faults. Here is my setup: I am using AWS EC2 instance with 16GB of RAM, monetdb farm is location on ESB volume. Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://ip-10-0-0-47:50000/mysql-copy' In there I have several tables, among them is one table I am most interested in, problemsview, with total columnsize of 120GB, largest column is around 11GB. The moment I execute “select count(1) from problemsview;” I get segmentation fault: mserver5[1300]: segfault at 0 ip 00007f86a0ca0712 sp 00007f869b6faa80 error 4 in lib_sql.so[7f86a0c72000+199000] Next select * from problemsview limit 10; wait for 20-30 seconds, and then segmentation fault: mserver5[1313]: segfault at 18 ip 00007f68cf2a0726 sp 00007f68ce14ea80 error 4 in lib_sql.so[7f68cf272000+199000] So essentially I cannot do anything, I always get segmentation fault. Can this be fixed somehow? Thank you in advance, Nikita Salnikov-Tarnovski Plumbr Founder and Master Developer @JavaPlumbr/@iNikem

3 5

segmentation fault with loopback query
by Stefano Fioravanzo 06 Jun '16

06 Jun '16

Hello, I am trying to execute a loopback query in embedded python: CREATE FUNCTION myfunc() RETURNS TABLE(n STRING) LANGUAGE PYTHON { res = _conn.execute("SELECT max(d) FROM mytable;") result = dict() result['n'] = str(res) return result }; When accessing the object ‘res’, mserver5 outputs ‘segmentation fault’ and exits. I think that the issue is in using the max() function in the query, if I leave that out everything works fine. The function: CREATE FUNCTION myfunc() RETURNS TABLE(n STRING) LANGUAGE PYTHON { res = _conn.execute("SELECT d FROM mytable;") result = dict() result['n'] = str(res) return result }; outputs a correct dictionary. Stefano

3 5

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

MonetDB-11.21.19 Compilation failure on JDBC tests
by Ashish Kumar Singh 04 Jun '16

04 Jun '16

All, I am trying to compile monetdb MonetDB-11.21.19 from sources where its failing with error below: prepare: [echo] Debug is true, optimise is false compile_mcl: [echo] Compiling MCL driver: [copy] Copying 1 file to /opt/MonetDB-11.21.19/java/src/main/java/nl/cwi/monetdb/jdbc [echo] Configured build for the 2.19 (Liberica) edition driver compile_jdbc: [echo] Compiling JDBC driver [javac] Compiling 1 source file to /opt/MonetDB-11.21.19/java/tests/jar_jdbctests [javac] This version of java does not support the classic compiler; upgrading to modern jar_jdbc: BUILD FAILED /opt/MonetDB-11.21.19/java/tests/build.xml:75: The following error occurred while executing this line: /opt/MonetDB-11.21.19/java/build.xml:96: java.io.FileNotFoundException: /opt/MonetDB-11.21.19/java/tests/jar_jdbctests/META-INF/services/java.sql.Driver (No such file or directory) Below is my configure output : I am compiling with JDK 8 latest , and ant 1.7 MonetDB is configured as follows: * Compilation specifics: Host: x86_64-unknown-linux-gnu Compiler: gcc (gcc-4.4.7; gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-17) Copyright (C) 2010 Free Software Foundation, Inc. This is free software; see the source for copying conditions. There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.) CFLAGS: -g -O2 $(X_CFLAGS) X_CFLAGS: LDFLAGS: * Enabled/disabled build options: strict is disabled (by default) assert is disabled (by default) debug is disabled (by default) optimize is disabled (by default) developer is disabled (by default) instrument is disabled (by default) profile is disabled (by default) * Enabled/disabled components: gdk is enabled monetdb5 is enabled sql is enabled geom is disabled (geos-config not found) console is enabled gsl is disabled (gsl library not found) fits is disabled (cfitsio library not found) netcdf is disabled (netcdf library not found) microhttpd is disabled (by default) rintegration is disabled (libR library not found) odbc is disabled (unixODBC library required for ODBC driver) jdbc is enabled control is enabled testing is enabled developer is disabled (by default) strict is disabled (by default) debug is disabled (by default) assert is disabled (by default) optimize is disabled (by default) * Available features/extensions: atomic_ops = no (atomic_ops library not found) bz2 = no (bz2 library not found) curl = no (libcurl not found) getaddrinfo = yes gsl = no (gsl library not found) java = yes java_control = yes java_jdbc = yes libxml2 = yes openssl = yes pcre = yes perl = yes pthread = yes python2 = yes python3 = no (Python 3 executable not found) readline = no (readline header file not found) rubygem = no (no rubygem executable found) samtools = no (samtools/bam.h header not found) sphinxclient = no (sphinxclient.h header not found) unixodbc = no (odbcinst.h not found) valgrind = no (by default) zlib = yes * Important options: OID size: 64 bits largest integer type: 128 bits largest decimal type: 38 digits

1 0

query json from embedded py
by Stefano Fioravanzo 01 Jun '16

01 Jun '16

Hello, I am trying to query a table with a JSON field with a loopback query in embedded python. I get this error: SQL Query Failed: MALException:pyapi.eval:Unsupported SQL Type: son Is JSON not supported? Do I have to store the JSON object as a string in case I want to query it from python? Thank you, Stefano

2 1

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