- users-list - monetdb.org

handling of (indirect) division by zero: nulls or an error?
by g4＠novadsp.com 27 Nov '14

27 Nov '14

I posted earlier about a problem with 'type mismatch' being reported. The root cause is quite simple: it is returned if the stddev_pop() function is used in a select that returns zero rows. Given that the count of the population (N) is used as the denominator in the calculation this makes sense. A question arising then is this. If one has assembled a more complex list of aggregate functions in a query, would it be better for null to be returned instead of an error? i.e. select count(weight), sum(weight), average(weight) where (no rows are selected) => 0,null,null Should this not also be the case if stddev_pop is included? select count(weight), sum(weight), average(weight), stddev_pop(weight) where (no rows are selected) => 0,null,null,null Instead of 'type mismatch' ? TIA. J.

1 0

Announcement: New Oct2014-SP1 Bugfix release of MonetDB suite
by Sjoerd Mullender 27 Nov '14

27 Nov '14

The MonetDB team at CWI/MonetDB BV is pleased to announce the Oct2014-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/>. Oct 2014-SP1 bugfix release MonetDB5 Server * Fixed adding of 0 intervals to dates. Build Environment * The libraries included in the Windows installers have been upgraded. We now use libxml2-2.9.2, openssl-1.0.1j, pcre-8.36, and zlib-1.2.8. MonetDB Common * Implemented a change to the way in which string bats are appended. We now try harder to limit the growth of the string heap. * Fixed some problems with BATsample. It was possible for BATsample to return a value that was just beyond the end of the sampled BAT. Also, on some systems the range of the rand() function is rather limited (0..32767) and trying to get a sample larger than this range would result in an infinite loop. SQL * Fixed sys.queue() implementation to report on other queries being executed. * A number of bugs were fixed in the code to upgrade a database from previous releases. This version should fix the upgrade of a database that had been upgraded to the Oct2014 release, but also properly upgrade directly from Jan2014 and Feb2013 releases. Bug Fixes * 3580: cosmetic change (append newline) * 3609: Incorrect use of generate_series * 3611: quantile() and median() commands crash when used 2x on the same variable on a null table * 3612: assertion failure when deleting rows from table to which a FK constraint is defined * 3620: ORDER BY broken when using UNION ALL * 3621: Hexadecimal literal vs decimal literal

1 0

'type mismatch' error
by g4＠novadsp.com 26 Nov '14

26 Nov '14

Using both mclient and C#/C++ test clients connected via 64bit ODBC I am encountering this type mismatch error for no obvious reason. This is something of a show-stopper for what had proved to be a very interesting experiment! # MonetDB 5 server v11.19.3 "Oct2014" # Serving database 'dbfarm', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 15.982 GiB available main-memory. An example query below, weight is a double, all other columns are integer. There are no NULL values, nor any oddities sql>SELECT count(weight) , sum(weight) , avg(weight) , stddev_pop(weight) FROM test_1 WHERE ((severity = 1 OR severity = 2) AND (iat = 2) AND (ilit = 3)); Type mismatch But a family of closely related queries do succeed: sql>SELECT count(weight) , sum(weight) , avg(weight) , stddev_pop(weight) FROM test_1 WHERE ((severity = 1 OR severity = 2) AND (iat = 2) AND (ilit = 11)); | L1 | L2 | L3 |> | 290517 | 290517 | 1 | 1 tuple (200.115ms) !1 column dropped! Enabling ODBC logging does not reveal a great deal more than this: dbc-con.vshost 13d8-2ac4 ENTER SQLExecDirectW HSTMT 0x000000000048F370 WCHAR * 0x000000000256458C [ -3] "SELECT count(weight) as cnt, sum(weight) as total, avg(weight) as average, stddev_pop(weight) as stddev FROM test_1 WHERE ((severity = 1 OR severity = 2) AND (iat = 2) AND (ilit = 3))\ 0" SDWORD -3 odbc-con.vshost 13d8-2ac4 EXIT SQLExecDirectW with return code -1 (SQL_ERROR) HSTMT 0x000000000048F370 WCHAR * 0x000000000256458C [ -3] "SELECT count(weight) as cnt, sum(weight) as total, avg(weight) as average, stddev_pop(weight) as stddev FROM test_1 WHERE ((severity = 1 OR severity = 2) AND (iat = 2) AND (ilit = 3))\ 0" SDWORD -3 DIAG [HY000] [MonetDB][ODBC Driver 11.19.3]Type mismatch (0) odbc-con.vshost 13d8-2ac4 ENTER SQLGetDiagRecW SQLSMALLINT 3 <SQL_HANDLE_STMT> SQLHANDLE 0x000000000048F370 SQLSMALLINT 1 SQLWCHAR * 0x000000001C39D940 SQLINTEGER * 0x000000001C39DB34 SQLWCHAR * 0x000000001B6246C0 SQLSMALLINT 1024 SQLSMALLINT * 0x000000001C39DB30 odbc-con.vshost 13d8-2ac4 EXIT SQLGetDiagRecW with return code 0 (SQL_SUCCESS) SQLSMALLINT 3 <SQL_HANDLE_STMT> SQLHANDLE 0x000000000048F370 SQLSMALLINT 1 SQLWCHAR * 0x000000001C39D940 [ 5] "HY000" SQLINTEGER * 0x000000001C39DB34 (0) SQLWCHAR * 0x000000001B6246C0 [ 43] "[MonetDB][ODBC Driver 11.19.3]Type mismatch" SQLSMALLINT 1024 SQLSMALLINT * 0x000000001C39DB30 (43)

1 0

Loading RDF data
by Rinke Hoekstra 26 Nov '14

26 Nov '14

Hi All, After some fiddling, I managed to compile and install the latest version of MonetDB with RDF support enabled (turns out it requires an old version of Raptor... 1.4). My next step would be to load some actual RDF data... but I cannot find any mention of how to do this on the MonetDB website, nor in the mailinglist archives. Pointers very welcome. Thanks, Rinke ---- Rinke Hoekstra VU University Amsterdam/University of Amsterdam

1 0

corrupt catalog Oct 2014
by Pierre-Adrien Coustillas 26 Nov '14

26 Nov '14

Hello, we have again duplicated table on version Oct2014. pcoustillas@ monetdb :~$ mclient lemonde Welcome to mclient , the MonetDB / SQL interactive terminal (Oct2014) Database : MonetDB v11.19.3 (Oct2014), 'mapi : monetdb :// monetdb :50000/ lemonde' Type \q to quit , \? for a list of available commands auto commit mode: on sql >\d src __ at __ lm __visites_ agregees table sys . src __ at __ lm __visites_ agregees is not unique, corrupt catalog ? sql > echo " SELECT name FROM SYS .TABLES WHERE system = false ;" | mclient lemonde | grep " " | grep -v " name " | grep -v " tuple " | sed 's /[| ]// g' | sort | uniq -c | grep -v " 1 " 2 src __ at __ lm __visites_ agregees 2 src __ at __ tra __visites_ agregees 2 tmp _ lm _ suppr _id 4 tmp _ lm _ suppr _id_unique 2 tmp _ tra _ suppr _id 4 tmp _ tra _ suppr _id_unique we do a lot of drop and create table . Is the trouble can come of it ? it must take a sleep after a drop ? Pierre -- 1G6 52 route de bischwiller 67300 Schiltigheim Société de Services et de Formations en Logiciels Libres http://1g6.biz Tél : 06 64 63 70 35

2 1

support for window aggregates and window frames ...
by Jay Prasad 24 Nov '14

24 Nov '14

Does MonetDB support window aggregates and window frames? >From the documentation it looks like it supports only window rank functions. Queries of the form: SELECT saleyear, salequarter, sum(saleamount), sum(saleamount) OVER ( PARTITION BY saleyear ORDER BY salequarter ASC) AS partitionedsaleamount FROM ds_1108_null_advent_1580276965 group by saleyear, salequarter; fail with this error: SELECT: function 'sum' not found However, the code seems to include branches that process window aggregates. See sql_parser.y (line 3530) and rel_select.c (line 4658). I am also seeing a similar issue with window frames: SELECT saleyear, salequarter, EXTRACT (MONTH FROM orderdate) AS monthofsale, SUM(saleamount) AS totalsales, RANK() OVER ( PARTITION BY monthofsale ORDER BY SUM(saleamount) ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS rank FROM ds_1108_null_advent_1580276965 GROUP BY saleyear,salequarter,monthofsale ORDER BY saleyear ASC LIMIT 20; TypeException:user.s16_1[60]:'algebra.subsort' undefined in: (_103:any, r1_103:any, r2_103:any) := algebra.subsort(_92:int, r1_100:bat[:oid,:oid], r2_100:bat[:o id,:oid], _83:bit, _99:bit) program contains errors NOTICE: This e-mail message and any included attachments are from Infogix, Inc. ("Infogix") and are intended solely for use by the individual(s) to whom the message was addressed. The information contained herein may include privileged or otherwise confidential information. Unauthorized review, forwarding, printing, copying, distributing, or using the information contained in this message is strictly prohibited. If you have received this message in error, or have reason to believe that you are not authorized to receive it, please promptly notify the sender by e-mail, delete the message from your computer, and do not copy or disclose the information to anyone else. If you properly received this e-mail as an addressee, please maintain its contents in confidence to protect confidentiality. Thank you.

1 0

How to use JSON in MonetDB
by Art Apl 24 Nov '14

24 Nov '14

Hello! MonetDB documentation says, that JSON is supported in MonetDB, but I can`t find any example. When I try to implement this query in MonetDB client: json.filter({ "store": { "bicycle": { "color": "red", "price": 19.95 }}},'$') I get this error: syntax error, unexpected IDENT in: "json" THX

2 4

JDBC driver: Statement#setMaxRows()
by Robert Koch 24 Nov '14

24 Nov '14

I have big tables with billions of rows. If I click accidentally on DBVisualizer's data tab, or, if the data tab of a small table is opened and I click on a big table, DBVisualizer tries to retrieve all rows. Then I have to kill DBVisualizer process and/or the DB server process. Answer of DBVisualizer's team: "Hi Robert, It seems like the JDBC driver does not implement the Statement#setMaxRows() method. Please contact your driver developer about this problem." Is the Statement#setMaxRows() method implemented?

2 2

Resource on the PHP API for monetdb
by Leon-Constantin Fopa 24 Nov '14

24 Nov '14

Hi every one, I'm interested in accessing monetdb from a PHP script. Can someone provide me a link on resource with introduce that. The research of the keyword "monetdb" from the the http://php.net website search engine give no related result. thanks in advance, Best regards, Fopa Léon Constantin

3 3

Whicht GUIs/Frontends are you using? / MonetDB support in DBVisualizer
by Robert Koch 24 Nov '14

24 Nov '14

Which tools are you using? As mentioned, I am using DBVisualizer for work with MonetDB. I asked for a better support of MonetDB in DBVisualizer: "MonetDB is the leading open-source, column-oriented, analytical in-memory database. MonetDB's community is growing and MonetDB does not come along with a frontend. It would be great to have a native support out of DBVis." This is the answer: "Hi Robert, We have an open ticket for addressing support for MonetDB in DbVisualizer. Currently the number of requests for it is low so I cannot say when or if it will happen. Most of the database specific support is expressed in a so called database profile in DbVisualizer. This is a single XML file which defines up to 80% of the functionality for a database. The database profile framework is documented in the users guide and if you are looking for improved MonetDB support I suggest you look into building a database profile for it. http://confluence.dbvis.com/display/UG91/Database+Profiles If you have any related questions please don't hesitate to contact us." I somebody out there, who is interested in MonetDB support in DBVisualizer, willing to build a database profile XML file?

1 0