- users-list - monetdb.org

windows function
by imad hajj chahine 07 Oct '15

07 Oct '15

Hi, Since monetdb support window RANK functions, is it possible to extend the sql library to support also window aggregate function like SUM, AVG ... useful for running total and moving average. Any plan to support this in the near future or the implementation is easy to be done with user defined functions. Thanks.

2 4

UUIDs in MonetDB
by Doug Service 01 Oct '15

01 Oct '15

The documentation at https://www.monetdb.org/Documentation/Manuals/SQLreference/UUItype indicates that a UUID type is supported in MonetDB and gives the example select uuid(); +--------------------------------------+ | uuid | +======================================+ | 65950c76-a2f6-4543-660a-b849cf5f2453 | +--------------------------------------+ which appears to output from mclient. When I try the same with mclient I receive auto commit mode: on sql>select uuid(); SELECT: no such operator 'uuid' mapi.h only shows support for the following types #define MAPI_AUTO 0 /* automatic type detection */#define MAPI_TINY 1#define MAPI_UTINY 2#define MAPI_SHORT 3#define MAPI_USHORT 4#define MAPI_INT 5#define MAPI_UINT 6#define MAPI_LONG 7#define MAPI_ULONG 8#define MAPI_LONGLONG 9#define MAPI_ULONGLONG 10#define MAPI_CHAR 11#define MAPI_VARCHAR 12#define MAPI_FLOAT 13#define MAPI_DOUBLE 14#define MAPI_DATE 15#define MAPI_TIME 16#define MAPI_DATETIME 17#define MAPI_NUMERIC 18 Is it possible to get MonetDB to generate UUIDs and how is the type supported in mapi. Thanks Doug Service

4 8

R aggregation fails when input is exactly 200K tuples
by Roberto Cornacchia 01 Oct '15

01 Oct '15

Hi, I have a suspect failure at exactly 200K tuples in input. I declared a simple aggregate on two columns (here it doesn't aggregate, for simplicity, it just returns 42). START TRANSACTION; CREATE table test (customer int, d string, n int); INSERT INTO test VALUES(1,'2015-01-01', 100); INSERT INTO test VALUES(1, '2015-01-02', 100); INSERT INTO test VALUES(2, '2015-01-03', 100); INSERT INTO test VALUES(2, '2015-01-01', 100); INSERT INTO test VALUES(2, '2015-01-02', 100); CREATE AGGREGATE sow(d string, n int) RETURNS DOUBLE LANGUAGE R { # aggregation function is constant, not important here sow_aggr <- function(df) { 42.0 } df <- cbind(d,n) as.vector(by(df, aggr_group, sow_aggr)) }; select customer, sow(d,n) from test group by customer; ROLLBACK; +----------+--------------------------+ | customer | L1 | +==========+==========================+ | 1 | 42 | | 2 | 42 | +----------+--------------------------+ The result is what I had expected. That is true until table test is long 199999 tuples. When it's exactly 200000 tuples, I get: Error running R expression. Error message: Error in tapply(seq_len(200000L), list(INDICES = c(0, 1, 2, 3, 4, 5, 6, : arguments must have same length Calls: as.data.frame ... by.data.frame -> structure -> eval -> eval -> tapply I checked the vector aggr_group, and indeed it is not 200000 long, as it should be. Instead, it is just one longer than then number of distinct values for customer (the grouping column). Any thought? Roberto

2 3

COPY INTO string attribute containing single quote, succeed in Jan2014-SP2, fail in Jul2015
by Constantine Feng 30 Sep '15

30 Sep '15

Dear all, I am trying to bulk load the TPC-DS database benchmark into MonetDB. One of the data file "customer.csv" contains data in this format: 27|AAAAAAAALBAAAAAA|111621|835|16096|2450611|2450581|Dr.|Shawn|Prather|N|7|3|1972|PUERTO RICO||Shawn.Prather(a)8BusRYegn6.org|2452353 28|AAAAAAAAMBAAAAAA|889961|897|183480|2452615|2452585|Miss|Edith|Hernandez|Y|17|8|1959|CÔTE D'IVOIRE||Edith.Hernandez(a)BNHL0k.com|2452631 Note in the second row, there is a string attribute containing a single quote. And I use the below command to load it: COPY INTO customer from 'path/to/customer.csv' USING DELIMITERS '|','\n' NULL AS ''; I have two machines with two installations: one installed last year via Ubuntu apt-get, "monetdb version" shows "MonetDB Database Server Toolkit v1.1 (Jan2014-SP2)" the other recently follows the instructions here: https://www.monetdb.org/downloads/deb/ (Ubuntu), "MonetDB Database Server Toolkit v1.1 (Jul2015)" Somehow the loading succeeded in the former but failed in the latter. Is it due to some version difference? How can I fix it? Error: field 15 expecting varchar(15) 'CÔTE D'IVOIRE' Thanks in advance. - Neo

2 1

Strange characters with ODBC driver
by Dennis Pallett 28 Sep '15

28 Sep '15

Hi all, We're trying to use MonetDB together with SAP Business Objects using the ODBC driver but we're getting some strange results when using the table browser. The names of the schema's seem to be all mangled up (see attached screenshot). Any ideas what might be wrong? It works fine with the JDBC driver btw. Any advantages/disadvantages with both drivers? Best regards, Dennis Pallett

2 2

Version vs release naming schemes
by Eyal Rozenberg 28 Sep '15

28 Sep '15

What's the relation between the Month+Year release naming scheme (e.g. Jul2015, Oct2014-SP2) and the numeric versions (e.g. 11.19.9)? The website has a release history page: https://www.monetdb.org/Documentation/UserGuide/ReleaseNotes But I haven't found a page discussing the numeric version numbers. I'm not even sure the X.Y.Z format means version.subversion.subsubversion ... Eyal

2 1

Jul2015 not availbale for debian Wheezy
by imad hajj chahine 28 Sep '15

28 Sep '15

Hi, I am trying to upgrade my installation on debian from 11.19 to 11.21, i cant find the Jul2015 in the https://www.monetdb.org/downloads/deb/dists/wheezy/monetdb/binary-amd64/ and the apt-get is pointing to the 11.19.15 version, any idea how to get the latest release. Thank you

2 1

Issue importing certain character combinations
by andy.barlow＠datum360.com 25 Sep '15

25 Sep '15

I filed a bug earlier, the gist of which is below but perhaps there is some setting or flag I am missing when importing? If your import file contains a number of character combinations, such as \560, \561, \700 and probably lots of others, the copy into fails with a message like: Failed to import table line 5 field 1 'varchar(20)' expected in 'FIVE\700' Reproducible: Always Steps to Reproduce: 1. create table test (test varchar(20)); 2. create a load file test.txt with the following: ONE TWO THREE FOUR FIVE\700 3. copy into test from 'test.txt' using delimiters '\t','\n'; 4. fails with message: Failed to import table line 5 field 1 'varchar(20)' expected in 'FIVE\700' Actual Results: Failed to import table line 5 field 1 'varchar(20)' expected in 'FIVE\700' Expected Results: should import OK, the character combinations are valid Andy Barlow Datum360

1 1

Re: Disk space running out
by scArcer 24 Sep '15

24 Sep '15

"create table" and "insert data" are separate. And the disk is consumed by queries. because when I finished data loading, it was, only 3~4G. Thank you for the link. I will try restart server to see if monetdb can unlink these files too. I execute queries in auto commit mode, should I control transactions myself? ------------------ Original ------------------ From: "Guillaume Savary";<guillaume.savary(a)securactive.net>; Date: Thu, Sep 24, 2015 02:30 PM To: "Communication channel for MonetDB users"<users-list(a)monetdb.org>; Cc: "scArcer"<53308768(a)qq.com>; Subject: Re: Disk space running out Hi, Are you repeating this "create table + insert data" multiple times on this disk? Because there is a bug on the BAT files which are never freed : https://www.monetdb.org/bugzilla/show_bug.cgi?id=3802 Work around : try to do the "create table" in an other transaction than the "insert data". Le 24/09/2015 08:17, scArcer a écrit : Here is my situation. I create a table A, load about 3~4G data into it. Then a lot of queries are executed, making it using disk up to 1T. [hadoop@falco001-bi-30-141 data3]$ du -sh monetdb/ 1.1T monetdb/ and when I try to execute new query, I got this error: sql>select * from ods.attr_user_c_profile limit 10; could not allocate space for GDKsave: error -1 on: name=06/62/66261, ext=theap, mode=0 !OS: No space left on device HEAPextend: failed to extend to 983040 for 06/62/66261.theap: h->storage == STORE_MEM && can_map && fd >= 0 && HEAPsave_intern() < 0 ------------------ Original ------------------ From: "Martin Kersten";<martin.kersten(a)cwi.nl>; Date: Thu, Sep 24, 2015 02:03 PM To: "users-list"<users-list(a)monetdb.org>; Subject: Re: Disk space running out On 24/09/15 06:04, scArcer wrote: > Hi, I'm facing the problem of disk space running out. > Since monetdb will store the query intermediates into the disk when the memory is not enough to hold them all, I'm wondering that does monetdb will automatically free the disk space? > And if it doesn't, how can I do it manually? > > Hi It will release disk space as soon as possible. > _______________________________________________ > 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 -- Guillaume Savary Securactive R&D

1 0

Disk space running out
by scArcer 24 Sep '15

24 Sep '15

Hi, I'm facing the problem of disk space running out.Since monetdb will store the query intermediates into the disk when the memory is not enough to hold them all, I'm wondering that does monetdb will automatically free the disk space? And if it doesn't, how can I do it manually?

3 4