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 … [View More]

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 … [View More]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 [View Less]

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. … [View More]

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 … [View More]

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 … [View More]

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