- users-list - monetdb.org

Re: how to know imprints take effect?
by integrity 20 Jul '17

20 Jul '17

Hi Stefan, Nice talking to you again! Sorry my query in the image didn't show up, so I paste it in text now. The query is a simple select with equals. ======================================== sql>select * from id14 where id=1461; +------+-------+------+ | id | label | cid | +======+=======+======+ | 1461 | false | 0 | +------+-------+------+ 1 tuple (104.549ms) sql> sql> sql>select row_number() over (),* from storage() where "table"='id14'; +------+--------+-------+--------+---------+----------+----------+---------+-----------+------------+----------+----------+-------+----------+--------+-----------+--------+----------+------+ | L5 | schema | table | column | type | mode | location | count | typewidth | columnsize | heapsize | hashes | phash | imprints | sorted | revsorted | unique | orderidx | L5 | +======+========+=======+========+=========+==========+==========+=========+===========+============+==========+==========+=======+==========+========+===========+========+==========+======+ | 1 | sys | id14 | id | bigint | writable | 12/1244 | 2811606 | 8 | 22492848 | 0 | 28049448 | false | 0 | false | false | null | 0 | 1 | | 2 | sys | id14 | label | boolean | writable | 02/254 | 2811606 | 1 | 2811606 | 0 | 0 | false | 0 | null | null | null | 0 | 2 | | 3 | sys | id14 | cid | bigint | writable | 12/1231 | 2811606 | 8 | 22492848 | 0 | 0 | false | 0 | false | false | null | 0 | 3 | +------+--------+-------+--------+---------+----------+----------+---------+-----------+------------+----------+----------+-------+----------+--------+-----------+--------+----------+------+ 3 tuples (10.090ms) ===================================================== Best, Meng ------------------ Original ------------------ From: "Stefan Manegold";<Stefan.Manegold(a)cwi.nl>; Date: Jul 19, 2017 To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Subject: Re: how to know imprints take effect? Hi Meng, most important question: From what kind of queries do you expect millisecond responses over a table of 15,000,000 rows? Thanks! Best, Stefan ----- On Jul 19, 2017, at 12:37 PM, integrity 357416268(a)qq.com wrote: > Hi all, > > I am back, and I am using MonetDB Dec2016-SP5 with "enable-optimize" to do graph > computing. I remember that imprints are suitable for all numeric types, > however, after querying on the bigint column for many times, I found there is > no imprints. > > > > What I want to achieve is to "make" MonetDB respond in milliseconds, although I > know that in MonetDB it is not necessary to create index like imprints. > So, on a table of 15,000,000 rows, can I expect to get response in milliseconds? > and whether imprints will help with this? > > Any comments is appreciated! > > Best regards, > Meng > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > https://www.monetdb.org/mailman/listinfo/users-list -- | Stefan.Manegold(a)CWI.nl | DB Architectures (DA) | | www.CWI.nl/~manegold/ | Science Park 123 (L321) | | +31 (0)20 592-4212 | 1098 XG Amsterdam (NL) | _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

2 1

how to know imprints take effect?
by integrity 19 Jul '17

19 Jul '17

Hi all, I am back, and I am using MonetDB Dec2016-SP5 with "enable-optimize" to do graph computing. I remember that imprints are suitable for all numeric types, however, after querying on the bigint column for many times, I found there is no imprints. What I want to achieve is to "make" MonetDB respond in milliseconds, although I know that in MonetDB it is not necessary to create index like imprints. So, on a table of 15,000,000 rows, can I expect to get response in milliseconds? and whether imprints will help with this? Any comments is appreciated! Best regards, Meng

2 1

Announcement: New Jul2017 Feature release of MonetDB suite
by Sjoerd Mullender 18 Jul '17

18 Jul '17

The MonetDB team at CWI/MonetDB BV is pleased to announce the Jul2017 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/>. Jul 2017 feature release MonetDB5 Server * The "sub" prefix of many functions, both at the MAL and the C level, has been removed. * Changed the interfaces of the AUTH* functions: pass values, not pointers to values. * Removed calc.setoid(). * group.subgroup is now called group.group if it is not refining a group. Both group.group and group.subgroup now also have variants with a candidate list. * The allocation schemes for MAL blocks and Variables has been turned into block-based. This reduces the number of malloc()/free() calls. . * Added a new server-side protocol implementation. The new protocol is backwards compatible with the old protocol. Clients can choose whether they want to use the old or the new protocol during the initial handshake with the server. The new protocol is a binary column-based protocol that is significantly faster than the old protocol when transferring large result sets. In addition, the new protocol supports compression using Snappy or LZ4. * Moved the sphinx extension module to its own repository. See https://dev.monetdb.org/hg/MonetDB-sphinx/. * Removed GSL module: it's now a separate (extension) package. See https://dev.monetdb.org/hg/MonetDB-gsl/. * The PCRE library is now optional for systems that support POSIX regular expressions. * Added 5 new sys schema tables: function_languages, function_types, key_types, index_types and privilege_codes. They are pre-loaded with static content and contain descriptive names for the various integer type and code values. See also sql/scripts/51_sys_schema_extension.sql Merovingian * Added handling of a dbextra property per database at the daemon level. The user can set the dbextra property for a database using the command: $ monetdb set dbextra=<path> <database> and the daemon will make sure to start the new server using the correct --dbextra parameter. Client Package * The mclient and msqldump programs lost compatibility with old mserver5 versions (pre 2014) which didn't have a "system" column in the sys.schemas table. * The mclient and msqldump programs lost compatibility with ancient mserver5 versions (pre 2011) which didn't have the sys.systemfunctions table. * Removed the "array" and "quick" functions from the mapi library. To be precise, the removed functions are: mapi_execute_array, mapi_fetch_field_array, mapi_prepare_array, mapi_query_array, mapi_quick_query, mapi_quick_query_array, and mapi_quick_response. * Added a more elaborate \help command for SQL expressions. MonetDB Common * Improved error checking in the logger code (dealing with the write-ahead log); changed return types a several functions from int to gdk_return (i.e., they now return GDK_SUCCEED or GDK_FAIL). The logger no longer calls GDKfatal on error. Instead the caller is responsible for dealing with errors. * BATsort may now create an order index as a by product. * Quantile calculations now use the order index if available (and use BATsort otherwise, producing an order index). * Quantiles calculate a position in the sorted column. If this position is not an integer, we now choose the nearest position, favoring the lower if the distance to the two adjacent positions is equal (round down to nearest integer). * Removed function BATprintf. Use BATprint or BATprintcolumns instead. * Removed BATsave from the list of exported functions. * Replaced BBPincref/BBPdecref with BBPfix/BBPunfix for physical reference count and BBPretain/BBPrelease for logical reference count maintenance. * Removed automatic conversion of 32-bit OIDs to 64 bits on 64-bit architectures. * Removed functions OIDbase() and OIDnew(). * Removed talign field from BAT descriptor. * BATappend now takes an optional (NULL if not used) candidate list for the to-be-appended BAT. * New function BATkeyed(BAT *b) that determines (possibly using a hash table) whether all values in b are distinct. SQL * Made the operator precedence of % equal to those of * and /. All three are evaluated from left to right. * Removed table sys.connections. It was a remnant of an experimental change that had already been removed in 2012. * Protect against runaway profiler events If you hit a barrier block during profiling, the JSON event log may quickly become unwieldy. Event production is protected using a high water mark, which ensures that never within the single execution of MAL block the instruction causes excessive event records. Bug Fixes * 3465: Request: add support for CREATE VIEW with ORDER BY clause * 3545: monetdb commands don't work with -h -P -p options (locally and remotely) * 3996: select * from sys.connections always returns 0 rows. Expected to see at least one row for the active connection. * 6187: Nested WITH queries not supported * 6225: Order of evaluation of the modulo operator * 6289: Crashes and hangs with remote tables * 6292: Runaway SQL optimizer in too many nested operators * 6310: Name resolution error (sqlsmith) * 6312: Object not found in LIMIT clause (sqlsmith) * 6313: Null type resolution in disjunction fails (sqlsmith) * 6319: Server crash on LATERAL (sqlsmith) * 6322: Crash on disjunction with LIMIT (sqlsmith) * 6323: Deadlock calling sys.bbp() * 6324: Sqlitelogictest crash in a IN query (8th) * 6327: The daemon does not respect the actual name of the mserver5 executable * 6330: Sqlitelogictest crash on a complex SELECT query * 6331: sys.statistics column "nils" always contains 0. Expected a positive value for columns that have one or more nils/NULLs * 6332: Sqlitelogictest crash related to an undefined MAL function

3 3

possible to change the decimal character from "." to ", " for the COPY INTO command?
by Anthony Damico 18 Jul '17

18 Jul '17

hi, i'm curious if this is possible to implement? not seeing it in the docs.. for example, in R you can use read.csv() which uses , as the delimiter and . as the decimal or you can use read.csv2 which uses ; as the delimiter and , as the decimal thanks all

1 0

Add entry in a table only if that entry does not already exist
by Stefano Piani 14 Jul '17

14 Jul '17

Dear monetdb users, I started to use MonetDB a few weeks ago and, until now, I have been able to use it proficiently. Unfortunately, I have faced a small issue that I have not been able to figure out how to fix. I collect data generated by different users. A unique id is associated to each user. I have a specific table (called "user") where I save the name and some other information about the user. In this table, the ID is a primary key. When I collect the data, I want to check if the specific user who generated that data is already saved in the user table. If this is not the case, I want to add a row to the user table for this user. Unfortunately, I have not been able to accomplish this operation in one SQL query and, therefore, I have been forced to query for the user id and, if the answer is negative, add the user using another query. The following is the python script that perform the operations. def save_user_in_database(user, cursor): db_line = 'SELECT 1 FROM users WHERE user_id = {}'.format(user.id) LOGGER.info('Executing on DB: %s', db_line) cursor.execute(db_line) answer = cursor.fetchall() LOGGER.info('Get this answer %s', answer) if len(answer) == 0: LOGGER.info('Inserting new user') db_line = sql_cmd_from_template( USER_TABLE_INSERT_TEMPLATE, user.id, user.name, user.description, user.location ) LOGGER.info('Executing on DB: %s', db_line) cursor.execute(db_line) This function is executed inside a single threaded application. Moreover, there are no "DELETE" or similar operations inside this script (it only adds rows to the database). No user (human or software) but this script connects to my MonetDB instance. Unfortunately, after I while that the script is running (let's say one or two days), something unexpected happens. For example, this is the log generated for user 42847029 (there are a lot of other lines in between that I removed). As you can see, after a while, my check fails and I don't understand why. 2017/06/13 09:30:01 - INFO - save_user_in_database: Executing on DB: SELECT 1 FROM users WHERE user_id = 42847029 2017/06/13 09:30:01 - INFO - save_user_in_database: Get this answer [(1,)] 2017/06/13 10:00:04 - INFO - save_user_in_database: Executing on DB: SELECT 1 FROM users WHERE user_id = 42847029 2017/06/13 10:00:04 - INFO - save_user_in_database: Get this answer [(1,)] 2017/06/13 10:02:33 - INFO - save_user_in_database: Executing on DB: SELECT 1 FROM users WHERE user_id = 42847029 2017/06/13 10:02:33 - INFO - save_user_in_database: Get this answer [(1,)] 2017/06/13 10:30:36 - INFO - save_user_in_database: Executing on DB: SELECT 1 FROM users WHERE user_id = 42847029 2017/06/13 10:30:36 - INFO - save_user_in_database: Get this answer [] 2017/06/13 10:30:36 - INFO - save_user_in_database: Inserting new user 2017/06/13 10:30:36 - INFO - save_user_in_database: Executing on DB: INSERT INTO users(user_id, user_name, user_description, user_location) VALUES (42847029, 'XXXX', 'XXXX', 'XXXX') 2017/06/13 10:30:36 - WARNING - on_data: Error elaborating data Traceback (most recent call last): File "data_collector.py", line 267, in on_data return self.elaborate_data(data) File "data_collector.py", line 354, in elaborate_data save_user_in_database(user, cursor) File "data_collector.py", line 234, in save_user_in_database cursor.execute(db_line) File "/opt/Python-3.6.0/lib/python3.6/site-packages/pymonetdb-1.0.4-py3.6.egg/pymonetdb/sql/cursors.py", line 164, in execute block = self.connection.execute(query) File "/opt/Python-3.6.0/lib/python3.6/site-packages/pymonetdb-1.0.4-py3.6.egg/pymonetdb/sql/connections.py", line 133, in execute return self.command('s' + query + ';') File "/opt/Python-3.6.0/lib/python3.6/site-packages/pymonetdb-1.0.4-py3.6.egg/pymonetdb/sql/connections.py", line 138, in command return self.mapi.cmd(command) File "/opt/Python-3.6.0/lib/python3.6/site-packages/pymonetdb-1.0.4-py3.6.egg/pymonetdb/mapi.py", line 235, in cmd raise exception(string) pymonetdb.exceptions.IntegrityError: INSERT INTO: PRIMARY KEY constraint 'users.users_user_id_pkey' violated Have you any advice? Is there a way to perform the same check in just one SQL line? Is this way atomic (if a would ever move to a multithreading script)? Am I doing something wrong with my current code? Please, take into account that I do not have a deep experience with other SQL tools so it is perfectly possible that I miss something that for you is extremely obvious. Thank you in advance! Best regards, Stefano Piani

2 3

Drop a local temporary table within a function
by Luis Larios 14 Jul '17

14 Jul '17

Hi, all, Consider the following scenario. I have a bunch of user defined functions, f_i, that create, populate, and use a local temporary table with the same name "res": create function f_i() returns table( ... ) begin # Result stats table. create local temporary table res( param1 int, param2 int ); /* Populate res */ /* Use res in some queries */ return table( /* Query involving res */ ); end; My first question is: Does "create local temporary table x" create a table that is disposed/dropped as soon as the function where it was declared finishes execution? I assumed this was the case, but I'm now skeptical as we are running into all sorts of concurrency issues that point to the "res" table not being cleared out properly from memory. This takes me to the second question: How do I drop a temporary table if such table exists? The reason for this is to prevent "table create" exceptions (which we are currently running into). I'd like to achieve something along these lines: create function f_i() returns table( ... ) begin # Drop local temporary table if such table exists. drop local temporary table res; <-------- How do I do this in MonetDB? # Result stats table. create local temporary table res( param1 int, param2 int ); /* Populate res */ /* Use res in some queries */ return table( /* Query involving res */ ); end; Where, as you can see, I want to explicitly drop the local temporary table "res" if such exists. If I leave the "local temporary" markers in the drop statement, MonetDB complains that it found unexpected "LOCAL" or "TEMPORARY". I wouldn't like to remove these markers because a statement like: drop table res; will drop any table in the default function schema with that name. Any help and hints are well appreciated! Thanks for the help in advance ~ Luis Angel 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.

3 2

Jul2015-SP4 vs. Dec2016-SP5
by Anderson, David B 13 Jul '17

13 Jul '17

All, I know I've skipped multiple releases so this is a difficult question to answer, but should I expect the Dec2016-SP5 version of MonetDB to be faster than/equal to the Jul2015-SP4 version? I have a parallel set of servers (on the same box, 24 cores 192GB ram, listening to different ports) operating on identical data sets, and the Dec2016 version is slower than the Jul2015 version (~15-20%). My queries are basic joins between two tables with grouping. I have top running on the same box, and I can see that the Jul2015 server uses all 24 cores, but the Dec2016 server barely cracks 2-3. The MAL plans obtained by explain are totally different (which partly explains the cores being utilized because I can see blocks of 24 commands) and the length of the Dec2016 one is a fraction of the Jul2015 version. I guess the older version was inefficiently using all 24 cores, but the new version efficiently uses a smaller set of cores?? Thanks, Dave

2 2

script-friendly prepare statement
by Roberto Cornacchia 13 Jul '17

13 Jul '17

Hi there, I'm trying to script the execution of (generated) prepared queries. It does not look possible, because the identifier to use for the EXEC statement is returned dynamically by the PREPARE statement: sql>prepare select 1; execute prepared statement using: EXEC 73(...) This can obviously not be scripted. If I tried to parse the output of the prepare statement to retrieve the identifier, this would split PREPARE and EXEC in two different mclient sessions, losing the prepared statement. As far as I know, there is at the moment no syntax available for providing the identifier, something like: sql>prepare myid as select 1; (that wouldn't be a bad idea, would it?) Besides syntax extensions, I was wondering if there is any trick I could use at runtime with the current syntax to retrieve that identifier. Like, is it stored in some system table? Is it available as any undocumented variable? I could not find anything like that, but I might have missed it. Cheers, Roberto

3 4

adding an "insert into" completely changes output from explain
by Anderson, David B 13 Jul '17

13 Jul '17

All, Is it expected behavior if adding "insert into xxxx" at the beginning of a sql query completely alters the output of explain? Without the "insert into", explain returns 1490 tuples, but when I add it, explain only returns 88. I was expecting that the number would go up, i.e. that the original plan would be wrapped in some additional commands which would implement the "insert into" part. Here is my sql: explain insert into aggr select 'CHANNEL', s.product, s.coupon, s.productionyear, s.tpoflag, sum(1), sum(d.scheduled)/1000000, sum(d.realized )/1000000, round(100*(1-power(sum(d.realized)/sum(d.scheduled),12)),3) as cpr from stt s, dyn d where s.loanseqnum = d.loanseqnum group by s.product, s.coupon, s.productionyear, s.tpoflag having sum(d.scheduled) > 0 ;

2 1

Extremely slow order by queries
by Manish gupta 13 Jul '17

13 Jul '17

Hi, I have a scenario where data size is ~3M rows. My query is select C1, C2, C3 from T1 group by C1,C2,C3 This returns result in 30 Sec ( number of groups around 1 M ) While, if I modify the query to select C1, C2, C3 from T1 group by C1,C2,C3 Order by C1, C2 on the same dataset, it takes ~ 1 hour to get result. The platforms I tried is both widows 64 bit and Linux 64 bit, with latest monet installed. Both the machines have 4 cores. And the behavior is same on both the machines. I am really surprised to see overhead of second query. One more observation is that this query occupies only single core, while monet is free to utilize 4 cores. Regards, Manish

2 1

Mserver returns with "memory exhausted in" error if query size exceeds 1.2MB
by Sharma, Sreejith 08 Jul '17

08 Jul '17

Hi, I have encountered scenario where SQL generated exceeds 3mb and it's failing in mserver with an error "memory exhausted in select .......". On further checks I have noticed that the SQL will go through only if SQL size is less than 1.2MB. Has anyone encountered this before? Or is anyone aware of any SQL size limitation in MonetDB? Please help. Regards, Sreejith

1 0

access MapiHdl by using qtag value from C API
by Anton Kravchenko 26 Jun '17

26 Jun '17

Is there a way to access MapiHdl of the query by using its qtag value?

1 0

Re: MonetDB taking lot of storage space
by Stefan Manegold 17 Jun '17

17 Jun '17

Typo in formula:does * columns * (average) width per columnmust readrows * columns * (average) width per column, -------- Original message --------From: Stefan Manegold <stefan.manegold(a)cwi.nl> Date: 6/17/17 12:33 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: MonetDB taking lot of storage space Hi Amir, May I assume "cr" stands for "crore" (c.f.., https://en.m.wikipedia.org/wiki/Crore) I.e. 1 cr = 10 million = 10^7 = 10,000,000 ? The plain expected size of the database assuming only fixed-width column types and ignoring automatically generated indexes, etc.Can be assumed as does * columns * (average) width per column, e.g.,Assuming all 4 byte numerical columns:(4+5) * 10^7 * 10 * 4 byte = 36 * 10^8 ~= 3.6 GBor twice as much for all 8 byte columns. Your 196 GB is far from that. Are you sure there is no other left-over data in your database?As Jennie suggested, did you do a lot of update (insert, delete) operations on these tables?Which (top-level) directories in your dbfarm are the largest? Best,Stefan -------- Original message --------From: Ying Zhang <Y.Zhang(a)cwi.nl> Date: 6/17/17 10:35 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: MonetDB taking lot of storage space Hai, MonetDB doesn’t provide any user definable compressions. What do you mean with “5cr rows”? Compressed Rows? 198GB is indeed pretty big, so it’s expected to contain a lot of table data. If you don’t have variable length columns, it’s easy to compute the expected storage space: #rows * #columns * (sum of the length of each column). Are your database fresh or a lot of insert and delete have been conducted on them? Then, the deletes might have leaved garbages. Did you happen to have put larges BLOBs or CLOBs in some columns? Which MonetDB version and OS do you use? Regards, Jennie > On 17 Jun 2017, at 09:38, Amit Ambekar <amit(a)g-square.in> wrote: > > Hi, > > i have 2 databases in monetdb. first one has 5cr rows and second has 4 cr rows with average 10 columns per table. It takes 196 GB storage space. is it normal ?? most of data consists of numeric values. And How i can compress database size in MonetDB ?? > > _______________________________________________ > 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

1 0

Re: MonetDB taking lot of storage space
by Stefan Manegold 17 Jun '17

17 Jun '17

Hi Amir, May I assume "cr" stands for "crore" (c.f.., https://en.m.wikipedia.org/wiki/Crore) I.e. 1 cr = 10 million = 10^7 = 10,000,000 ? The plain expected size of the database assuming only fixed-width column types and ignoring automatically generated indexes, etc.Can be assumed as does * columns * (average) width per column, e.g.,Assuming all 4 byte numerical columns:(4+5) * 10^7 * 10 * 4 byte = 36 * 10^8 ~= 3.6 GBor twice as much for all 8 byte columns. Your 196 GB is far from that. Are you sure there is no other left-over data in your database?As Jennie suggested, did you do a lot of update (insert, delete) operations on these tables?Which (top-level) directories in your dbfarm are the largest? Best,Stefan -------- Original message --------From: Ying Zhang <Y.Zhang(a)cwi.nl> Date: 6/17/17 10:35 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Subject: Re: MonetDB taking lot of storage space Hai, MonetDB doesn’t provide any user definable compressions. What do you mean with “5cr rows”? Compressed Rows? 198GB is indeed pretty big, so it’s expected to contain a lot of table data. If you don’t have variable length columns, it’s easy to compute the expected storage space: #rows * #columns * (sum of the length of each column). Are your database fresh or a lot of insert and delete have been conducted on them? Then, the deletes might have leaved garbages. Did you happen to have put larges BLOBs or CLOBs in some columns? Which MonetDB version and OS do you use? Regards, Jennie > On 17 Jun 2017, at 09:38, Amit Ambekar <amit(a)g-square.in> wrote: > > Hi, > > i have 2 databases in monetdb. first one has 5cr rows and second has 4 cr rows with average 10 columns per table. It takes 196 GB storage space. is it normal ?? most of data consists of numeric values. And How i can compress database size in MonetDB ?? > > _______________________________________________ > 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

1 0

MonetDB taking lot of storage space
by Amit Ambekar 17 Jun '17

17 Jun '17

Hi, i have 2 databases in monetdb. first one has 5cr rows and second has 4 cr rows with average 10 columns per table. It takes 196 GB storage space. is it normal ?? most of data consists of numeric values. And How i can compress database size in MonetDB ??

2 1

SQLsetTrace assumes in-place append
by Roberto Cornacchia 14 Jun '17

14 Jun '17

Background: I'm working on a side-effect-free branch of MonetDB. No in-place operations allowed. With bat.append() already changed this way, everything seemed to work so far, except TRACE. Even TRACE SELECT 1 would fail. I looked at it and the reason is that SQLsetTrace assumed in-place appends, thus first adding new empty bats to resultset, then filling in those bats. Everywhere else this is not assumed. The return bat of append is always created and used, even if it is the same of the first operand. The diff in attachment makes it work for both branches and is in my opinion more correct and consistent with the rest of the code. Best, Roberto

1 0

Announcement: New Dec2016-SP5 Bugfix release of MonetDB suite
by Sjoerd Mullender 12 Jun '17

12 Jun '17

The MonetDB team at CWI/MonetDB BV is pleased to announce the Dec2016-SP5 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/>. Dec 2016-SP5 bugfix release Bug Fixes * 6290: Crash (and assertion failure) with a correlated subquery with NOT IN in the select-list * 6291: crash if executes a function with sample operator * 6294: Sqlitelogictest crash * 6296: Another sqllitelogictest crash :( * 6297: 7th sqllitelogictest crash :( * 6300: Protect against missing BATs (sqlsmith) * 6314: Lateral crash report (sqlsmith) * 6315: Exist operator on type bigint missing (sqlsmith) * 6316: Coalesc and limit error (sqlsmith) * 6317: Two-column integer aggregation extremely slow * 6318: Daemon crashes if .merovignian_properties for a database contains more than 42 entries * 6320: The daemon ignores all but the last entry in the .merovignian_properties file when spawning mserver5

1 0

How to use prepared statements within a function
by Luis Larios 08 Jun '17

08 Jun '17

Hi, We’d like to know if there is a way to create and execute prepared statements within an SQL function. In particular, once that we use: “prepare select * from table” How can I retrieve the prepared statement ID to use in an exec()? Thanks, Luis Ángel. 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

Cannot execute embedded R in MonetDB
by Luis Larios 25 May '17

25 May '17

Hi, We are having issues when executing embedded R in a MonetDB SQL function. We have compiled and configured MonetDB accordingly, so as to be able to run embedded R; however, the simple example in the documentation fails to execute. This is the function we have: create function rapi00() returns table( d int ) language R { return seq(1,10); }; Having just "seq(1,10)" doesn't make any difference either. When this function gets created, we don't get any error; however, when we do the following: select d from rapi00() as r where d > 5; we get the this error: Query failed: TypeException:user.s6_3[3]:'rapi.eval' undefined in: X_4:bat[:int] := rapi.eval(7fdb9830e210:ptr,"{\n\treturn seq(1,10);\n};":str); We don't know how to tackle this issue. We have followed the process from https://www.monetdb.org/content/embedded-r-monetdb. Do you have any suggestion to make this work? Thanks! Luis Ángel. 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

binary bulk loading improvements?
by Lynn Carol Johnson 22 May '17

22 May '17

Hello Stefan and developers – Has any more thought been given to performance issues on binary bulk loading of large tables? I’m hoping to hear the “steal” option will be available in an upcoming release. Thanks - Lynn

1 0

Distributed Join and Replica Tables
by Kelly Burkhart 11 May '17

11 May '17

Can someone describe how exactly replica tables are used to push joins to worker nodes? Assume a master node and two worker nodes. I assume my reference table would go on my master node, and on my worker nodes I would have my large data table and replicas of the reference tables from the master node. Is that the general strategy? Thanks, -K

4 6

RE: Queries regarding embedded python
by Ganesh 08 May '17

08 May '17

Hi there, hope you are doing good. We were trying to implement Monetdb/python i.e. embedded python and tried the following code but its giving an error. (We have tried it on Linux) CREATE FUNCTION python_times_two(i INTEGER) RETURNS INTEGER LANGUAGE PYTHON { return i * 2 }; CREATE TABLE integers(i INTEGER); INSERT INTO integers VALUES (1), (2), (3), (4), (5); SELECT python_times_two(i) AS result FROM integers; Error: TypeException:user.s12_1[10]:'pyapi.eval' undefined in: X_18:int := pyapi.eval(0x7f051006a340:ptr,"{\n return i * 2\n};":str,X_17:int); Please help us on what should be done in order to code to work. Also need clarification on following points: 1. Does embedded R or embedded python work on windows? 2. Does it take data in memory when we use machine learning with Numpy and Scikitlearn? Awaiting for your response. Warm Regards, Ganesh Anbhule Data Scientist

2 1

Retrieving all schemata for a user
by Luis Larios 07 May '17

07 May '17

Hi, I wonder if anyone knows how I can retrieve all schemata on which a user has been granted permissions/authorization. I have checked most of the hinting tables I get from querying >> select id, name, schema_id, query from sys.tables where system = true; but I have had no luck with that. I only get the default schema for every user when I perform the following: >> select * from sys.users Any ideas or suggestions are welcome. Thanks! Luis Ángel. 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

Error while installing Monetdb
by Amit Ambekar 06 May '17

06 May '17

Hi, How much time it will take for new release ? On Wednesday 03 May 2017 03:30 PM, users-list-request(a)monetdb.org wrote: > Error while installing Monetdb

4 5

Problem with release
by Amit Ambekar 03 May '17

03 May '17

Hi, New release of monetdb is not installing on ubuntu 16.04 LTS. I think problem will be your new release which is happened on 1 May 2017. Change https://dev.monetdb.org/downloads/deb/dists/xenial/monetdb/binary-amd64/Rel…: Valid-Until: Tue, 30 Nov 2038 00:00:00 JST this is not working. JST to some other proper format

1 0