- users-list - monetdb.org

Announcement: New Jun2016-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 22 Jul '16

22 Jul '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jun2016-SP1 bugfix 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/>. Jun 2016-SP1 bugfix release . * Lots of memory leaks have been plugged across the whole system. Java Module * Corrected PROCEDURE_TYPE output value of method DatabaseMetaData.getProcedures(). It used to return procedureReturnsResult. Now it returns procedureNoResult. Corrected ORDINAL_POSITION output value of method DatabaseMetaData.getProcedureColumns(). It used to start with 0, but as procedures do not return a result value it now starts with 1 for all the procedure arguments, as defined by the JDBC API. * Improved output of method DatabaseMetaData.getProcedures(). The REMARKS column now contains the procedure definition as stored in sys.functions.func. The SPECIFIC_NAME column now contains the procedure unique identifier as stored in sys.functions.id. This allows the caller to retrieve the specific overloaded procedure which has the same name, but different arguments. Also improved output of method DatabaseMetaData.getProcedureColumns(). The SPECIFIC_NAME column now contains the procedure unique identifier as stored in sys.functions.id. This allows the caller to retrieve the proper arguments of the specific overloaded procedure by matching the SPECIFIC_NAME value. * Improved output of method DatabaseMetaData.getFunctions(). The REMARKS column now contains the function definition as stored in sys.functions.func. The SPECIFIC_NAME column now contains the function unique identifier as stored in sys.functions.id. This allows the caller to retrieve the specific overloaded function which has the same name, but different arguments. Also improved output of method DatabaseMetaData.getFunctionColumns(). The SPECIFIC_NAME column now contains the function unique identifier as stored in sys.functions.id. This allows the caller to retrieve the proper arguments of the specific overloaded function by matching the SPECIFIC_NAME value. Bug Fixes * 4014: KILL signal * 4021: Analyze query does not escape input [security] * 4026: JDBC driver incorrectly converts TINYINT fields to String instead of an integer type. * 4028: inputs not the same size * 4031: mclient doesn't accept - argument to refer to stdin * 4032: no decimal places after update. ODBC driver * 4035: SQL Function call bug * 4036: Possible sql_catalog corruption due to unclean backuped tail

1 0

Determining join order
by Moritz Bruder 20 Jul '16

20 Jul '16

Hi, in the process of writing a MAL backend for a query compiler, I'm trying to optimize the join order of arbitrary theta joins on whole relations (i.e. predicates may be arbitrary). To implement this, I use a simple heuristic for 2-way theta joins on relations: First the cheap predicates are identified, then the necessary joins are performed on the columns. After that all results of the cheap predicates are merged via joins on the OIDs. Finally, expensive predicates (i.e. with a lot of qualifying tuples) are implemented by applying a binary scalar operation and using a *subselect* and a *projection*/*leftfetchjoin*, since all columns are aligned now. To conclude: 2-way joins may not be atomic MAL joins (i.e. not column-wise), but they very well could be. So, what I'm looking for is some facility in MonetDB, with which I am able to break down a n-way join into 2-way joins, with the smallest possible intermediate results, without assuming too much about how those 2-way joins are implemented. The first thing I found was the *scheduler* module, which is also on the official website ( https://www.monetdb.org/Documentation/Manuals/MonetDB/Optimizers/Memoization ). But that seems no more up to date, since most of the functions are missing and the semantics of join operators in MAL has changed quite a bit. Is there anything else I can use for this task (aside from implementing some dynamic programming algorithm within MAL)? Kind regards, Moritz

1 0

Limits on loaded shared libraries
by Anderson, David B 20 Jul '16

20 Jul '16

All, I was planning on loading a shared library within either an R or Python function inside a MonetDB server. Is this supported? What kinds of constraints do I need to worry about? Guessing any memory problems will impact the server, but not sure about thread safety etc. Thanks, Dave

1 0

MERGE TABLE ERROR
by Ariel Abadi 20 Jul '16

20 Jul '16

Hi! We are facing a problem with MERGE Tables. We have a process in php which connects to the DB using the mapi ( https://www.monetdb.org/Documentation/Manuals/SQLreference/Programming/MAPI <https://www.google.com/url?q=https%3A%2F%2Fwww.monetdb.org%2FDocumentation%…> ) The process creates a new table everyday as follows: CREATE TABLE cub8_20160717 AS (SELECT * FROM cub8) WITH NO DATA; Then, attaches that new table to the MERGE table called cub8_cubo as follows: ALTER TABLE cub8_cubo ADD TABLE cub8_20160717 ; Once all this is created, it start inserting data. Everything works fine, with NO errors. BUT if I run a query against cub8_cubo I dont see cub8_20160717 data. So I run this query in order to check if the new table was attached to the merge: SELECT a.name, b.name, s.name FROM sys.tables as a JOIN sys.dependencies d ON a.id = d.depend_id JOIN sys.tables as b ON b.id= d.id JOIN sys.schemas as s ON a.schema_id = s.id WHERE a.type= 3 AND a.name ILIKE '%cub8%' ORDER BY 1,2; +-----------+---------------+ | name | name | +===========+===============+ | cub8_cubo | cub8_20160710 | | cub8_cubo | cub8_20160711 | | cub8_cubo | cub8_20160712 | | cub8_cubo | cub8_20160713 | | cub8_cubo | cub8_20160714 | | cub8_cubo | cub8_20160715 | | cub8_cubo | cub8_20160716 | | cub8_cubo | cub8_20160717 | So I try to detached from cub8_cubo, with the command: ALTER TABLE cub8_cubo DROP TABLE cub8_20160717; ALTER TABLE: table 'sb_traf.cub8_20160717' isn't part of the MERGE TABLE 'sb_traf.cub8_cubo' So I ADD it again: ALTER TABLE cub8_cubo ADD TABLE cub8_20160717; and now yes... I can see the data. If I need to detach that table, I need to run ALTER ... DROP twice or three times to get it done. It seems to be a bug?, are we doing something wrong ?? Thks in advance! *PS.:We are using * *MonetDB Database Server v1.7 (Jun2016)* *Welcome to mclient, the MonetDB/SQL interactive terminal (Jun2016)*

3 8

embedded py/R on stable release
by Stefano Fioravanzo 17 Jul '16

17 Jul '16

Hello, Are ambedded python/R included in the latest stable release of MonetDB or do I have to install the dev version from source? Thank you Regards, Stefano Fioravanzo

3 3

jdbc connection problems
by Anderson, David B 16 Jul '16

16 Jul '16

All, Do I need to start the server with any special options in order to connect to it via jdbc? I have installed the driver in DBVisualizer v9.1.7 and am using the URL jdbc:monetdb://mrsvm1d:50000/pytest but I keep getting connection refused errors from the driver (included below). When I connect locally via mclient, I have: Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased) Database: MonetDB v11.24.0 (unreleased), 'mapi:monetdb://mrsvm1d:50000/pytest' Type \q to quit, \? for a list of available commands auto commit mode: on sql> Here is my server command line (do I need to change the mapi items?): /opt/mr/monetdb/bin/mserver5.bin --set gdk_nr_threads 3 --dbpath=/home/da64666/MonetDB/pytest --set merovingian_uri mapi:monetdb://mrsvm1d:50000/pytest --set mapi_open false --set mapi_port 0 --set mapi_usock /home/da64666/MonetDB/pytest/.mapi.sock --set monet_vault_key /home/da64666/MonetDB/pytest/.vaultkey --set gdk_nr_threads 2 --set max_clients 64 --set sql_optimizer default_pipe --set embedded_py true --set monet_daemon yes Thanks, Dave java.sql.SQLException: Unable to connect (mrsvm1d:50000): Connection refused: connect at nl.cwi.monetdb.jdbc.MonetConnection.<init>(MonetConnection.java:244) at nl.cwi.monetdb.jdbc.MonetDriver.connect(MonetDriver.java:166) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at com.onseven.dbvis.g.B.D.?(Z:1413) at com.onseven.dbvis.g.B.F$A.call(Z:1474) at java.util.concurrent.FutureTask.run(Unknown Source) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source) SQLException: SQLState(08006) registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@130b7ed registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@7e673e registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@115a9a7 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1057d25 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1abd8e3 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@63900c registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1ff6814 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@9a6101 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@74e0a3 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@a0148b registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1070f4 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@9701ad registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1b6da12 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1769a58 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@4ea676 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@2d0f93 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1c2163a registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1056ee7 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1570d7b registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@18b128d registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@fae160 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@17d9594 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@cb361 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@c1f8d registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@8fe97c registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1108377 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@faf9a3 registerDriver: nl.cwi.monetdb.jdbc.MonetDriver@1c77aba java.sql.SQLException: Unable to connect (mrsvm1d:50000): Connection refused: connect at nl.cwi.monetdb.jdbc.MonetConnection.<init>(MonetConnection.java:244) at nl.cwi.monetdb.jdbc.MonetDriver.connect(MonetDriver.java:166) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.lang.reflect.Method.invoke(Unknown Source) at com.onseven.dbvis.g.B.D.?(Z:1413) at com.onseven.dbvis.g.B.F$A.call(Z:1474) at java.util.concurrent.FutureTask.run(Unknown Source) at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) at java.lang.Thread.run(Unknown Source)

3 5

Re: monet crashes with query to VERY large table
by Sahasranaman M S 15 Jul '16

15 Jul '16

Hello, Just a suggestion, if you think the issue is related to the size of the data, then you could use a random data generator to test it. We were facing a similar issue with shareable data for testing. So I wrote a program called tqgen in Golang (https://github.com/niocs/tqgen) It can be used to generate large amounts of csv data for testing, and to share the data, we just need to share the command we used to generate it, and the other person can run it to generate the exact same data. It produces datewise fictional trade/quote data for fictional stocks. It puts each date's data into a separate file, and each file is around 128MB, so if we run it for a daterange of 1.5 years, it should generate around 70GB of data. It takes a while to finish because the data is not completely random and it is related to other rows. To generate, you could run: $ git clone https://github.com/niocs/tqgen $ cd tqgen $ go build tqgen.go $ ./tqgen --OutFilePat /path/to/data/tq.YYYYMMDD.csv --NumStk 1000 --Seed 234532 --DateBeg 20200101 --DateEnd 20210831 I usually load them one by one so that I could record load time stats, etc, but you could use this bash script ( https://github.com/niocs/hcsv/blob/master/hcat) to concatenate them without repeating the headers, and then load to MonetDB. (I will later add a parameter to tqgen to save to a single file) To do that, $ git clone https://github.com/niocs/hcsv $ hcsv/hcat /path/to/data/tq.20*.csv > /path/to/data/tq.big.csv If this causes the same issue, then others could regenerate the same data to try. Hope this helps. Thanks, Sahas > On Jul 14, 2016 10:20 AM, "Anthony Damico" <ajdamico(a)gmail.com> wrote: > >> can you winnow down the problem enough to construct a reproducible >> example? thanks >> >> On Thursday, July 14, 2016, Stefano Fioravanzo <fioravanzos(a)gmail.com> >> wrote: >> >>> Hello, >>> >>> No the data is not public, and testing in on another machine is not an >>> easy option right now. >>> >>> Regards, >>> >>> Stefano >>> >>> On 14 Jul 2016, at 15:49, Anthony Damico <ajdamico(a)gmail.com> wrote: >>> >>> hi, any chance these data are public? if not, can you test on a >>> separate box and see if the exact same thing happens or if it's >>> hardware-specific? >>> >>> On Thursday, July 14, 2016, Stefano Fioravanzo <fioravanzos(a)gmail.com> >>> wrote: >>> >>>> Hello, >>>> >>>> I currently have a table with 307524407 rows and 52 columns, it was >>>> loaded from a 78GB cvs file, but I do not know how much space the table >>>> actually takes on disk. >>>> If I query the table with a simple query like: >>>> >>>> select count(*) from big_table where field =1; >>>> >>>> mserver5 crashes with ‘segmentation fault' >>>> I tried also the following query (adding the LIMIT statement): >>>> select count(*) from farmap_movimento where farmacia_id =1 >>>> limit 10; >>>> >>>> and the output from mserver5 was: >>>> mserver5: gdk_select.c:869: fullscan_int: Assertion `cnt < >>>> (bn)->S->capacity' failed. >>>> Aborted >>>> >>>> Doing a more complex query (the one I am aiming for), with also a group >>>> by and a few aggregation functions (sum, min) on the fields, the outputs is: >>>> mserver5: gdk_bat.c:2066: BATsetcount: Assertion >>>> `b->S->capacity >= cnt' failed. >>>> Aborted >>>> >>>> This seems to be a table size problem, so…is there anything I can do? >>>> >>>> I am using MonetDB 5 server v11.22.0 >>>> The machine has 30GB of RAM and 60GB of swap. >>>> Available disk space: ~50GB >>>> >>>> Any help would be much appreciated, >>>> >>>> Regards, >>>> >>>> Stefano >>>> >>>> _______________________________________________ >>>> users-list mailing list >>>> users-list(a)monetdb.org >>>> https://www.monetdb.org/mailman/listinfo/users-list >>>> >>> _______________________________________________ >>> users-list mailing list >>> users-list(a)monetdb.org >>> https://www.monetdb.org/mailman/listinfo/users-list >>> >>> >>> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> https://www.monetdb.org/mailman/listinfo/users-list >> >>

3 5

monet crashes with query to VERY large table
by Stefano Fioravanzo 14 Jul '16

14 Jul '16

Hello, I currently have a table with 307524407 rows and 52 columns, it was loaded from a 78GB cvs file, but I do not know how much space the table actually takes on disk. If I query the table with a simple query like: select count(*) from big_table where field =1; mserver5 crashes with ‘segmentation fault' I tried also the following query (adding the LIMIT statement): select count(*) from farmap_movimento where farmacia_id =1 limit 10; and the output from mserver5 was: mserver5: gdk_select.c:869: fullscan_int: Assertion `cnt < (bn)->S->capacity' failed. Aborted Doing a more complex query (the one I am aiming for), with also a group by and a few aggregation functions (sum, min) on the fields, the outputs is: mserver5: gdk_bat.c:2066: BATsetcount: Assertion `b->S->capacity >= cnt' failed. Aborted This seems to be a table size problem, so…is there anything I can do? I am using MonetDB 5 server v11.22.0 The machine has 30GB of RAM and 60GB of swap. Available disk space: ~50GB Any help would be much appreciated, Regards, Stefano

2 3

ROLE copy_role
by Alberto Ferrari 14 Jul '16

14 Jul '16

Hi all: In the new version of Monet (jun/2016), when normal USER (not "monetdb" user) runs "COPY INTO" or "INSERT", it only works with "SET ROLE copy_role;" before the query. Is that really needed? Should I prepend "SET ROLE copy_role" before every INSERT / COPY INTO query?? Doesn't make sense for me. I supposed the user/permissions configuration is enough for that? Thanks in advance.

1 0

adding binary columns to existing table
by Lynn Carol Johnson 14 Jul '16

14 Jul '16

Hi all - I've been using Monetdb's binary bulk loads and love it. Very fast, very efficient. We have a large database (2.3 billion rows) to which we frequently add new columns as new data is curated. To date, I've been re-creating the table each time, adding the new columns, and performing a binary bulk load for data in all columns. My question: Does monetdb allow for altering a table and then loading a binary file just for that column? Or is this not efficient as it requires an update on every row to add the new column's data? As our table columns reach into the hundreds it would be nice to "append" new columns, but perhaps logistically this isn't reasonable. Thanks for your insight - Lynn

1 0

Server crashes when using a prepared statement with a function
by João Victor Risso 12 Jul '16

12 Jul '16

Hello, I have a SQL function (myFunc) that computes the sum of the records on a table (tbl1) and returns the sum (along with other attributes) as a table. tbl1 references tables tbl2 and tbl3 through fk_tbl2_id and fk_tbl3_id's, respectively. Since I want to use this function in a Node.js API and the parameters for the function will come from the users of the API, it is safer to use a prepared statement to bind the parameters. Whenever I try to execute the following prepared statement with the function: > PREPARE SELECT * FROM myFunc(?, ?, ?); > EXEC (2008, 1, 2); The server crashes, as shown in the server log and dmesg: > 2016-07-12 10:56:40 MSG merovingian[3110]: database 'testdb' (19875) was killed by signal SIGSEGV > mserver5[18201]: segfault at 30 ip 00007f2d87be51e4 sp 00007f2d86f63b80 error 4 in lib_sql.so[7f2d87b85000+199000] The SQL function for myFunc is the following: > CREATE FUNCTION myFunc(year int, table2_id int, table3_id int) > RETURNS TABLE(name text, total bigint) > BEGIN > RETURN ( > SELECT > 'Overall', > COALESCE(SUM(t1.num_enrollments), 0) > FROM > tbl1 AS t1 > WHERE > t1.census_year = year > AND > (CASE(table2_id) > WHEN 0 THEN > (t1.fk_table2_id > 0) > ELSE > (t1.fk_table2_id = table2_id) > END) > AND > (CASE(table3_id) > WHEN 0 THEN > (t1.fk_table3_id > 0) > ELSE > (t1.fk_table3_id = table3_id) > END) > ); > END; I'm running MonetDB v11.21.19 (Jul2015-SP4) on Ubuntu 14.04 LTS. I could not find any bug reports related to the issue. How can I debug it further? Any help is deeply appreciated. Best regards, João Victor Risso.

1 0

select * from sys.db_users() SIGSEGV
by Vincent Sheffer 12 Jul '16

12 Jul '16

We are running: [root@monetdb monetdb5]# mserver5 --version MonetDB 5 server v11.21.19 "Jul2015-SP4" (64-bit, 64-bit oids, 128-bit integers) Copyright (c) 1993-July 2008 CWI Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved Visit http://www.monetdb.org/ for further information Found 503.6GiB available memory, 28 available cpu cores Libraries: libpcre: 8.32 2012-11-30 (compiled with 8.32) openssl: OpenSSL 1.0.1e 11 Feb 2013 (compiled with ) libxml2: 2.9.1 (compiled with 2.9.1) Compiled by: @9bedafc83c11 (x86_64-unknown-linux-gnu) Compilation: gcc -gdwarf-4 -g3 Linking : /usr/bin/ld -m elf_x86_64 When select * from sys.db_users() is executed when logged in as the monetdb user there is no problem, but when run under any other user we get the following stack trace: [Switching to Thread 0x7f418e7f3700 (LWP 26599)] 0x00007f41bd5cf059 in db_users_wrap (cntxt=0x7f41be899630, mb=0x7f412416e620, stk=0x7f4124114bd0, pci=0x7f41241255d0) at sql_user.c:160 160sql_user.c: No such file or directory. (gdb) where #0 0x00007f41bd5cf059 in db_users_wrap (cntxt=0x7f41be899630, mb=0x7f412416e620, stk=0x7f4124114bd0, pci=0x7f41241255d0) at sql_user.c:160 #1 0x00007f41c519f1f7 in runMALsequence (cntxt=0x7f41be899630, mb=0x7f412416e620, startpc=1, stoppc=0, stk=0x7f4124114bd0, env=0x0, pcicaller=0x0) at mal_interpreter.c:631 #2 0x00007f41c519e819 in callMAL (cntxt=0x7f41be899630, mb=0x7f412416e620, env=0x7f418e7f2a68, argv=0x7f418e7f2af0, debug=0 '\000') at mal_interpreter.c:447 #3 0x00007f41bd5d50d6 in SQLexecutePrepared (c=0x7f41be899630, be=0x7f41242417f0, q=0x7f4124117740) at sql_execute.c:328 #4 0x00007f41bd5d54c9 in SQLengineIntern (c=0x7f41be899630, be=0x7f41242417f0) at sql_execute.c:390 #5 0x00007f41bd5d4257 in SQLengine (c=0x7f41be899630) at sql_scenario.c:1323 #6 0x00007f41c51bcaf7 in runPhase (c=0x7f41be899630, phase=4) at mal_scenario.c:515 #7 0x00007f41c51bccb4 in runScenarioBody (c=0x7f41be899630) at mal_scenario.c:559 #8 0x00007f41c51bcda1 in runScenario (c=0x7f41be899630) at mal_scenario.c:579 #9 0x00007f41c51bdcd8 in MSserveClient (dummy=0x7f41be899630) at mal_session.c:439 #10 0x00007f41c51bd8b2 in MSscheduleClient (command=0x7f4124279b90 "p\233'$A\177", challenge=0x7f418e7f2da0 "Up8bNTal6v", fin=0x7f4124117560, fout=0x7f4124241680) at mal_session.c:319 #11 0x00007f41c526d001 in doChallenge (data=0x7f41a0001690) at mal_mapi.c:184 #12 0x00007f41c4bae17f in thread_starter (arg=0x7f41a00008c0) at gdk_system.c:458 #13 0x00007f41c2814dc5 in start_thread () from /lib64/libpthread.so.0 #14 0x00007f41c2541ced in clone () from /lib64/libc.so.6 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.

1 0

range request on timestamp column in prepared statement
by Frank Tetzel 11 Jul '16

11 Jul '16

Hello, i have trouble to get a parametrized range request on a timestamp column working. Small example in mclient: create table test(creationDate timestamp); insert into test values('2012-04-02 00:00:00'), ('2012-03-30 00:00:00'),('2012-04-20 00:00:00'); -- works prepare select * from test where creationDate < ?; exec 21(TIMESTAMP '2012-04-01 00:00:00'); -- works select * from test where creationDate between TIMESTAMP '2012-04-01 00:00:00' and TIMESTAMP '2012-04-01 00:00:00' + interval '5' day; -- syntax error, unexpected '?', expecting SCOLON prepare select * from test where creationDate between ? and ? + interval ? day; -- fails too: program contains errors prepare select * from test where creationDate between ? and ? + interval '5' day; exec 22(TIMESTAMP '2012-04-01 00:00:00', TIMESTAMP '2012-04-01 00:00:00'); Does anybody know of a workaround? Is there a builtin function for adding days to a timestamp i missed? I couldn't find anything in the documentation. Btw, i'm on v11.23.3. Regards, Frank

1 1

Is there any scan sharing technique in MonetDB?
by Xu,Wenjian 07 Jul '16

07 Jul '16

Hi all, I understand that MonetDB would recycle intermediate results to accelerate subsequent OLAP queries that involve common sub-queries as previous ones. I am wondering that, 1. Does MonetDB implement any scan sharing techniques (or generally, multi-query optimization techniques) in the context of concurrent workloads? For example, two concurrent queries Q1 and Q2 both require scanning column X. Is it possible for their query plans to share the scan operation? 2. If no, what is the reason? too complex to implement? or any reason related to MonetDB's execution paradigm that prevents such sharing techniques? Any comments would be helpful, thanks. Best regards, Wenjian

2 2

R UDFs
by Anderson, David B 06 Jul '16

06 Jul '16

All, Quick question, how many R interpreters are running within MonetDB if you are using R UDFs? Is there a single shared interpreter or are there multiple copies running? Thanks, Dave

2 16

DATABASE NOT STARTING
by Ariel Abadi 04 Jul '16

04 Jul '16

Guys! Im desperate... the DB is not starting anymore... Can someone help me? Im starting the DB but I receive the following msg: starting database 'sb_traf'... FAILED start: starting 'sb_traf' failed: database 'sb_traf' appears to shut itself down after starting, check monetdbd's logfile for possible hints This is part of the log... maybe somebody has a clue. 2016-07-02 22:15:30 MSG merovingian[28268]: proxying client static.10-10-10-225.clients.your-server.de:47462 for database 'sb_traf' to mapi:monetdb:///opt/monetdb/CUSTOMERnew/sb_traf/.mapi.sock?database=sb_traf 2016-07-02 22:15:30 MSG merovingian[28268]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-07-02 22:15:30 MSG merovingian[28268]: proxying client static.10-10-10-225.clients.your-server.de:47463 for database 'sb_traf' to mapi:monetdb:///opt/monetdb/CUSTOMERnew/sb_traf/.mapi.sock?database=sb_traf 2016-07-02 22:15:30 MSG merovingian[28268]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-07-02 22:20:29 MSG merovingian[28268]: proxying client static.10-10-10-225.clients.your-server.de:47726 for database 'sb_traf' to mapi:monetdb:///opt/monetdb/CUSTOMERnew/sb_traf/.mapi.sock?database=sb_traf 2016-07-02 22:20:29 MSG merovingian[28268]: target connection is on local UNIX domain socket, passing on filedescriptor instead of proxying 2016-07-02 22:20:29 MSG merovingian[28268]: database 'sb_traf' (28351) was killed by signal SIGSEGV 2016-07-02 22:20:36 MSG merovingian[28268]: starting database 'sb_traf', up min/avg/max: 11s/1d/3w, crash average: 1.00 0.40 0.50 (69-25=44) 2016-07-02 22:20:36 MSG sb_traf[29578]: arguments: /usr/bin/mserver5 --dbpath=/opt/monetdb/CUSTOMERnew/sb_traf --set merovingian_uri=mapi:monetdb://CUSTOMER-monetbd:54321/sb_traf --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/monetdb/CUSTOMERnew/sb_traf/.mapi.sock --set monet_vault_key=/opt/monetdb/CUSTOMERnew/sb_traf/.vaultkey --set gdk_nr_threads=8 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-07-02 22:20:36 MSG sb_traf[29578]: # MonetDB 5 server v11.21.19 "Jul2015-SP4" 2016-07-02 22:20:36 MSG sb_traf[29578]: # Serving database 'sb_traf', using 8 threads 2016-07-02 22:20:36 MSG sb_traf[29578]: # Compiled for x86_64-redhat-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked 2016-07-02 22:20:36 MSG sb_traf[29578]: # Found 20.000 GiB available main-memory. 2016-07-02 22:20:36 MSG sb_traf[29578]: # Copyright (c) 1993-July 2008 CWI. 2016-07-02 22:20:36 MSG sb_traf[29578]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2016-07-02 22:20:36 MSG sb_traf[29578]: # Visit http://www.monetdb.org/ for further information 2016-07-02 22:20:36 MSG sb_traf[29578]: # Listening for UNIX domain connection requests on mapi:monetdb:///opt/monetdb/CUSTOMERnew/sb_traf/.mapi.sock 2016-07-02 22:20:36 MSG sb_traf[29578]: # Start processing logs sql/sql_logs version 52200 2016-07-02 22:20:36 MSG sb_traf[29578]: # Start reading the write-ahead log 'sql_logs/sql/log.81841' 2016-07-02 22:20:36 MSG sb_traf[29578]: # Finished reading the write-ahead log 'sql_logs/sql/log.81841' 2016-07-02 22:20:36 MSG sb_traf[29578]: # Finished processing logs sql/sql_logs 2016-07-02 22:20:36 MSG merovingian[28268]: database 'sb_traf' (29578) was killed by signal SIGSEGV 2016-07-02 22:20:36 ERR merovingian[28268]: client error: database 'sb_traf' appears to shut itself down after starting, check monetdbd's logfile for possible hints 2016-07-02 22:20:37 MSG merovingian[28268]: starting database 'sb_traf', up min/avg/max: 11s/1d/3w, crash average: 1.00 0.50 0.50 (70-25=45) 2016-07-02 22:20:37 MSG sb_traf[29582]: arguments: /usr/bin/mserver5 --dbpath=/opt/monetdb/CUSTOMERnew/sb_traf --set merovingian_uri=mapi:monetdb://CUSTOMER-monetbd:54321/sb_traf --set mapi_open=false --set mapi_port=0 --set mapi_usock=/opt/monetdb/CUSTOMERnew/sb_traf/.mapi.sock --set monet_vault_key=/opt/monetdb/CUSTOMERnew/sb_traf/.vaultkey --set gdk_nr_threads=8 --set max_clients=64 --set sql_optimizer=default_pipe --set monet_daemon=yes 2016-07-02 22:20:37 MSG sb_traf[29582]: # MonetDB 5 server v11.21.19 "Jul2015-SP4" 2016-07-02 22:20:37 MSG sb_traf[29582]: # Serving database 'sb_traf', using 8 threads 2016-07-02 22:20:37 MSG sb_traf[29582]: # Compiled for x86_64-redhat-linux-gnu/64bit with 64bit OIDs and 128bit integers dynamically linked 2016-07-02 22:20:37 MSG sb_traf[29582]: # Found 20.000 GiB available main-memory. 2016-07-02 22:20:37 MSG sb_traf[29582]: # Copyright (c) 1993-July 2008 CWI. 2016-07-02 22:20:37 MSG sb_traf[29582]: # Copyright (c) August 2008-2015 MonetDB B.V., all rights reserved 2016-07-02 22:20:37 MSG sb_traf[29582]: # Visit http://www.monetdb.org/ for further information 2016-07-02 22:20:37 MSG sb_traf[29582]: # Listening for UNIX domain connection requests on mapi:monetdb:///opt/monetdb/CUSTOMERnew/sb_traf/.mapi.sock 2016-07-02 22:20:37 MSG sb_traf[29582]: # Start processing logs sql/sql_logs version 52200 2016-07-02 22:20:37 MSG sb_traf[29582]: # Start reading the write-ahead log 'sql_logs/sql/log.81841' 2016-07-02 22:20:37 MSG sb_traf[29582]: # Finished reading the write-ahead log 'sql_logs/sql/log.81841' 2016-07-02 22:20:37 MSG sb_traf[29582]: # Finished processing logs sql/sql_logs 2016-07-02 22:20:37 MSG merovingian[28268]: database 'sb_traf' (29582) was killed by signal SIGSEGV 2016-07-02 22:20:37 ERR merovingian[28268]: client error: database 'sb_traf' appears to shut itself down after starting, check monetdbd's logfile for possible hints

4 5

python map
by Stefano Fioravanzo 04 Jul '16

04 Jul '16

Hello, I have a long and complex embedded python function. This function depends on the single rows of the input columns, so I would like to parallelize it. I’ve read about the post on embedded python and python_map. I understand that using python_map should automatically split (in a not specified way) the input columns and fire different (how many?) python processes executing on their input slice. Finally it should pack all the partial results. Well, I have tried to use python_map, but nothing different happened. Is there something more I have to do apart from setting LANGUAGE PYTHON_MAP? Regards, Stefano

2 9

Positional placeholders in prepared statement
by Frank Tetzel 29 Jun '16

29 Jun '16

Hi, does MonetDB support positional placeholders like '$1' instead of just '?' in prepared statements? I want to refer to a parameter more than once and possibly also out of order. Is there some alternative available? Regards, Frank

2 1

Resetting monetdb administrative password
by Roman S 29 Jun '16

29 Jun '16

Hello, Can somebody tell me how to reset monetdb administrative password? I’ve tried to stop mserver5, start it manually, oldp:=clients.getPasswordHash(“monetdb"); newp:=clients.backendsum("newpassword"); clients.changePassword(oldp, newp); Stoping it again and start in normal mode But it didn’t help: mclient --database=mydb --user=monetdb password:<usingnewpass> InvalidCredentialsException:checkCredentials:invalid credentials for user 'monetdb'

2 2

MERGE Tables works ?
by Ariel Abadi 28 Jun '16

28 Jun '16

Hi Everybody! Im facing a problem. We register telecomunications calls. As the volume is so big, we decided to store the data in different tables. The criteria we used for splitting the info was to create a table per each Switch and Day. So we have Sw1_20160601, Sw2_20160602...Sw3_20160620..etc etc Each table has aprox 15MM rows. In order to create some reports we have created a *MERGE TABLE *and added to this table the 90 tables (3 switches x 30 days). BUT, now Im trying to run a very simple query, like SELECT cdr_date FROM xxxx LIMIT 3; To get at least 3 rows (just to see that this schema works). It took 21 mins to give me those 3 rows.... Am I doing something wrong? Rgds Ariel

3 4

Announcement: New Jun2016 Feature release of MonetDB suite
by Sjoerd Mullender 23 Jun '16

23 Jun '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jun2016 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/>. Jun 2016 feature release MonetDB5 Server * Extended the storage() table producing function to also accept storage([schemaname [, tablename [, columnname]]]) * The :bat[:oid,:any] type descriptor has been turned into its columnar version :bat[:any] * Converted the *.mal scripts into *.malC versions to prepare for removal of the mserver console. * Removed functions mat.hasMoreElements, mat.info, mat.mergepack, mat. newIterator, mat.project, mat.pack2, mat.sortReverse, mat.sort, and mat.slice. * Removed grouped aggregate functions from the aggr module in which the groups were indicated by the head column of the bat to be aggregated. Use the interface with a separate group bat instead. * The server now stops executing a query when the client disconnects. * Removed algebra.join. Use algebra.subjoin instead. * Removed algebra.antijoin. Use algebra.subantijoin or algebra.subthetajoin instead. * The MAL function 'leftfetchjoin' is renamed to its relational algebra version 'projection' * The generic property handling scheme has been removed. It was used in just a few places, for widely different purposes and contained cruft. * Removed functions str.iconv and str.codeset. Internally, strings are always in UTF-8 encoding, so we can't allow code set conversions. * Removed algebra.like with a BAT argument. Use algebra.likesubselect instead. * Removed algebra.leftjoin. Use algebra.subleftjoin or algebra.leftfetchjoin instead. * Removed algebra.tdiff and algebra.tinter. * Removed algebra.sample. Use sample.subuniform instead. * Removed algebra.select. Use algebra.subselect instead. * Removed algebra.revert. * Removed bat.order and bat.orderReverse functions. * Changed client.getUsers to return two bats, one with the user id and one with the user name. * Implemented algebra.subdiff and algebra.subinter. * Removed algebra.tdifference and algebra.tintersect. * Removed algebra.tunion. Build Environment * A new package MonetDB-lidar (Fedora) or libmonetdb5-server-lidar (Debian/Ubuntu) has been created to work with LiDAR data. * Implemented a systemd configuration file for a monetdbd.service on systems that support it (Fedora, newer Ubuntu). Client Package * The Ruby client is now in a separate repository (http://dev.monetdb.org/hg/monetdb-ruby) and released independently. Geom Module * The geom module has been completely overhauled. Types are now specified as GEOMETRY(POINT) instead of POINT, old functions have been removed, new ones introduced. However, even with all the changes, a database upgrade should still be possible (as always, make a backup first). MonetDB Common * Removed BATconst. Use BATconstant instead. * Changed BATconstant. It now has a new first argument with the seqbase for the head column. * Removed BATmmap. It was no longer used. * BUNdelete and BATdel don't accept a foce argument and only allow deleting values that have not yet been committed. BUNdelete exchanges the deleted value with the last value (if the deleted value isn't the last value). BATdel compacts the BAT by shifting values after the deleted values up. The list of to-be-deleted values in BATdel must be sorted and unique. * Removed BUNreplace from list of exported functions. It wasn't used, and there is still BUNinplace and void_inplace that do more-or-less the same thing. * Changed BATderiveHeadProps to BATderiveTailProps (and it now works on the tail column). * Removed unused functions BATalpha, BATdelta, and BATprev. * Removed function VIEWcombine. Use BATdense instead. * Removed "left" parameter from BUNinplace. It wasn't used since the BAT it works on is dense headed. * Created function BATdense to easily create a [void,void] BAT with specified seqbases for head and tail and a count. * Removed function BATmark. When all head columns are dense, BATmark basically only created a new [void,void] BAT. * Renamed BATsubsort to BATsort. * Removed "sub" from the names of the function BATsubselect, BATthetasubselect, BATsubcross, BATsubleftjoin, BATsubouterjoin, BATsubthetajoin, BATsubsemijoin, BATsubdiff, BATsubjoin, BATsubbandjoin, BATsubrangejoin, and BATsubunique. * Removed BATsubleftfetchjoin: it was not used. * Removed BATcross1. Use BATsubcross instead. * Removed all versions of the SORTloop macro. * Removed Batkdiff. Use BATsubdiff instead. * Removed BATselect. Use BATsubselect instead. * Removed BATsemijoin. Use BATsubsemijoin instead. * Removed BATjoin. Use BATsubjoin instead. * Removed BATleftjoin. Use BATsubleftjoin or BATproject instead. * Removed BATleftfetchjoin. Use BATproject instead. * Removed BUNins from the list of exported functions. * Removed legacy functions BATuselect and BATuselect_. * Removed legacy functions BATantijoin, BATbandjoin, BATouterjoin, BATrangejoin, and BATthetajoin. * Removed function BATrevert. * BATordered now works on the TAIL column. * Removed obsolete functions BATorder() and BATorder_rev(). * Implemented BATsubdiff which returns a list of OIDs (sorted, i.e. usable as candidate list) of tuples in the left input whose value does not occur in the right input. * Removed function BATkintersect. It wasn't used anymore. It's functionality can be obtained by using BATsubsemijoin. * Removed the function BATkunion. Java Module * Fixed problem in DatabaseMetaData.getUDTs() when it was called with types parameter filled. It used to throw SQException with message: SELECT: identifier 'DATA_TYPE' unknown. Now it returns the UDTs which match the provided array of data types. * Implemented MonetDatabaseMetaData.supportsConvert() and MonetDatabaseMetaData.supportsConvert(int fromType, int toType) methods. It used to always return false. Now it returns true for the supported conversions. This fixes Bug 3460. * ResultSet.setFetchSize(): added a dummy implementation to get rid of the SQLFeatureNotSupportedException. In MonetDB, it does not make sense to set the fetch size of a result set. If one really wants to set the fetch size, one should use Statement.setFetchSize() instead. * Fixed resource leak in ResultSetMetaData. It created and cached a ResultSet object for each column but never closed the ResultSet objects. * Corrected DatabaseMetaData methods which accept a catalog filter argument. Those methods will now filter the results on the specified catalog name, whereas previously the catalog filter argument was ignored. * Corrected output of column KEY_SEQ of DatabaseMetaData methods: getPrimaryKeys(), getImportedKeys(), getExportedKeys() and getCrossReference(). It now starts at 1 instead of 0 previously. * Corrected DatabaseMetaData.getSchemas() by returning 2 instead of 3 columns. * Improved DatabaseMetaData.getColumns() by returning two additional columns: IS_AUTOINCREMENT and IS_GENERATEDCOLUMN. * Improved DatabaseMetaData.getTypeInfo(). It now returns better information on LITERAL_PREFIX, LITERAL_SUFFIX, CREATE_PARAMS, CASE_SENSITIVE, FIXED_PREC_SCALE and MAXIMUM_SCALE for some data types. Also the returned rows are now ordered by DATA_TYPE, TYPE_NAME, PRECISION as required by the specs. Also corrected output column names "searchable" into "SEARCHABLE" and "MAXIMUM SCALE" into "MAXIMUM_SCALE" to match the JDBC specification. * Corrected DatabaseMetaData.getPseudoColumns(). It used to return 12 empty rows. Now it returns no rows as MonetDB does not have pseudo columns. * Implemented method DatabaseMetaData.getClientProperties(). It used to always return a resultset with 4 completely empty rows. It now returns a resultset with the possible connection properties. * Implemented method DatabaseMetaData.getUDTs(). It used to return an empty resultset. Now it returns the User Defined Types such as inet, json, url and uuid. * Corrected the returned table types in DatabaseMetaData.getTableTypes(). It now returns all 10 table types (as stored in sys.table_types) instead of the previously 8 hardcoded table types. For old MonetDB servers which do not have the sys.table_types table, the old behavior is retained. * Implemented methods DatabaseMetadata.getProcedures() and DatabaseMetadata.getProcedureColumns(). They used to return an empty resultset. Now they return the expected Procedures and ProcedureColumns. Also getProcedureColumns() now returns a resultset with all 20 columns instead of 13 columns previously. * Method getFunctionColumns() in DatabaseMetadata used to throw an SQLException: getFunctionColumns(String, String, String, String) is not implemented. This method is now implemented and returns a resultset. * Method getFunctions() in DatabaseMetadata used to throw an SQLException: SELECT: no such column 'functions.sql' This has been corrected. It now returns a resultset as requested. * The resultsets of DatabaseMetadata methods now no longer return a value for the *_CAT columns as MonetDB does not support Catalogs. * Fixed a memory leak in MonetDatabaseMetaData.java for a static cache which kept references to closed Connection objects. * Improved JdbcClient program when presenting query data to console. It used to send an SQL catalog query for each query result column which slowed down the interactive response considerably. These additional SQL catalog queries have been eliminated. * Corrected MonetResultSet.getObject(String columnName). It no longer throws a NullPointerException in cases where internally a MonetVirtualResultSet is used. * Fixed Connection.isValid(): this method should never attempt to close the connection, even if an error has occurred. SQL * Disallow GRANT <some-user> TO <role-or-use>. Only explicitly created roles can be granted. * Extended grantable schema privileges: when a user is granted the "sysadmin" role, the user now hcan not only create schemas, but also drop schemas. * Introduced COPY INTO/ COPY FROM global privileges. These privileges allows other users than 'monetdb' to be granted the privileges to use the MonetDB bulk data loading/exporting features, i.e., COPY FROM <file> and COPY INTO <file>. Bug Fixes * 2407: Merovingian: allow binds to given ip/interface * 2815: No SRID support * 3361: constants as MAL function parameters prevent intermediate reuse * 3374: UNIQUE constraint does not set tkey property on the corresponding BAT * 3460: incomplete implementation of JDBC driver supportsConvert(), supportsConvert(int fromType, int toType) methods in MonetDatabaseMetaData.java * 3711: JDBC connection using jdbcclient.jar is very slow * 3877: MonetDBLite should allow close then re-open databases? without restarting R * 3911: Invalid connect() call in 'redirect' mode * 3920: query with DISTINCT and correlated scalar subquery in SELECT list causes Assertion failure and crash of mserver5 * 3927: COUNT( distinct my_clob ) , QUANTILE( my_double , number ) fails in dev build * 3956: MonetDBLite unable to execute LIMIT 1 statement * 3972: Drastic Memory leak of 600GBs while generating plan for Query with 25 joins * 3974: Prepared statement rel_bin.c:2378: rel2bin_project: Assertion `0' failed. * 3975: Suspicious code in store_manager() on exit path * 3978: SQL returns TypeException 'aggr.subcorr' undefined for sys.corr function * 3980: JOIN with references on both sides crashes mserver * 3981: Incorrect LEFT JOIN when FROM clause contains nested subqueries * 3983: Creation of a Foreign Key which partially maps to a primary key is accepted without a warning * 3984: Multiple paths in the .profile * 3985: ruby-monetdb-sql gem fails for negative timezone offset (USA, etc.) * 3987: Segfault on malformed csv import * 3991: MonetDBLite feature request: default monetdb.sequential to FALSE on windows * 3994: MonetDBLite dbDisconnect with shutdown=TRUE freezes the console on windows * 3995: NullPointerException when calling getObject() * 3997: calling scalar functions sys.isaUUID(str) or sys.isaUUID(uuid) fail * 3999: length() returns wrong length for strings which have spaces at the end of the string * 4010: RELEASE SAVEPOINT after ALTER TABLE crashes mserver5 * 4011: sys.sessions.user column always shows 'monetdb' * 4013: GDKextendf does not free up memory when it fails due to insufficient resources * 4015: Daemon crashes on database release command

1 0

Announcement: New Jun2016 Feature release of MonetDB suite
by Sjoerd Mullender 23 Jun '16

23 Jun '16

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jun2016 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/>. Jun 2016 feature release MonetDB5 Server * Extended the storage() table producing function to also accept storage([schemaname [, tablename [, columnname]]]) * The :bat[:oid,:any] type descriptor has been turned into its columnar version :bat[:any] * Converted the *.mal scripts into *.malC versions to prepare for removal of the mserver console. * Removed functions mat.hasMoreElements, mat.info, mat.mergepack, mat. newIterator, mat.project, mat.pack2, mat.sortReverse, mat.sort, and mat.slice. * Removed grouped aggregate functions from the aggr module in which the groups were indicated by the head column of the bat to be aggregated. Use the interface with a separate group bat instead. * The server now stops executing a query when the client disconnects. * Removed algebra.join. Use algebra.subjoin instead. * Removed algebra.antijoin. Use algebra.subantijoin or algebra.subthetajoin instead. * The MAL function 'leftfetchjoin' is renamed to its relational algebra version 'projection' * The generic property handling scheme has been removed. It was used in just a few places, for widely different purposes and contained cruft. * Removed functions str.iconv and str.codeset. Internally, strings are always in UTF-8 encoding, so we can't allow code set conversions. * Removed algebra.like with a BAT argument. Use algebra.likesubselect instead. * Removed algebra.leftjoin. Use algebra.subleftjoin or algebra.leftfetchjoin instead. * Removed algebra.tdiff and algebra.tinter. * Removed algebra.sample. Use sample.subuniform instead. * Removed algebra.select. Use algebra.subselect instead. * Removed algebra.revert. * Removed bat.order and bat.orderReverse functions. * Changed client.getUsers to return two bats, one with the user id and one with the user name. * Implemented algebra.subdiff and algebra.subinter. * Removed algebra.tdifference and algebra.tintersect. * Removed algebra.tunion. Build Environment * A new package MonetDB-lidar (Fedora) or libmonetdb5-server-lidar (Debian/Ubuntu) has been created to work with LiDAR data. * Implemented a systemd configuration file for a monetdbd.service on systems that support it (Fedora, newer Ubuntu). Client Package * The Ruby client is now in a separate repository (http://dev.monetdb.org/hg/monetdb-ruby) and released independently. Geom Module * The geom module has been completely overhauled. Types are now specified as GEOMETRY(POINT) instead of POINT, old functions have been removed, new ones introduced. However, even with all the changes, a database upgrade should still be possible (as always, make a backup first). MonetDB Common * Removed BATconst. Use BATconstant instead. * Changed BATconstant. It now has a new first argument with the seqbase for the head column. * Removed BATmmap. It was no longer used. * BUNdelete and BATdel don't accept a foce argument and only allow deleting values that have not yet been committed. BUNdelete exchanges the deleted value with the last value (if the deleted value isn't the last value). BATdel compacts the BAT by shifting values after the deleted values up. The list of to-be-deleted values in BATdel must be sorted and unique. * Removed BUNreplace from list of exported functions. It wasn't used, and there is still BUNinplace and void_inplace that do more-or-less the same thing. * Changed BATderiveHeadProps to BATderiveTailProps (and it now works on the tail column). * Removed unused functions BATalpha, BATdelta, and BATprev. * Removed function VIEWcombine. Use BATdense instead. * Removed "left" parameter from BUNinplace. It wasn't used since the BAT it works on is dense headed. * Created function BATdense to easily create a [void,void] BAT with specified seqbases for head and tail and a count. * Removed function BATmark. When all head columns are dense, BATmark basically only created a new [void,void] BAT. * Renamed BATsubsort to BATsort. * Removed "sub" from the names of the function BATsubselect, BATthetasubselect, BATsubcross, BATsubleftjoin, BATsubouterjoin, BATsubthetajoin, BATsubsemijoin, BATsubdiff, BATsubjoin, BATsubbandjoin, BATsubrangejoin, and BATsubunique. * Removed BATsubleftfetchjoin: it was not used. * Removed BATcross1. Use BATsubcross instead. * Removed all versions of the SORTloop macro. * Removed Batkdiff. Use BATsubdiff instead. * Removed BATselect. Use BATsubselect instead. * Removed BATsemijoin. Use BATsubsemijoin instead. * Removed BATjoin. Use BATsubjoin instead. * Removed BATleftjoin. Use BATsubleftjoin or BATproject instead. * Removed BATleftfetchjoin. Use BATproject instead. * Removed BUNins from the list of exported functions. * Removed legacy functions BATuselect and BATuselect_. * Removed legacy functions BATantijoin, BATbandjoin, BATouterjoin, BATrangejoin, and BATthetajoin. * Removed function BATrevert. * BATordered now works on the TAIL column. * Removed obsolete functions BATorder() and BATorder_rev(). * Implemented BATsubdiff which returns a list of OIDs (sorted, i.e. usable as candidate list) of tuples in the left input whose value does not occur in the right input. * Removed function BATkintersect. It wasn't used anymore. It's functionality can be obtained by using BATsubsemijoin. * Removed the function BATkunion. Java Module * Fixed problem in DatabaseMetaData.getUDTs() when it was called with types parameter filled. It used to throw SQException with message: SELECT: identifier 'DATA_TYPE' unknown. Now it returns the UDTs which match the provided array of data types. * Implemented MonetDatabaseMetaData.supportsConvert() and MonetDatabaseMetaData.supportsConvert(int fromType, int toType) methods. It used to always return false. Now it returns true for the supported conversions. This fixes Bug 3460. * ResultSet.setFetchSize(): added a dummy implementation to get rid of the SQLFeatureNotSupportedException. In MonetDB, it does not make sense to set the fetch size of a result set. If one really wants to set the fetch size, one should use Statement.setFetchSize() instead. * Fixed resource leak in ResultSetMetaData. It created and cached a ResultSet object for each column but never closed the ResultSet objects. * Corrected DatabaseMetaData methods which accept a catalog filter argument. Those methods will now filter the results on the specified catalog name, whereas previously the catalog filter argument was ignored. * Corrected output of column KEY_SEQ of DatabaseMetaData methods: getPrimaryKeys(), getImportedKeys(), getExportedKeys() and getCrossReference(). It now starts at 1 instead of 0 previously. * Corrected DatabaseMetaData.getSchemas() by returning 2 instead of 3 columns. * Improved DatabaseMetaData.getColumns() by returning two additional columns: IS_AUTOINCREMENT and IS_GENERATEDCOLUMN. * Improved DatabaseMetaData.getTypeInfo(). It now returns better information on LITERAL_PREFIX, LITERAL_SUFFIX, CREATE_PARAMS, CASE_SENSITIVE, FIXED_PREC_SCALE and MAXIMUM_SCALE for some data types. Also the returned rows are now ordered by DATA_TYPE, TYPE_NAME, PRECISION as required by the specs. Also corrected output column names "searchable" into "SEARCHABLE" and "MAXIMUM SCALE" into "MAXIMUM_SCALE" to match the JDBC specification. * Corrected DatabaseMetaData.getPseudoColumns(). It used to return 12 empty rows. Now it returns no rows as MonetDB does not have pseudo columns. * Implemented method DatabaseMetaData.getClientProperties(). It used to always return a resultset with 4 completely empty rows. It now returns a resultset with the possible connection properties. * Implemented method DatabaseMetaData.getUDTs(). It used to return an empty resultset. Now it returns the User Defined Types such as inet, json, url and uuid. * Corrected the returned table types in DatabaseMetaData.getTableTypes(). It now returns all 10 table types (as stored in sys.table_types) instead of the previously 8 hardcoded table types. For old MonetDB servers which do not have the sys.table_types table, the old behavior is retained. * Implemented methods DatabaseMetadata.getProcedures() and DatabaseMetadata.getProcedureColumns(). They used to return an empty resultset. Now they return the expected Procedures and ProcedureColumns. Also getProcedureColumns() now returns a resultset with all 20 columns instead of 13 columns previously. * Method getFunctionColumns() in DatabaseMetadata used to throw an SQLException: getFunctionColumns(String, String, String, String) is not implemented. This method is now implemented and returns a resultset. * Method getFunctions() in DatabaseMetadata used to throw an SQLException: SELECT: no such column 'functions.sql' This has been corrected. It now returns a resultset as requested. * The resultsets of DatabaseMetadata methods now no longer return a value for the *_CAT columns as MonetDB does not support Catalogs. * Fixed a memory leak in MonetDatabaseMetaData.java for a static cache which kept references to closed Connection objects. * Improved JdbcClient program when presenting query data to console. It used to send an SQL catalog query for each query result column which slowed down the interactive response considerably. These additional SQL catalog queries have been eliminated. * Corrected MonetResultSet.getObject(String columnName). It no longer throws a NullPointerException in cases where internally a MonetVirtualResultSet is used. * Fixed Connection.isValid(): this method should never attempt to close the connection, even if an error has occurred. SQL * Disallow GRANT <some-user> TO <role-or-use>. Only explicitly created roles can be granted. * Extended grantable schema privileges: when a user is granted the "sysadmin" role, the user now hcan not only create schemas, but also drop schemas. * Introduced COPY INTO/ COPY FROM global privileges. These privileges allows other users than 'monetdb' to be granted the privileges to use the MonetDB bulk data loading/exporting features, i.e., COPY FROM <file> and COPY INTO <file>. Bug Fixes * 2407: Merovingian: allow binds to given ip/interface * 2815: No SRID support * 3361: constants as MAL function parameters prevent intermediate reuse * 3374: UNIQUE constraint does not set tkey property on the corresponding BAT * 3460: incomplete implementation of JDBC driver supportsConvert(), supportsConvert(int fromType, int toType) methods in MonetDatabaseMetaData.java * 3711: JDBC connection using jdbcclient.jar is very slow * 3877: MonetDBLite should allow close then re-open databases? without restarting R * 3911: Invalid connect() call in 'redirect' mode * 3920: query with DISTINCT and correlated scalar subquery in SELECT list causes Assertion failure and crash of mserver5 * 3927: COUNT( distinct my_clob ) , QUANTILE( my_double , number ) fails in dev build * 3956: MonetDBLite unable to execute LIMIT 1 statement * 3972: Drastic Memory leak of 600GBs while generating plan for Query with 25 joins * 3974: Prepared statement rel_bin.c:2378: rel2bin_project: Assertion `0' failed. * 3975: Suspicious code in store_manager() on exit path * 3978: SQL returns TypeException 'aggr.subcorr' undefined for sys.corr function * 3980: JOIN with references on both sides crashes mserver * 3981: Incorrect LEFT JOIN when FROM clause contains nested subqueries * 3983: Creation of a Foreign Key which partially maps to a primary key is accepted without a warning * 3984: Multiple paths in the .profile * 3985: ruby-monetdb-sql gem fails for negative timezone offset (USA, etc.) * 3987: Segfault on malformed csv import * 3991: MonetDBLite feature request: default monetdb.sequential to FALSE on windows * 3994: MonetDBLite dbDisconnect with shutdown=TRUE freezes the console on windows * 3995: NullPointerException when calling getObject() * 3997: calling scalar functions sys.isaUUID(str) or sys.isaUUID(uuid) fail * 3999: length() returns wrong length for strings which have spaces at the end of the string * 4010: RELEASE SAVEPOINT after ALTER TABLE crashes mserver5 * 4011: sys.sessions.user column always shows 'monetdb' * 4013: GDKextendf does not free up memory when it fails due to insufficient resources * 4015: Daemon crashes on database release command

1 0

MonetDB vs. the new MariaDB ColumnStore
by Arno Mittelbach 20 Jun '16

20 Jun '16

Hi all, MariaDB has released a first alpha version of their new ColumnStore (which is essentially InfiniDB). We have done a first tentative performance test and did a comparison of MariaDB ColumnStore vs. MonetDB. For most of the test cases, MonetDB wins hands down. If you are interested in the numbers, have a look at https://reportserver.net/blog/2016/06/20/mariadb-columnstore-vs-innodb-vs-m… Best Regards, Arno

1 0

NULL value with embedded py
by Stefano Fioravanzo 20 Jun '16

20 Jun '16

Hello, I am creating a table using the result of an embedded py function. The result of the function is a dictionary composed of numpy arrays. The function and the CREATE TABLE work well. Inside the arrays of the dictionary, I have a few np.nan values, they seem to be recognized by monet during the creation of the table. In fact, when I query the whole table, I can see ‘null’ in the proper places. But if I try to compare against these null values, I get nothing (SELECT * FROM table where field is null). After a few trials, it seems to me that monet understands that np.nan is a NULL value, but then when it inserts the value into the table the internal representation (?) is somewhat different form a ‘normal’ NULL value. Any suggestion? Thanks, Stefano

3 2

Recover an inconsistent database?
by Vijay Krishna 20 Jun '16

20 Jun '16

Hi, I'm getting the following error while starting a database. *!FATAL: Logger_new: inconsistent database, catalog does not exist* Is there any means to recover this database with data and start it? Or, the only way is to create a new database? (P.S.: There is no backup of data) Thanks & Regards, Vijay.

1 0