December 2016 - users-list

UDF performance issue
by imad hajj chahine 30 Dec '16

30 Dec '16

Hi, I have a weird behavior when i package some sql code in a function, the performance deteriorate. The same code running directly in select statement is blinking fast. ex: *select date_to_str("Expiry_Date",'%Y-%m-%d') from table*: exec time is 0.72s if i create a function: *CREATE FUNCTION DayBracket(pdate date) returns char(10)* *BEGIN* * return date_to_str(pdate,'%Y-%m-%d');* *end;* *grant execute on function DayBracket to public;* and execute *select DayBracket("Expiry_Date") from table*: exec time is 24s even when i set the limit to 1 its taking the same time, so i guess it should be something related to loading the function. The following function perform much better: *CREATE FUNCTION DayBracketOpt(pdate date) returns char(10)* *BEGIN* * return extract(year from pdate) || '-' || lpad(extract(month from pdate),2,'0') || '-' || lpad(dayofmonth(pdate),2,'0');* *end;* *grant execute on function DayBracketOpt to public;* I also tried to create a Python function: *CREATE FUNCTION pyDayBracket(pdate date) returns string* *LANGUAGE PYTHON {* * from time import strftime, strptime* * return [strftime("%Y-%m-%d", strptime(pdt,"%Y-%m-%d")) for pdt in pdate]* *};* it is taking too long and throwing an exception:buffer size mismatch PS: the pdate array type is detected in pythons as string not as date; Also how to delete a function with dependency, I need to replace some functions which are used in others functions and views. Any flags to turn off dependency check before dropping? Thank you.

4 11

Merry Christmas and Happy 2017
by Ying Zhang 23 Dec '16

23 Dec '16

Dear MonetDB users, The MonetDB team wishes you a very Merry Christmas and Happy New Year! Thank you very much for helping us improving our work! Hope to see you all back in 2017, in which we’re of course going to continue our work, and come up with new MonetDB releases with more features, better performance and improved stability. Best wishes, Ying Zhang (on behalf of the MonetDB team)

2 1

Bug #3878
by Brian Hood 22 Dec '16

22 Dec '16

Hi Guys, I remember this somehow got fixed in the next release or 2 however i am not sure how it got fixed and possibly via a different issue. So it should be closed. https://www.monetdb.org/bugzilla/show_bug.cgi?id=3878 Regards, Brian Hood

1 0

Announcement: New Dec2016 Feature release of MonetDB suite
by Panagiotis Koutsourakis 21 Dec '16

21 Dec '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Dec2016 feature release of the MonetDB suite of programs. More information about MonetDB can be found on our website at <http://www.monetdb.org/>. For details on this release, please see the release notes at <http://www.monetdb.org/Downloads/ReleaseNotes>. As usual, the download location is <http://dev.monetdb.org/downloads/>. Dec 2016 feature release MonetDB5 Server * Removed the zorder module with functions zorder.encode, zorder.decode_x and zorder.decode_y. * Removed command bbp.getHeat(). * Removed bat.setColumn with two arguments and bat.setRole. Use bat.setColumn with one argument instead. * Removed function BKCappend_reverse_val_wrap: it was unused. * The "wrd" type has been removed from GDK and MAL. The type was defined to be a 32 bit integer on 32 bit architectures and a 64 bit integer on 64 bit architectures. We now generally use "lng" (always 64 bits) where "wrd" was used. * Keep a collection of full traces. Each time the SQL user applies the TRACE option, the full json trace is retained within the <dbpath>/<dbname>/sql_traces Build Environment * New packages MonetDB-python2 (Fedora) and monetdb-python2 (Debian/Ubuntu) have been created for Python 2 integration into MonetDB. * With OID size equal to ABI/word size, mserver5 does not need to print the OID size, anymore. * Removed obsolete code associated with long gone static linking option. * Removed configure option --enable-oid32 to compile with 32 bit OIDs on a 64 bit architecture. . * The Perl, PHP, and Python clients, and the JDBC driver each now have their own repositories and release cycles. The Python client is maintained by Gijs Molenaar on Github (https://github.com/gijzelaerr/pymonetdb) the other clients are maintained by CWI/MonetDB on our own server (https://dev.monetdb.org/hg/monetdb-java, https://dev.monetdb.org/hg/monetdb-perl, https://dev.monetdb.org/hg/monetdb-php) MonetDB Common * The tnokey values must now be 0 if it is not known whether all values in a column are distinct. * The 2-bit tkey field in the bat descriptor has been split into two single bit fields: tkey and tunique. The old tkey&BOUND2BTRUE value is now stored in tunique. * Implemented conversion to str from any type (not just the internal types). * VALcopy and VALinit both return their first argument on success or (and that's new) NULL on (allocation) failure. * BATattach now can also create a str BAT from a file consisting of null-terminated strings. The input file must be encoded using UTF-8. * BATattach now copies the input file instead of "stealing" it. * Removed the lastused "timestamp" from the BBP. * Removed batStamp field from BAT descriptor, and removed the BBPcurstamp function. * Removed unused functions BBPhot and BBPcold. * Removed BATderiveTailProps and BATderiveProps. Just set the properties you know about, or use BATsettrivprop. * Removed the macro BUNfirst. It can be replaced by 0. * Changed BATroles by removing the argument to set the name of the head column. * The head column is now completely gone. MonetDB is completely "headless". * The format of the BBP.dir file was simplified. Since the head column is VOID, the only value that needs to be stored is the head seqbase. * BATs now only have a single (logical) name. * The function BATmirror is gone. The HEAD column is always VOID (with a non-nil seqbase) and the TAIL column carries the data. All functions that deal with data work on the TAIL column. * BATkey now works on the TAIL column instead of the HEAD column. * Replaced BATseqbase with BAThseqbase and BATtseqbase, the former for setting the seqbase on the HEAD, the latter for setting the seqbase on the TAIL. * Replaced function BATnew with COLnew with slightly different arguments: the first argument of COLnew is the SEQBASE of the head column (which is always VOID). * The "wrd" type has been removed from GDK and MAL. The type was defined to be a 32 bit integer on 32 bit architectures and a 64 bit integer on 64 bit architectures. We now generally use "lng" (always 64 bits) where "wrd" was used. SQL * Removed functions sys.zorder_encode, sys.zorder_decode_x, and sys.zorder_decode_y. * The experimental recycler code is moved to the attic. * The syntax of bat.new(:oid,:any) has been changed by dropping the superflous :oid. All BATs are now binary associations between a void column and a materialized value column. (except for the internal :bat[:void,:void] representation of simple oid ranged tails.) Bug Fixes * 3357: Implement setQueryTimeout() * 3445: Add support for database name to dotmonetdb file * 3597: SQL to MAL listing looses types * 3973: JDBC hangs * 3976: Performance enhancement to LIKE without wildcards * 4005: Correlated update causes incorrect null constraint violation * 4016: merge table only optimises for point query * 4040: sys.storage call can take a long time * 4047: Segfault when updating a dropped table * 4050: Database corruption when running low on inode * 4057: missing bulk operations between constant and bat * 4061: SIGSEGV in candscan_lng * 4066: Deadlocked monetdbd * 6068: Error message about incompatible BBP version should be clearer * 6069: query with union all silently crashes * 6070: setting negative session query timeout should not be possible/allowed * 6071: where clause with cast and floor fails to sigsegv * 6072: Bind to UPD delta column does not get/show type information in EXPLAIN * 6073: Missing type information for constants in MAL explain * 6074: SET ROLE command does not work * 6075: gdk_calc.c:13113: BATcalcifthenelse_intern: Assertion `col2 != NULL' failed. * 6076: rel_optimizer.c:5426: rel_push_project_up: Assertion `e' failed. * 6077: mserver5: rel_optimizer.c:5444: rel_push_project_up: Assertion `e' failed. * 6078: rel_bin.c:2402: rel2bin_project: Assertion `0' failed. * 6084: Merge table point to wrong columns if columns in partition tables are deleted * 6108: monetdb5-sql sysv init script does not wait for shutdown * 6114: segfault raised in the query rewriter due to a null pointer * 6115: Assertion hit in the codegen * 6116: Codegen does not support certain kind of selects on scalar subqueries * 6117: Assertion hit in the query rewriting stage during the push down phase * 6118: SIGSEGV in strPut due to shared heap * 6119: Assertion hit in the MAL optimiser on a complex query * 6120: QUANTILE() treats NULL as if it is zero * 6121: SELECT a.col IN ( b.col FROM b ) FROM a statements with no error but no result * 6123: Assertion hit in the codegen #2 * 6124: CASE <column> WHEN NULL THEN 0 ELSE 1 END returns wrong result * 6125: Stack overflow in the query rewriter with a query having an OR condition and a nested SELECT subexpression * 6126: batcalc.== can't handle void BATs * 6139: Debian libmonetdb13 conflicts with libmonetdb5-server-geom

1 0

Returning empty results from an embedded python table function.
by Joseph D'silva 20 Dec '16

20 Dec '16

Hi All, I am trying to return an "empty" result set from an embedded python function (as in some situations I will not have valid rows to produce in the output). However the very many attempts of trying to return an "empty" result from the function have failed (as is documented in the example below). How do you get it done ? Thanks. DROP FUNCTION p_results_exp01; CREATE FUNCTION p_results_exp01() RETURNS TABLE(val1 STRING, val2 INTEGER) LANGUAGE PYTHON { #Unsupported result object. Expected either a list, dictionary, a numpy array, a numpy masked array or a pandas data frame, but received an object of type "<class 'NoneType'>" #return None; #Errors #Expected a return value with name "val1", but this key was not present in the dictionary. #return {}; #Errors #Error converting dict return value "val1": MALException:pyapi.eval:Unsupported result object. Expected either a list, dictionary, a numpy array, a numpy masked array or a pandas data frame, but received an object of type "<class 'NoneType'>". #return {'val1':None, 'val2':None}; #Errors #Error converting dict return value "val1": MALException:pyapi.eval:An array of size 0 was returned, yet we expect a list of 1 columns. The result is invalid.. #return {'val1':[], 'val2':[]}; #return {'val1':['value_1'], 'val2':[1]}; #Works, as it has value. }; SELECT * FROM p_results_exp01(); Joseph

2 3

Python integration
by Imad Hajj Chahine 17 Dec '16

17 Dec '16

Hi, I need to calculate weighted quartile from a list of values and their counts, and all solutions that i found revolves around calculating the cumulative count. Since monetdb lacks the sum over window function, I want to create an aggregate function in python to return the weighted quartile after computing the cumulative count. As I understand there is 3 branches: release, candidate and dev. Currently the release version is june2016, candidate is dec 2016, dev is the default. Is the python integration only supported on default branch? Any plan to move it to stable branch? Thank you.

3 3

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

min() vs median() for string columns
by Eyal Rozenberg 13 Dec '16

13 Dec '16

When I try to get the min() or the max() of a string column (whose type is CHAR(2) ) - that works; but when I try to get the median() - I get an error about not being able to convert a value (say, 'AA') to type bte. Is this intended behavior? A bug? Perhaps something I might be doing wrong? PS - In case it matters: * I'm using v11.23.7 * I'm using the USDT on-time flight data, in a DB you can setup using the script here: https://github.com/eyalroz/usdt-ontime-tools

1 0

MonetDB unpredictable performance
by Kevork Vartanian 08 Dec '16

08 Dec '16

Hi, I have a few questions and hopefully someone will be able to answer me and put me in the right direction. - When we're having performance issues, the only way we can resolve them, is by dumping the data again, sorted by datetime, and after that the queries are fast. When we do this, the table enables sorting on the datetime column and that's why the queries are fast as there won't be a full table scan. As soon as we insert new data (always in the same sorted order), firstly the table is losing the sorting and secondly the queries become extremely slow - We find it very unpredictable, and somehow not very deterministic, especially when it comes to indexing, sorting and log file consumption. We can't control those, it seems - The read ahead log is growing uncontrollably and we don't really know when it's committing - When running the queries (after the table loses the sorting), we can see it's writing too much data (storage usage grows significantly when query is still running) and that's most probably because it is doing a full table scan - Even if we restart the engine, it takes a while for it to be back, as it recommits the read ahead log which takes time, and it doesn't make a difference with the sorting and therefore the queries are still slow even after a restart Is there a way we can further optimise so we don't have these issues? We have more than 600 million entries and when the datetime field is sorted, the queries are quick. The only way we can force the sorting is when we dump the data again, but we can't really do that all the time and we need to insert new data at least once every night. It's an Ubuntu 14.04.4 LTS instance on AWS with 2 CPU's and 16 GB of RAM (and SSD drives). Thank you. Regards, Kevork Vartanian Technical Solutions Consultant kevork.vartanian(a)innotta.com.au | +61 406 776 789 | www.innotta.com.au

2 1

null value from python function
by Stefano Fioravanzo 05 Dec '16

05 Dec '16

Hello, Some months ago I reported a problem when returning null values from an embedded py function (may they be inside arrays or data frames). Indeed, the null values cannot be queried using sql like: SELECT * FROM t WHERE v IS NULL the above query does not return any result, but selecting the row mclient presents a ‘null’ values in the cell. I am using a development build of a couple of months ago, I would like to know if this issue has been resolved or not. Thank you Stefano

2 1