- users-list - monetdb.org

COPY INFO Exception
by ping gao 高平(0) 03 Jan '17

03 Jan '17

Hi ALL I encountered a problem using a COPY INFO The command to execute: [hotel@02 data_visual_monetdb]$ mclient -u monetdb -d hotel-revenue -s "COPY 1727300 RECORDS INTO hotel_revenue_octopus_hotel_info FROM '/home/ping.gao/data_visual_monetdbinfo.data' USING DELIMITERS ',','\\n'<smb://n'>,'\"'" password: Failed to import table Leftover data '"0”' Data Format(Line separator —> \n): “test”,”test”,”test”,”test”

2 2

File System Backups
by Vincent Sheffer 30 Dec '16

30 Dec '16

I’m trying to do automated incremental backups following these instructions: https://www.monetdb.org/Documentation/UserGuide/FastDumpRestore My question is whether why isn’t it enough to just stop the DB? Is the waiting for open sessions a courtesy or is it required that no statements are executing prior to shutdown? Is it similar for the shutdown command? It gives people in a session time to exit or something? Again, these are to be automated at a time of night that queries should not be being executed. Bottom line question: is it enough to stop and lock a database to ensure the binary backup will have no errors that prevent the backup from being restored or are the other steps required? Thanks, Vince The information transmitted, including any attachments, is intended only for the individual or entity to which it is addressed, and may contain confidential and/or privileged information. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by individuals or entities other than the intended recipient is prohibited, and all liability arising therefrom is disclaimed. If you have received this communication in error, please delete the information from any computer and notify the sender.

2 1

table renaming
by Stefano Fioravanzo 30 Dec '16

30 Dec '16

Hello, In this blog post https://www.monetdb.org/blog/monetdb-autumn-2016-feature-release-outlook <https://www.monetdb.org/blog/monetdb-autumn-2016-feature-release-outlook> it was mentioned the possibility of renaming tables. It is possible with the new Dec2016 release? Thank you Stefano

2 1

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

Database dropping connections frequently with proxying.
by Joseph D'silva 01 Dec '16

01 Dec '16

Running on Ubuntu 16.04 LTS, database and clients run on the same system. I am using the Dec2016 branch, and noticed that the database connections get dropped randomly, but in high frequency. This error occurs irrespective for whether I use mclient, python package (pymonetdb) or the JDBC driver from the site. >From what I could figure if I use the forward=redirect option instead of the default proxy, the frequency reduces drastically, but is still present (if I leave the mclient connection idle for like 5 minutes it will drop - is there an idle time out somewhere ? Also is there a way to force a network connection locally instead of unix socket ? ). Sample of some messages For the default proxy option =========================== >From merovingian.log 2016-12-01 10:59:33 MSG merovingian[29445]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying Python error due to dropped connection. >>> rows=cursor.execute("SELECT 'HELLO';"); Traceback (most recent call last): File "<stdin>", line 1, in <module> File "/usr/local/lib/python3.5/dist-packages/pymonetdb/sql/cursors.py", line 164, in execute block = self.connection.execute(query) File "/usr/local/lib/python3.5/dist-packages/pymonetdb/sql/connections.py", line 133, in execute return self.command('s' + query + ';') File "/usr/local/lib/python3.5/dist-packages/pymonetdb/sql/connections.py", line 138, in command return self.mapi.cmd(command) File "/usr/local/lib/python3.5/dist-packages/pymonetdb/mapi.py", line 208, in cmd self._putblock(operation) File "/usr/local/lib/python3.5/dist-packages/pymonetdb/mapi.py", line 310, in _putblock self._putblock_inet(block) File "/usr/local/lib/python3.5/dist-packages/pymonetdb/mapi.py", line 321, in _putblock_inet self.socket.send(flag) BrokenPipeError: [Errno 32] Broken pipe JDBC Error (I am using PyCharm to execute some SQLs) [08000] Connection to server lost! (mserver still alive?) On the other hand, mclient does not show any error message, tried -X option, but no message when it dies. It just dies while executing a command (probably because the connection is dropped) A peek at netstat shows that the connections disappear from it before mclient dies. $ netstat -anp | grep 50000 tcp 0 0 127.0.0.1:50000 0.0.0.0:* LISTEN 29445/monetdbd udp 0 0 127.0.0.1:50000 0.0.0.0:* 29445/monetdbd unix 2 [ ACC ] STREAM LISTENING 3869978213 <(386)%20997-8213> 29445/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ACC ] STREAM LISTENING 3869978215 <(386)%20997-8215> 29445/monetdbd /tmp/.s.merovingian.50000 unix 3 [ ] STREAM CONNECTED 3875263615 29486/mserver5 /tmp/.s.monetdb.50000 unix 2 [ ] STREAM CONNECTED 3871676942 29486/mserver5 /tmp/.s.monetdb.50000 $ netstat -anp | grep 50000 tcp 0 0 127.0.0.1:50000 0.0.0.0:* LISTEN 29445/monetdbd udp 0 0 127.0.0.1:50000 0.0.0.0:* 29445/monetdbd unix 2 [ ACC ] STREAM LISTENING 3869978213 <(386)%20997-8213> 29445/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ACC ] STREAM LISTENING 3869978215 <(386)%20997-8215> 29445/monetdbd /tmp/.s.merovingian.50000 unix 2 [ ] STREAM CONNECTED 3871676942 29486/mserver5 /tmp/.s.monetdb.50000 After switching to the forward option (where this issue is less frequent to be bothersome) =========================== >From merovingian.log 2016-12-01 11:22:59 MSG merovingian[29824]: redirecting client (local) for database 'voc' to mapi:monetdb://cerberus:50001/voc Although for most part the connections are stable, if and when mclient dies, I do notice that the netstat entries are NOT being removed. (see the entries piling up at the bottom though there are no active client processes) $ netstat -anp | grep 50000 (Not all processes could be identified, non-owned process info will not be shown, you would have to be root to see it all.) tcp 0 0 127.0.0.1:50000 0.0.0.0:* LISTEN 29824/monetdbd tcp 1 0 127.0.0.1:50000 127.0.0.1:35870 CLOSE_WAIT 29824/monetdbd udp 0 0 127.0.0.1:50000 0.0.0.0:* 29824/monetdbd unix 2 [ ACC ] STREAM LISTENING 3876967185 29824/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ACC ] STREAM LISTENING 3876967187 29824/monetdbd /tmp/.s.merovingian.50000 unix 2 [ ] STREAM CONNECTED 3877116313 29824/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ] STREAM CONNECTED 3880819958 29824/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ] STREAM CONNECTED 3878012589 29824/monetdbd /tmp/.s.monetdb.50000 unix 2 [ ] STREAM CONNECTED 3877885158 29824/monetdbd /tmp/.s.monetdb.50000 Thanks, Joseph

1 0

Building monetdb with Python 3
by Joseph D'silva 01 Dec '16

01 Dec '16

Hi all, Has anyone had any luck building the sources with Python 3 ? I tried building the Dec2016 branch from git, passing --with-python3=/usr/bin/python3 --with-pyversion=/usr/bin/python3 --with-pyconfig=/usr/bin/python3-config to configure, but received many errors during make under the pyapi directory, such as. /usr/include/python3.5m/object.h:86:5: error: missing braces around initializer [-Werror=missing-braces] { _PyObject_EXTRA_INIT \ ^ connection.c:159:5: note: in expansion of macro ‘PyObject_HEAD_INIT’ PyObject_HEAD_INIT(NULL) ^ /usr/include/python3.5m/object.h:86:5: note: (near initialization for ‘Py_ConnectionType.ob_base’) { _PyObject_EXTRA_INIT \ ^ connection.c:159:5: note: in expansion of macro ‘PyObject_HEAD_INIT’ PyObject_HEAD_INIT(NULL) ^ connection.c:159:5: error: missing initializer for field ‘ob_size’ of ‘PyVarObject {aka struct <anonymous>}’ [-Werror=missing-field-initializers] ................. connection.c:246:5: error: ‘return’ with a value, in function returning void [-Werror] import_array(); ^ It looks like some of the macro definitions to make python3 header files work with the pyapi source might be missing. I tried fixing some of the sources like unicode.c, formatinput.c etc. But it looks like majority of the source files have issues. Thanks, Joseph

2 2

MonetDB and IN search
by blacky_2k2＠yahoo.com 29 Nov '16

29 Nov '16

Hello, We’ve been battling for some time with what is fundamentally a simple query. We have a databases with about 15 columns which store int and float values. The query searches in each column with an IN condition , where the possible test values in the IN condition may vary from a 1 to 200 - where these 200 conditions are all the possible values of that column. So normally a query would look something like: SELECT id FROM table WHERE a IN (1,2,3,4) AND b IN (4,5,6,) AND c IN (2,5,6) ...... AND i = 5 etc. The database has about 2 mil rows but will grow to potentially 10 mil rows. A query similar to the one above takes about 80-100 ms. With about 50 test values for each IN. So far, the best results we got with MariaDB and proper indexing using Engine Memory where the above query takes about 2ms. We suspect MonetDB is not optimised for such queries, but since we don’t know much about its workings can you please confirm this? Otherwise, is there a way to improve the above query? Say using multiple JOINs instead of IN ? By the way, we are experimenting with MonetDB out of curiosity, we heard a lot of good things about it. Thank you very much, Silviu

3 4

Improved concurrency version of MonetDB
by Puthick Hok 24 Nov '16

24 Nov '16

Hi, I'm using MonetDB v11.23.13 (Jun2016-SP2). We decided to use MonetDB to store DNA marker data for our plant breeding platform (www.kddart.org) 2 years ago based on MonetDB performance on data retrieval and data update is also good. The use of MonetDB is not purely OLAP. There are lot of aspects of OLTP because people need to load marker data from their genotyping services. We finish integrating MonetDB into the platform and developing more tools for the system. As the platform is getting more mature, we started to do concurrency testing against the system including the part that is relying on MonetDB. Naively, I ran into a concurrency problem described in a master thesis by Rene Tassy (Improving Transactional Scalability in MonetDB). The work done in the thesis is very promising and I would like to thank you all for that. My question in relation to how I would deal with my problem is that what is the time frame for the improved MVCC Optimistic Concurrency Strategy in the MonetDB release? Regards, Puthick

2 1

Functions in MOnetdb
by Ariel Abadi 22 Nov '16

22 Nov '16

Hi! We are trying to create a couple of functions in Monet. Every function we create has a terrible bad performance. Can someone help me to find out what Im doing wrong ? Here is the function CREATE FUNCTION sb_glob.camelcase(input VARCHAR(500)) RETURNS VARCHAR(500) begin DECLARE len INT; DECLARE i INT; SET len = CHAR_LENGTH(input); SET input = LOWER(input); SET i = 0; WHILE (i < len) DO SET input = CASE WHEN ((SUBSTR(input,i,1) = ' ' OR SUBSTR(input,i,1) = '-' OR i = 0) AND (i < len)) THEN (LEFT(input,i) || UPPER(SUBSTR(input,i + 1,1)) || RIGHT(input,len - i - 1)) ELSE input END; SET i = i + 1; END WHILE; RETURN input; END Thks in advance!

2 1

filter based on json value of key/value pair
by Lynn Carol Johnson 21 Nov '16

21 Nov '16

Hello all - I'm having trouble pulling rows based on the value of a json key/value pair. I have created a table with a json object that looks like this: sql>\d testjson CREATE TABLE "sys"."testjson" ( "chr" INTEGER, "pos" INTEGER, "value" JSON ); sql> I have entries in the table that look as below: sql>select * from testjson; +------+-----------+----------------------------------------------------------------+ | chr | pos | value | +======+===========+================================================================+ | 1 | 209890809 | {"df":1,"pval":4.556021744872574E-6,"r2":0.0995066009518681} | | 1 | 789383847 | {"df":1,"pval":2.50962115178055E-6,"r2":0.10462833261259474} | | 2 | 127893782 | {"df":1,"pval":4.2825829011938765E-6,"r2":0.10003907080878045} | | 2 | 234533212 | {"df":2,"pval":4.556021744872574E-6,"r2":0.0995066009518681} | +------+-----------+----------------------------------------------------------------+ 4 tuples (1.027ms) sql> I would like to filter on json values. Not the json key, the json key's value. I can pull json values based on a non-json field, e.g. Grab the pval where chr>1 as below: sql>select json.filter(value,'r2') as r2Value from testjson where chr=1; +-----------------------+ | r2value | +=======================+ | [0.0995066009518681] | | [0.10462833261259474] | +-----------------------+ 2 tuples (1.179ms) sql> But I want to pull chr/pos where pval < some amount, or df > some number. For example, how would I write the query to select all rows where df > 1? This query should return the last row of the table above. I've tried the following: sql>select * from testjson where (json.filter(value,'df') > 1); types json(0,0) and tinyint(8,0) are not equal sql> Thanks for your help - Lynn

2 2

how to structure queries like SELECT a.col IN ( b.col FROM b ) FROM a ?
by Anthony Damico 21 Nov '16

21 Nov '16

hi monetdb team, i've pasted a minimal reproducible example of a few SQL queries that i had expected to work -- only about half of them do work, and the other half just error with 'ParseException:SQLparser:' my goal is to get the very last line working, so if anybody could suggest a hack or a workaround that doesn't involve a JOIN, i would appreciate it. thanks all!! CREATE TABLE table_one (this_column INTEGER) ; INSERT INTO table_one VALUES (1) ; INSERT INTO table_one VALUES (2) ; INSERT INTO table_one VALUES (3) ; INSERT INTO table_one VALUES (4) ; INSERT INTO table_one VALUES (5) ; CREATE TABLE table_two (this_column INTEGER) ; INSERT INTO table_two VALUES (1) ; INSERT INTO table_two VALUES (2) ; # works as expected SELECT SUM( ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) ) FROM table_one ; SELECT MAX( ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) ) FROM table_one ; # fails SELECT ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) AS new_column FROM table_one ; # Server says 'ParseException:SQLparser:'. # works SELECT AVG( CASE WHEN table_one.this_column IN ( SELECT this_column FROM table_two ) THEN 1 ELSE 0 END ) AS new_column FROM table_one ; # fails SELECT ( CASE WHEN ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) THEN 1 ELSE 0 END ) AS new_column FROM table_one ; # Server says 'ParseException:SQLparser:'. # fails [[this query is the one i would like to implement]] SELECT COUNT(*) , ( CASE WHEN ( table_one.this_column IN ( SELECT this_column FROM table_two ) ) THEN 1 ELSE 0 END ) AS new_column FROM table_one GROUP BY new_column ; # Server says 'ParseException:SQLparser:'.

3 8

Unable to install//access external R Packages inside of MonetDB
by Cas Saternos 10 Nov '16

10 Nov '16

Hello All, Just wanted to report a bug, or at least unexpected behavior. I am new to monetdb, and could not submit this to your bugzilla tracker. *Background* I successfully have used MonetDB with Docker ( https://www.monetdb.org/blog/docker-container-monetdb-r) and was able to create FUNCTIONs and AGGREGATEs with R (as described here: https://www.monetdb.org/content/embedded-r-monetdb) This blog post also states: "As a final note on usage, you can use any R package from CRAN within your function without explicitly installing it. We overloaded the library function in R to automatically install missing packages." *Description* However, I am not able to use external R packages available from CRAN. The session below describes both Docker commands and SQL used. This session was run on OSX using Docker Machine. Thank you, -Cas *bash-3.2$* docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES d6b944445237 monetdb/monetdb-r-docker:latest "/usr/bin/supervisord" 13 minutes ago Up 13 minutes 0.0.0.0:32768->50000/tcp monetdb-r-docker *bash-3.2$* docker exec -it monetdb-r-docker mclient db user:monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Jul2015-SP4) Database: MonetDB v11.21.19 (Jul2015-SP4), 'mapi:monetdb://d6b944445237: 50000/db' Type \q to quit, \? for a list of available commands auto commit mode: on # Create and use a function to show that MonetDB with basic R expressions works: *sql>*CREATE FUNCTION test_add() RETURNS DOUBLE LANGUAGE R { 1 + 1 }; operation successful (1.254ms) *sql>*SELECT test_add(); +--------------------------+ | test_add | +==========================+ | 2 | +--------------------------+ # Add only the library function call to the function just shown (and give the function a new name). The function is created successfully, but an error occurs when the function is invoked in a query: *sql>*CREATE FUNCTION test_library() RETURNS DOUBLE LANGUAGE R { library("ggplot2"); 1 + 1 }; operation successful (1.378ms) *sql>*SELECT test_library(); Error running R expression. Error message: Error in .library.original(package, help, pos, lib.loc, character.only = T, : there is no package called 'ggplot2' Calls: as.data.frame -> <Anonymous> -> library -> .library.original *sql>*

1 0

NULL in COPY INTO FROM bulk
by Mathieu Raillard 10 Nov '16

10 Nov '16

Hi all, We are working with monetdb since 1 year and half, the performance are great. We are using in production the July 2015 release and we want to upgrade to the last release. We installed the new version and ran some tests to ensure the compatibility, unfortunatly we discovered a change in the way monetdb is handling NULL in bulk import. We are exporting datas from Mysql and by default we get a \N for every NULL value plus every field are enclosed by "" ( even number and timestamp). Exemple : 1,test,2016-01-01 00:00:00,null in database become "1","test","2016-01-01 00:00:00',"\N" in bulk. This bulk is working fine with the July 2015 realease but we cant import "\N" in number and timestamp with the june 2016 realase Have you an idea how can we make it works ? Here is some results from my tests On the July 2015 release : create schema test; create table test.test ( field1 int, field2 timestamp, field3 varchar(20) ); Bulk1 : "1","2016-01-01 00:00:00","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk1' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (52.812ms) Bulk2 : "1","2016-01-01 00:00:00","\N" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk2' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (47.852ms) Bulk3 : "1","\N","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk3' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (58.811ms) Bulk4 : "\N","2016-01-01 00:00:00","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk4' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (32.812ms) Bulk5 : "\N","\N","\N" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk5' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (63.785ms) select * from test.test field1 field2 field3 1 2016-01-01 00:00:00.0 test 1 2016-01-01 00:00:00.0 <null> 1 <null> test <null> 2016-01-01 00:00:00.0 test <null> <null> <null> Tested on the Jun 2016 release : create schema test; create table test.test ( field1 int, field2 timestamp, field3 varchar(20) ); Bulk1 : "1","2016-01-01 00:00:00","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk1' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (61.812ms) Bulk2 : "1","2016-01-01 00:00:00","\N" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk2' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; 1 affected row (47.314ms) Bulk3 : "1","\N","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk3' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; Failed to import table line 1 field 2 'timestamp(7)' expected in '\N' Bulk4 : "\N","2016-01-01 00:00:00","test" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk4' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; Failed to import table line 1 field 1 'int' expected in '\N' Bulk5 : "\N","\N","\N" COPY 10 RECORDS INTO test.test FROM '/tmp/bulk5' USING DELIMITERS ',','\n','"' NULL AS '\\N' ; Failed to import table line 1 field 1 'int' expected in '\N' select * from test.test +--------+----------------------------+--------+ | field1 | field2 | field3 | +========+============================+========+ | 1 | 2016-01-01 00:00:00.000000 | test | | 1 | 2016-01-01 00:00:00.000000 | N | +--------+----------------------------+--------+ Regards Mathieu

1 0

COPY subquery INTO file_name - INCLUDE field name
by Ariel Abadi 10 Nov '16

10 Nov '16

Hi! We are using the COPY *subquery* INTO *file_name *[ [USING] DELIMITERS *field_separator* [',' *record_separator* [ ',' *string_quot*e ]]] [ NULL AS *null_string* ] BUT we need to have in the file the name of the fields on the first row. Im looking on the documentation, but I cannot find anything related to this, can someone help me with this? Thks in advance!

3 3

Fwd: Performance tuning
by Rajendra Bhat 09 Nov '16

09 Nov '16

Hi team, I am running monetdb on 16 core processor and 30gb ram. I have table around 54 fields. gzip compressed csv size is around 6.5 GB. query will take around 6 min. please suggest tuning options. query: Select count(distinct userid),sum(mrp)/sum(noOfItems),sum(mrp)/sum( orderCount),sum(revenue),sum(orderCount),sumnoOfItems),sum( returnCount),sum(rtoCount),sum(0),sum(0),sum(tDiscountPer),sum(cDiscountPer) from historical where gender='men' and brand='puma' Thanks and Regards Rajendra Bhat -- Thanks and Regards Rajendra Bhat

2 3

How do I force start a new monetdbd instance?
by Anderson, David B 09 Nov '16

09 Nov '16

Or what files do I need to delete from the file system? When I try to start a new monetdbd instance, I am getting the error: another monetdbd is already running but there are no monetdb processes running. . .

2 1