- users-list - monetdb.org

Using something like "trace" within the MAL interpreter
by Masood Mortazavi 20 May '13

20 May '13

Hi, The "trace" commands works well when mclient is in the SQL interpreter mode. How about when mclient is in MAl interpreter mode? Is there a way to use the "trace" command in this mode? Best regards, Masood

3 6

support for non-median quantile functions?
by Anthony Damico 15 May '13

15 May '13

Hi, would it be possible to expand the median() function to be expanded work on any quantile? Martin's original request did say quantile, not just median :D http://osdir.com/ml/db.monetdb.bugs/2008-06/msg00206.html Given the MonetDB.R connection, I think a lot more statisticians are going to be using MonetDB -- and they'll find a lot of value in being able to calculate statistics like the 25th and 75th, percentiles quickly. I can submit a bugs.monetdb.org report if that's appropriate? Thanks for considering this!!

2 2

Re: Help for MonetDB source code (SQL Module)
by Stefan Manegold 14 May '13

14 May '13

Dear Khaled, for questions about MonetDB, I kindly ask you to use the respective mailing list(s); I Cc my answer to the users list. See also http://www.monetdb.org/Developers/Mailinglists On Mon, May 13, 2013 at 06:41:45PM +0200, Khaled Dehdouh wrote: > Dear sir, > > I am a student at university of LYON. Concerning MonetDB, I need your help, > please on some points. > > > - I want to do some optimizations in "GROUP BY" function of SQL module. > For this end, can you show me where is exactly the function code > responsible to perform the "Group By" operation on MonetDB source > codein order to modif > y it. I'm wondering how you know that "optimizations" are necessary/possible without knowing the actual code, but I guess you know. You find the respective code in gdk/gdk_group.c > - I would like to know also how to recompile the code after modification > of the "GROUP BY" function. See http://www.monetdb.org/Developers/SourceCompile http://dev.monetdb.org/hg/MonetDB/file/tip/HowToStart.rst http://dev.monetdb.org/hg/MonetDB/file/1272d67f24be/buildtools/doc/windowsb… > - How creating a new SQL command that performs function with parameters and > combines two existing functions in MONETDB. See http://www.monetdb.org/Documentation/SQLreference/Modules or your favorite SQL:2003 documentation. MonetDB is SQL standard compliant. Best, Stefan > Thanks in advance. > > Best regards, > Khaled DEHDOUH. -- | 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) |

1 0

GROUP BY
by Bouslah Ahmed 14 May '13

14 May '13

Hello, I am a student at a university in France. I work on MonetDB, Windows platform. I have some questions: I want to optimize the "Group by" function of the SQL module in order to create a new function combining the "Group by" function and another function. For this I want to know: 1 - How to locate the class containing the code of the "Group by" function 2 - How to edit the code in question to change it 3 - Which editor should be used to compile the project. 4 - I tried to use "Visual Studio 2012" to open the project "MonetDB" but I have not managed to do. Regards Ahmed Bouslah.

3 2

Question on installing extensions
by Kaniska_Mandal＠DELL.com 12 May '13

12 May '13

Hi All, How to install extensions for Streams, XML and Json support ? Many thanks, Kaniska

2 1

TINYINT display problems in 3rd party query clients
by Diethard Steiner 10 May '13

10 May '13

Hi, I am wondering why TINYINT values are displayed with different values in 3rd party SQL clients like Squirrel (please see attached screenshot): So 1 is shown as 49 in example. I used following JDBC driver version: monetdb-jdbc-2.8.jar Any ideas? Thanks, Diethard

3 5

Feb2013-SP2 mserver loading time much longer than Feb2013 on Windows x86_64
by Anthony Damico 03 May '13

03 May '13

Hi, after upgrading to MonetDB Feb2013-SP2, I've noticed a huge increase in the loading time for the server to get the ">" shown in the last line below (indicating it's loaded and ready for mclient connections). It now takes between three and ten minutes. Has anything changed in the Windows version of the server between Feb2013 and Feb2013-SP2 that might cause this? Once loaded, the queries are still lightning-fast, but I'm debating whether I ought to re-create all of my databases from scratch using Feb2013-SP2, if there's any reason doing that might help? Any clues? :) Thanks!!! # MonetDB 5 server v11.15.7 "Feb2013-SP2" # Serving database 'medicare_sample', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 20.000 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:49800/ # MonetDB/JAQL module loaded >

1 0

question about arrays in SciQL
by Gabor Urbanics 03 May '13

03 May '13

Hi All, I have a question about MonetDB/SciQL, in particular regards to the unbounded arrays and their updates. I've created two arrays (one bounded and an unbounded) essentially with the same schemata in my database as follows: """ sql>\d ARRAY sys.facts ARRAY sys.facts_bounded sql>\d sys.source CREATE TABLE "sys"."source" ( "x" INTEGER, "y" INTEGER, "v" VARCHAR(32) ); sql>\d sys.facts CREATE ARRAY "sys"."facts" ( "x" INTEGER DIMENSION [0:1:1], "y" INTEGER DIMENSION [0:1:*], "v" VARCHAR(32) ); """ My goal at the moment is to use the unbounded array as it would fit very well the domain of my application: several coordinates-like columns and a couple of attributes, but the ranges of the coordinates are not known beforehand. Unfortunately, I am facing some problems when I try to insert values into the unbounded array: """ sql>insert into sys.facts values (0,0,'42'); 0 affected rows (0.938ms) """ On the other hand, the same insert operation works just fine with the bounded array: """ sql>insert into sys.facts_bounded values (0,0,'42'); 1 affected row (20.594ms) sql>select * from sys.facts_bounded; +------+------+------+ | x | y | v | +======+======+======+ | 0 | 0 | 42 | | 0 | 1 | null | ... (5 lines omitted for brevity) | 1 | 3 | null | +------+------+------+ 8 tuples (0.612ms) """ I would like to know if this is the supposed way one can insert values into unbounded arrays or I am doing something wrong. I compiled the source from the branch "sciql" from the mercurial repository 2 days ago, as my understanding was that the built versions do not support SciQL. Is there any other way I can get a MonetDB with SciQL support? If it helps anything, the beginning of the welcome message of the mserver5 is as follows: """ # MonetDB 5 server v11.16.0 # This is an unreleased version # Serving database 'monetdb5', using 4 threads # Compiled for x86_64-unknown-linux-gnu/64bit with 64bit OIDs dynamically linked """ Any help or suggestion is appreciated! Best regards, Gabor

2 3

column alias combined with ORDER BY returns wrong data
by Budulinku Dejmihrasku 03 May '13

03 May '13

Hi, please note the test cases below - it should be self-explanatory i am on MonetDB v11.15.7 (Feb2013-SP2), i run the test before the upgrade with the same results please advice thanks a lot milan *test case 1, no ORDER BY - correct** **----------------------------------* WITH SAWITH0 AS ( select 'a' as c1, 'b' as c2, 'c' as c3, 1 as c4 ) select 0 as c1, D1.c1 as c2, D1.c2 as c3, D1.c3 as c4, D1.c4 as c5 from SAWITH0 D1 +------+------+------+------+------+ | c1 | c2 | c3 | c4 | c5 | +======+======+======+======+======+ | 0 | a | b | c | 1 | +------+------+------+------+------+ *test case 2, add ORDER BY - wrong result (repeats the 1st value for all columns)* *--------------------------------------------------------------------------------* WITH SAWITH0 AS ( select 'a' as c1, 'b' as c2, 'c' as c3, 1 as c4 ) select 0 as c1, D1.c1 as c2, D1.c2 as c3, D1.c3 as c4, D1.c4 as c5 from SAWITH0 D1 order by 5, 4, 3, 2 --order by c5,c4,c3,c2 +------+------+------+------+------+ | c1 | c2 | c3 | c4 | c5 | +======+======+======+======+======+ | 0 | a | a | a | a | +------+------+------+------+------+ Note that the result is the same even if you use the column alias instead of the ord.num. * **test case 3, use completely different alias with order by - correct ** **-------------------------------------------------------------------* WITH SAWITH0 AS ( select 'a' as c1, 'b' as c2, 'c' as c3, 1 as c4 ) select 0 as c1, D1.c1 as a2, D1.c2 as a3, D1.c3 as a4, D1.c4 as a5 from SAWITH0 D1 order by 5, 4, 3, 2 +------+------+------+------+------+ | c1 | a2 | a3 | a4 | a5 | +======+======+======+======+======+ | 0 | a | b | c | 1 | +------+------+------+------+------+

4 6

Re: should I be able to CAST a BOOLEAN type to a DOUBLE ( zero-one ) variable?
by Stefan Manegold 02 May '13

02 May '13

cast syntax is "cast( <expression> as type )" Anthony Damico <ajdamico(a)gmail.com> wrote: I tried lots of different things (pasted below) and none of them worked. I ended up using a CASE statement which worked around the issue, but I would've thought something like the command.. SELECT ( ( COLNAME > 0 )*1.000 ) AS ANY_VALUE FROM TABLENAME ; ..should give a valid response instead of an error? But I'm not sure if MonetDB wants to support this type of CAST/CONVERT operation? (None of the things I tried worked, not just the on-the-fly multiplication by 1.000). Using Feb2013-SP2 on Windows x86_64 If MonetDB should be able to convert TRUE/FALSE values to numerics, I'll submit a bug report with some PLAN and EXPLAIN results? :) Thanks for the great software!! sql>select ( mpg > 10 ) from mtcars ; +-------+ | >_mpg | +=======+ | null | | null | | true | | null | | true | | null | | true | | true | | true | | null | | null | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | null | | true | | true | +-------+ 32 tuples (15.010ms) sql>select ( mpg > 10 )*1.000 from mtcars ; TypeException:user.s11_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s11_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg > 10 )*1.000 ) from mtcars ; TypeException:user.s12_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s12_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg >= 10 )*1.000 ) from mtcars ; TypeException:user.s13_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s13_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg >= 10 ) ) from mtcars ; +--------+ | >=_mpg | +========+ | null | | null | | true | | null | | true | | null | | true | | true | | true | | null | | null | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | null | | true | | true | +--------+ 32 tuples (103.019ms) sql>select CAST( ( mpg >= 10 ) ) as DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( ( mpg >= 10 ) )" sql>select CAST( ( mpg >= 10 ) ) AS DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( ( mpg >= 10 ) )" sql>select CAST( mpg >= 10 ) AS DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( mpg >= 10 )" sql>select ( CAST( mpg >= 10 ) AS DOUBLE ) from mtcars ; syntax error, unexpected ')', expecting AS in: "select ( cast( mpg >= 10 )" sql>select ( CAST( mpg >= 10 AS DOUBLE ) from mtcars ; syntax error, unexpected FROM, expecting ')' in: "select ( cast( mpg >= 10 as do uble ) from" sql>select ( CAST( mpg >= 10 AS DOUBLE ) as x from mtcars ; syntax error, unexpected AS, expecting ')' in: "select ( cast( mpg >= 10 as doub le ) as" sql>select ( CAST( mpg >= 10 ) AS DOUBLE as x from mtcars ; syntax error, unexpected ')', expecting AS in: "select ( cast( mpg >= 10 )" sql>select CONVERT( double , mpg > 10 ) as x from mtcars; syntax error, unexpected sqlDOUBLE in: "select convert( double" sql>select CAST( TRUE as double ) from mtcars ; types boolean(1,0) and double(53,0) are not equal sql>select CAST( TRUE*1.000 as double ) from mtcars ; TypeException:user.s15_2[11]:'calc.sht' undefined in: _21:any := calc.sht(_20:bi t, _22:int, _23:int) program contains errors sql>select CAST( 'TRUE' as bit ) as z from mtcars; type (bit) unknown in: "select cast( 'TRUE' as bit )" syntax error, unexpected AS in: "as" sql>select CAST( TRUE as bit ) as z from mtcars; type (bit) unknown in: "select cast( true as bit )" syntax error, unexpected AS in: "as" sql>select CAST( TRUE as double ) as z from mtcars; types boolean(1,0) and double(53,0) are not equal sql>

3 3

should I be able to CAST a BOOLEAN type to a DOUBLE ( zero-one ) variable?
by Anthony Damico 02 May '13

02 May '13

I tried lots of different things (pasted below) and none of them worked. I ended up using a CASE statement which worked around the issue, but I would've thought something like the command.. SELECT ( ( COLNAME > 0 )*1.000 ) AS ANY_VALUE FROM TABLENAME ; ..should give a valid response instead of an error? But I'm not sure if MonetDB wants to support this type of CAST/CONVERT operation? (None of the things I tried worked, not just the on-the-fly multiplication by 1.000). Using Feb2013-SP2 on Windows x86_64 If MonetDB should be able to convert TRUE/FALSE values to numerics, I'll submit a bug report with some PLAN and EXPLAIN results? :) Thanks for the great software!! sql>select ( mpg > 10 ) from mtcars ; +-------+ | >_mpg | +=======+ | null | | null | | true | | null | | true | | null | | true | | true | | true | | null | | null | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | null | | true | | true | +-------+ 32 tuples (15.010ms) sql>select ( mpg > 10 )*1.000 from mtcars ; TypeException:user.s11_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s11_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg > 10 )*1.000 ) from mtcars ; TypeException:user.s12_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s12_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg >= 10 )*1.000 ) from mtcars ; TypeException:user.s13_2[15]:'calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s13_2[17]:'bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg >= 10 ) ) from mtcars ; +--------+ | >=_mpg | +========+ | null | | null | | true | | null | | true | | null | | true | | true | | true | | null | | null | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | null | | true | | true | +--------+ 32 tuples (103.019ms) sql>select CAST( ( mpg >= 10 ) ) as DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( ( mpg >= 10 ) )" sql>select CAST( ( mpg >= 10 ) ) AS DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( ( mpg >= 10 ) )" sql>select CAST( mpg >= 10 ) AS DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( mpg >= 10 )" sql>select ( CAST( mpg >= 10 ) AS DOUBLE ) from mtcars ; syntax error, unexpected ')', expecting AS in: "select ( cast( mpg >= 10 )" sql>select ( CAST( mpg >= 10 AS DOUBLE ) from mtcars ; syntax error, unexpected FROM, expecting ')' in: "select ( cast( mpg >= 10 as do uble ) from" sql>select ( CAST( mpg >= 10 AS DOUBLE ) as x from mtcars ; syntax error, unexpected AS, expecting ')' in: "select ( cast( mpg >= 10 as doub le ) as" sql>select ( CAST( mpg >= 10 ) AS DOUBLE as x from mtcars ; syntax error, unexpected ')', expecting AS in: "select ( cast( mpg >= 10 )" sql>select CONVERT( double , mpg > 10 ) as x from mtcars; syntax error, unexpected sqlDOUBLE in: "select convert( double" sql>select CAST( TRUE as double ) from mtcars ; types boolean(1,0) and double(53,0) are not equal sql>select CAST( TRUE*1.000 as double ) from mtcars ; TypeException:user.s15_2[11]:'calc.sht' undefined in: _21:any := calc.sht(_20:bi t, _22:int, _23:int) program contains errors sql>select CAST( 'TRUE' as bit ) as z from mtcars; type (bit) unknown in: "select cast( 'TRUE' as bit )" syntax error, unexpected AS in: "as" sql>select CAST( TRUE as bit ) as z from mtcars; type (bit) unknown in: "select cast( true as bit )" syntax error, unexpected AS in: "as" sql>select CAST( TRUE as double ) as z from mtcars; types boolean(1,0) and double(53,0) are not equal sql>

1 0

Understanding strides in memory accesses
by Hassan, Ahmad 01 May '13

01 May '13

Hi All, I am analysing the monetdb memory access pattern to identify various strides in LLC accesses. But most of the accesses per object are being observed as non-strided which is not what I expected. As monetdb involves BATs and these are arrays so I was thinking that there would be more strided pattern rather than random accesses unless BAT arrays are being treated as trees and random access is being used to access the items. To understand it further, I have now retrieved the execution plan of TPCH Q1 and the plan has various segments like follows: X_455:bat[:oid,:str] := sql.bind(X_7,"sys","lineitem","l_linestatus",2,5,16); X_1346 := algebra.kdifference(X_400,X_455); X_1379 := algebra.kunion(X_1346,X_455); X_1412 := algebra.leftjoin(X_1180,X_1379); (X_1452,X_1453) := group.multicolumns(X_1293,X_1412); X_1514 := bat.mirror(X_1452); X_1516 := algebra.leftjoin(X_1514,X_1412); I have two questions: 1) As there are many repeated segments like above in whole query plan, so what is the context behind multiple repeated segments please. 2) I want to understand what is the input to most common operators like leftjoin, kdifference, kunion and whether they process those inputs sequentially or randomly. What is the best way to get this understanding please? From the plan it is obvious that the input is the BAT(s), but how do the operators access the BAT is really a question. Thanks for the help. Kind Regards, Ahmad

3 3

Fwd: How to get deleted space back from monetdb
by Gaurav Meena 01 May '13

01 May '13

I have searched entire archive, and this question have been asked many times i.e. to get the space back from monetdb after deleting some rows in monetdb. The answer I found was not consistent one of them was COPY SELECT * FROM <table> INTO <file>; DELETE FROM <table>; COPY INTO <table> FROM <file>; Which suggest that deleting rows (or cleaning??) table will get back the space. One was from this question http://mail.monetdb.org/pipermail/users-list/2011-July/005036.html 1) copy all table contents to another table, drop old table, copy again I have tried both deleting rows and then droping the table but the result was inconsistant. May be monetdb cleans up after some interval Other Related Questions were 1. http://mail.monetdb.org/pipermail/users-list/2010-March/003952.html 2. FEB 23, 2010 Reeves, Matthew J. wrote: >* Hello,*>* *>* I have a MonetDB database that contains a table of rolling events.*>* There are 200 million records with 500,000 bulk loaded each day with the*>* oldest 500,000 deleted at midnight. I'm finding the database gets slower*>* as time goes on. It gets to the point of being almost unusable after a*>* week. Is there something I should be doing different?*Hello Matthew, Thanks for using MonetDB. Interesting application target. This is a known issue. The system keeps the deleted tuples and rebuilds the table. A vacuum-like feature is needed and on the wishlist. For now, taking a copy of the table would 'solve' your problem. regards, Martin >* *>* Thanks,*>* Matt*>* * Gaurav Meena

2 1

Inner join.
by Bouslah Ahmed 30 Apr '13

30 Apr '13

Hello, I'm working on MonetDB under win7, and I encountered some problems that hinder me 1 - I need to make an amendment on the inner join; 2 - To do this, I ask you to explain how the inner join works; 3 - In what part of the program (source code) is implemented the inner join. Cordially.

2 1

sql.clients function removed
by Pete Hollobon 29 Apr '13

29 Apr '13

Hi, It looks like the sql.clients() function was removed (changeset 45a4e2205363). I was wondering why, and whether there is a suitable alternative? Regards, Pete.

1 1

Announcement: New Feb2013-SP2 Bugfix release of MonetDB suite
by Sjoerd Mullender 29 Apr '13

29 Apr '13

The MonetDB team at CWI/MonetDB BV is pleased to announce the Feb2013-SP2 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/>.

1 0

All tested on the same column, some functions work, some don't?
by Anthony Damico 24 Apr '13

24 Apr '13

Hi, I'm using MonetDB on Windows with the MonetDB.R connector. I'm getting some very strange errors on a particular column (called `rx_cso_num`) where *some* of the math functions work and others do not? I tested the median() function using mclient - I get the same error - so this has nothing to do with the fact that I'm connecting through R. I'm running these queries on a confidential data set, so unfortunately I cannot provide a reproducible example with this.. I'm sorry for the hassle, and hoping the error messages below might provide enough of a clue to resolve the issue? If this really isn't enough, I can send some snipped mclient -X output and/or try my best to create some fake data that also creates some of these problems? Thanks!! :) Here's a list of functions that *work* on the column without a problem: "count","distinct","min", "max", "sum","avg","abs","sqrt","floor","ceiling","exp","log","cos","sin","tan","acos","asin","atan","cosh","sinh","tanh" Here's the errors I get for each of the functions that do not: [1] "median" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select median(rx_cso_num) from x07'. Server says '!22003!overflow in conversion of 133 to bte.'. [1] "sign" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select sign(rx_cso_num) from x07'. Server says '!TypeException:user.s5_1[25]:'bat.insert' undefined in: _93:any := bat.insert(_83:bat[:oid,:int], _91:oid, _90:bte)'. [1] "stddev_pop" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select stddev_pop(rx_cso_num) from x07'. Server says '!SELECT: no such unary operator 'stddev_pop(wrd)''. [1] "stddev" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select stddev(rx_cso_num) from x07'. Server says '!SELECT: no such unary operator 'stddev(wrd)''. [1] "prod" Error in dbGetQuery(db, paste0("select ", i, "(rx_cso_num) from x07")) : Unable to execute statement 'select prod(rx_cso_num) from x07'. Server says '!22003!overflow in conversion of 133 to bte.'. Here's my mserver window: # MonetDB 5 server v11.15.3 "Feb2013-SP1" # Serving database 'medicare_sample', using 8 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 7.860 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:49800/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded > Thanks!!!

2 9

problem with foreign key constrain violation
by Gijs Molenaar 23 Apr '13

23 Apr '13

Hi MonetDB, I think I'm really confused or I found a bug. Please have a look below. I'm expecting a 'aggregation_book_contact_id_fkey' violation, since that row is clearly missing. But I'm getting a 'aggregation_book.aggregation_book_publisher_id_fkey' violation. Is it me or is this error message wrong? sql>\d sys.aggregation_book CREATE TABLE "sys"."aggregation_book" ( "id" INTEGER NOT NULL DEFAULT next value for "sys"."seq_6205", "isbn" VARCHAR(9) NOT NULL, "name" VARCHAR(255) NOT NULL, "pages" INTEGER NOT NULL, "rating" DOUBLE NOT NULL, "price" DECIMAL(6,2) NOT NULL, "contact_id" INTEGER NOT NULL, "publisher_id" INTEGER NOT NULL, "pubdate" DATE NOT NULL, CONSTRAINT "aggregation_book_id_pkey" PRIMARY KEY ("id"), CONSTRAINT "aggregation_book_contact_id_fkey" FOREIGN KEY ("contact_id") REFERENCES "sys"."aggregation_author" ("id"), CONSTRAINT "aggregation_book_publisher_id_fkey" FOREIGN KEY ("publisher_id") REFERENCES "sys"."aggregation_publisher" ("id") ); sql>select * from aggregation_publisher; +------+--------+------------+ | id | name | num_awards | +======+========+============+ | 1 | Apress | 3 | +------+--------+------------+ 1 tuple (0.588ms) sql>select * from aggregation_author; +----+------+-----+ | id | name | age | +====+======+=====+ +----+------+-----+ 0 tuples (0.570ms) sql>INSERT INTO "aggregation_book" ("id", "isbn", "name", "pages", more>"rating", "price", "contact_id", "publisher_id", "pubdate") VALUES (1, more>'159059725', 'The Definitive Guide to Django: Web Development Done more>Right', 447, 4.5, '30.00', 1, 1, '2007-12-06'); INSERT INTO: FOREIGN KEY constraint 'aggregation_book.aggregation_book_publisher_id_fkey' violated -- Gijs Molenaar http://www.astro.uva.nl/people/gijs-molenaar/

4 6

Re: Bug grouping on string column, only when results filtered
by Stefan Manegold 17 Apr '13

17 Apr '13

as string-related bug has recently been fixed (see MonetDB bug tracker; I don't recall the bug ID right now) and will be shipped with the upcoming Feb2013-SP2 release, a preview of which is avilable from the testing repository. Stefan Pete Hollobon <hollobon(a)gmail.com> wrote: Hi Hannes, Thanks for looking into this. I'm using a 64-bit build. Here's my uname and the versions from the MonetDB binaries. ~% uname -a Linux lx1 3.5.0-17-generic #28-Ubuntu SMP Tue Oct 9 19:31:23 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux ~% /usr/bin/monetdbd version MonetDB Database Server v1.7 (Feb2013-SP1) ~% /usr/bin/monetdb version MonetDB Database Server Toolkit v1.1 (Feb2013-SP1) ~% /usr/bin/mclient --version mclient, the MonetDB interactive terminal (Feb2013-SP1) support for command-line editing compiled-in character encoding: UTF-8 However, I realised that my local Feb2013 build was using the shared libraries from the Ubuntu debs. I fixed this, and that build now seems to be working properly. I can still reproduce it with the distributed binaries though. Whatever bug it was must have been fixed already - apologies for wasting your time. Regards, Pete. On 17 April 2013 12:56, Hannes Mühleisen <Hannes.Muehleisen(a)cwi.nl> wrote: > Hello Pete, > > thank you for your bug report and the effort you have put into making this > reproducible for us. However, having followed your instructions with both > the Feb2013 version as well as the current development branch, I was not > able to also provoke this issue. > > I got the following results (Both Feb2013 and default branch): > > ➜ ~ mclient bug -s "select a, count(*) from test where b = 20227 group by > a" > +------+--------+ > | a | L1 | > +======+========+ > | 2T | 193058 | > | 1T | 202267 | > | 5T | 87038 | > | 4T | 100302 | > | 3T | 142326 | > | 15T | 95 | > | 20T | 95 | > | 30T | 14277 | > | 7T | 767 | > | 6T | 22863 | > > | 10T | 127 | > | 6V | 216594 | > | 9T | 104 | > | 8T | 88 | > +------+--------+ > 14 tuples > > ➜ ~ mclient bug -s "select a, count(*) from test group by a" > > +------+---------+ > | a | L1 | > +======+=========+ > | 2T | 5799418 | > | 1T | 6072825 | > | 5T | 2605374 | > | 4T | 2998157 | > | 3T | 4262607 | > | 15T | 2703 | > | 20T | 2818 | > | 30T | 497657 | > | 7T | 22460 | > | 6T | 686789 | > | 10T | 3496 | > | 6V | 6550540 | > | 9T | 1853 | > | 8T | 1639 | > +------+---------+ > 14 tuples > > > Can you perhaps give more information abuont your environment? For example, > this a 64 or 32 bit build? You could also try the development default branch > and see if the problem also occurs there. > > Best, > > Hannes > > > > > > On 17/04/13 10:33, Pete Hollobon wrote: >> >> Hi, >> >> I think I've found a bug in MonetDB. It only occurs when grouping by a >> text column and filtering the results; with no "where" clause, the >> results are fine. The table is fairly large (~30 million rows). >> >> ~% ./generate_test_csv.py test.csv >> ~% mclient bug -s "create table test (b integer, a varchar(5))" >> operation successful >> ~% mclient bug -s "copy into test FROM '/home/pete/test.csv' DELIMITERS >> ','" >> 29508336 affected rows >> >> There are no nulls or empty strings in column "a", however: >> >> ~% mclient bug -s "select a, count(*) from test where b = 20227 group by >> a" >> +------+--------+ >> | a | L1 | >> +======+========+ >> | | 763088 | >> | 10T | 127 | >> | 6V | 216594 | >> | 9T | 104 | >> | 8T | 88 | >> +------+--------+ >> >> When the "where" clause is removed, no nulls are returned: >> >> ~% mclient bug -s "select a, count(*) from test group by a" >> +------+---------+ >> | a | L1 | >> +======+=========+ >> | 2T | 5799418 | >> | 1T | 6072825 | >> | 5T | 2605374 | >> | 4T | 2998157 | >> | 3T | 4262607 | >> | 15T | 2703 | >> | 20T | 2818 | >> | 30T | 497657 | >> | 7T | 22460 | >> | 6T | 686789 | >> | 10T | 3496 | >> | 6V | 6550540 | >> | 9T | 1853 | >> | 8T | 1639 | >> +------+---------+ >> 14 tuples >> >> I have tested with the latest MonetDB Ubuntu debs and also with a >> local build of the Hg Feb2013 branch tip. Both exhibit the same >> problem. I'm running Linux (Ubuntu 12.10) on a 6 core Xeon with 16Gb >> RAM. I've tried with minimal_pipe and the results are the same. >> >> Please let me know if any other information would be useful. >> >> Regards, >> Pete. >> >> >> >> _______________________________________________ >> users-list mailing list >> users-list(a)monetdb.org >> http://mail.monetdb.org/mailman/listinfo/users-list >> > > _______________________________________________ > users-list mailing list > users-list(a)monetdb.org > http://mail.monetdb.org/mailman/listinfo/users-list _______________________________________________ users-list mailing list users-list(a)monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list

1 0

Bug grouping on string column, only when results filtered
by Pete Hollobon 17 Apr '13

17 Apr '13

Hi, I think I've found a bug in MonetDB. It only occurs when grouping by a text column and filtering the results; with no "where" clause, the results are fine. The table is fairly large (~30 million rows). ~% ./generate_test_csv.py test.csv ~% mclient bug -s "create table test (b integer, a varchar(5))" operation successful ~% mclient bug -s "copy into test FROM '/home/pete/test.csv' DELIMITERS ','" 29508336 affected rows There are no nulls or empty strings in column "a", however: ~% mclient bug -s "select a, count(*) from test where b = 20227 group by a" +------+--------+ | a | L1 | +======+========+ | | 763088 | | 10T | 127 | | 6V | 216594 | | 9T | 104 | | 8T | 88 | +------+--------+ When the "where" clause is removed, no nulls are returned: ~% mclient bug -s "select a, count(*) from test group by a" +------+---------+ | a | L1 | +======+=========+ | 2T | 5799418 | | 1T | 6072825 | | 5T | 2605374 | | 4T | 2998157 | | 3T | 4262607 | | 15T | 2703 | | 20T | 2818 | | 30T | 497657 | | 7T | 22460 | | 6T | 686789 | | 10T | 3496 | | 6V | 6550540 | | 9T | 1853 | | 8T | 1639 | +------+---------+ 14 tuples I have tested with the latest MonetDB Ubuntu debs and also with a local build of the Hg Feb2013 branch tip. Both exhibit the same problem. I'm running Linux (Ubuntu 12.10) on a 6 core Xeon with 16Gb RAM. I've tried with minimal_pipe and the results are the same. Please let me know if any other information would be useful. Regards, Pete.

2 2

beginning with monetdb on windows
by Satoshi Nagayasu 09 Apr '13

09 Apr '13

Hi, I'm new to the MonetDB, and trying MonetDB on Windows. At first, I have installed MonetDB on Windows with using MonetDB5-SQL-Installer-x86_64-20130227.msi. http://dev.monetdb.org/downloads/Windows/Feb2013-SP1/ Then, I attempted to follow the Getting Started guide. http://www.monetdb.org/Documentation/Tutorial However, I couldn't find monetdb.exe(bat?) and monetdbd.exe(bat?) in my monetdb installed directory. What can I do for the first step? Any comments? Regards, -- Satoshi Nagayasu <snaga(a)uptime.jp> Uptime Technologies, LLC. http://www.uptime.jp

4 6

MonetDB server crashing on Windows for seemingly simple SELECT statements
by Anthony Damico 01 Apr '13

01 Apr '13

Hi, I'm hitting some very strange errors with a few of my data tables. The data users agreement for these datasets does not allow me to share the actual output and I'm having trouble getting a similar error to occur on public data, so hopefully my description of what's going on will help the troubleshooting. I can send more information about the structure of these tables if that would be helpful? This command causes mserver.exe to crash every time: select state_cd , sum(sex) from x01 where (bene_age_at_end_ref_yr = 64) group by state_cd order by state_cd; This command causes mserver.exe to crash after running it three consecutive times. The first two times, it works. select state_cd , sum(sex) from x01 where (bene_age_at_end_ref_yr < 65) group by state_cd order by state_cd; ALL of these commands work without any trouble on the same data table: select state_cd from x01 where (bene_age_at_end_ref_yr > 64) group by state_cd order by state_cd; select state_cd , sum( metro ) as metro from x01 group by state_cd order by state_cd; select state_cd , sum( metro ) as metro from x01 where (bene_age_at_end_ref_yr > 64) group by state_cd order by state_cd; select distinct bene_age_at_end_ref_yr from x01 order by bene_age_at_end_ref_yr' ) select state_cd , count(sex) from x01 where (bene_age_at_end_ref_yr = 64) group by state_cd order by state_cd; select state_cd , sum(sex) from x01 group by state_cd order by state_cd; select state_cd , sum(sex) from x01 where (bene_age_at_end_ref_yr > 64) group by state_cd order by state_cd; select state_cd , sum(sex) from x01 where (bene_age_at_end_ref_yr >= 64) group by state_cd order by state_cd; I have no idea where to start troubleshooting what's going on here. Can anyone recommend additional tests to diagnose the root of the problem? I really don't understand how flipping the greater than sign on the otherwise same query could cause it to break.. I've pasted a bunch of diagnostics and the mclient in -X mode below, but it really doesn't seem helpful. Thanks!!!!! Here's my mserver.exe version: # MonetDB 5 server v11.15.1 "Feb2013" # Serving database 'medicare_sample', using 4 threads # Compiled for x86_64-pc-winnt/64bit with 64bit OIDs dynamically linked # Found 20.000 GiB available main-memory. # Copyright (c) 1993-July 2008 CWI. # Copyright (c) August 2008-2013 MonetDB B.V., all rights reserved # Visit http://www.monetdb.org/ for further information # Listening for connection requests on mapi:monetdb://127.0.0.1:49800/ # MonetDB/JAQL module loaded # MonetDB/SQL module loaded > When running the first command that breaks inside mclient -X, here's what happens: user(win32):monetdb password: Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013) mapi_query:128:SELECT "name", "value" FROM sys.env() AS env WHERE "name" IN ('gd k_dbname', 'monet_version', 'monet_release', 'merovingian_uri') fetch next block: start at:4 got next block: length:200 text:&1 0 3 2 3 % .env, .env # table_name % name, value # name % varchar, varchar # type % 13, 15 # length [ "gdk_dbname", "medicare_sample" ] [ "monet_version", "11.15.1" ] [ "monet_release", "Feb2013" ] got complete block: text:&1 0 3 2 3 % .env, .env # table_name % name, value # name % varchar, varchar # type % 13, 15 # length [ "gdk_dbname", "medicare_sample" ] [ "monet_version", "11.15.1" ] [ "monet_release", "Feb2013" ] read_line:&1 0 3 2 3 allocating new result set got complete block: text:% .env, .env # table_name % name, value # name % varchar, varchar # type % 13, 15 # length [ "gdk_dbname", "medicare_sample" ] [ "monet_version", "11.15.1" ] [ "monet_release", "Feb2013" ] read_line:% .env, .env # table_name got complete block: text:% name, value # name % varchar, varchar # type % 13, 15 # length [ "gdk_dbname", "medicare_sample" ] [ "monet_version", "11.15.1" ] [ "monet_release", "Feb2013" ] read_line:% name, value # name got complete block: text:% varchar, varchar # type % 13, 15 # length [ "gdk_dbname", "medicare_sample" ] [ "monet_version", "11.15.1" ] [ "monet_release", "Feb2013" ] read_line:% varchar, varchar # type got complete block: text:% 13, 15 # length [ "gdk_dbname", "medicare_sample" ] [ "monet_version", "11.15.1" ] [ "monet_release", "Feb2013" ] read_line:% 13, 15 # length got complete block: text:[ "gdk_dbname", "medicare_sample" ] [ "monet_version", "11.15.1" ] [ "monet_release", "Feb2013" ] read_line:[ "gdk_dbname", "medicare_sample" ] got complete block: text:[ "monet_version", "11.15.1" ] [ "monet_release", "Feb2013" ] read_line:[ "monet_version", "11.15.1" ] got complete block: text:[ "monet_release", "Feb2013" ] read_line:[ "monet_release", "Feb2013" ] fetch next block: start at:204 got next block: length:0 text: got complete block: text:? read_line:? Database: MonetDB v11.15.1 (Feb2013), 'medicare_sample' closing result set Type \q to quit, \? for a list of available commands auto commit mode: on mapi_query:46:SET TIME ZONE INTERVAL '-05:00' HOUR TO MINUTE fetch next block: start at:206 got next block: length:3 text:&3 got complete block: text:&3 read_line:&3 allocating new result set fetch next block: start at:209 got next block: length:0 text: got complete block: text:? read_line:? closing result set sql>select state_cd , sum(sex) from x01 where (bene_age_at_end_ref_yr = 64) grou p by state_cd order by state_cd; mapi_query_part:109:select state_cd , sum(sex) from x01 where (bene_age_at_end_r ef_yr = 64) group by state_cd order by state_cd; fetch next block: start at:211 Connection terminated Press any key to continue . . . When running the second command that breaks inside mclient -X, here's what happens: The first two times, the command runs fine.. Then the third time, it gives: read_line:? +----------+-------+ 67 tuples (241.424ms) closing result set sql>select state_cd , sum(sex) from x01 where (bene_age_at_end_ref_yr < 65) grou p by state_cd order by state_cd; mapi_query_part:109:select state_cd , sum(sex) from x01 where (bene_age_at_end_r ef_yr < 65) group by state_cd order by state_cd; fetch next block: start at:2403 Connection terminated Press any key to continue . . .

3 2

select + group by expression alias from a view breaks connection
by Budulinku Dejmihrasku 29 Mar '13

29 Mar '13

Hi, i hit a strange problem when using alias for an expression in group by i run into this with a complex view containing multiple UNION ALL queries but after a while everything boiled down to a fairly simple test case that will always produce the error Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013-SP1) Database: MonetDB v11.15.3 (Feb2013-SP1), 'mapi:monetdb://lux-analytics:50000/zkdev1' Type \q to quit, \? for a list of available commands auto commit mode: on sql>CREATE VIEW mmtest_v AS SELECT date'2012-01-01' as period, 1 as quantity; operation successful (213.074ms) sql>SELECT extract(month from period) as m, count(1) as cnt FROM mmtest_v GROUP BY m; *Connection terminated* I am on Ubuntu 12.04.2 LTS As the alias in GROUP BY works in other cases i expect this is a bug - pls confirm and i will be happy to file it. BTW it looks like MonetDB has generally issues with selects from more complex views - i.e. views based on another view(s). In such cases the results do not match expectations or there is even an error. I can reproduce the problem but i only on quite large set of data (typically 20+ mil. records) and after some data manipulation got involved, so i cannot give a simple test case as above. Any idea how to pass such problem (when large data-set is involved) to the development team? Thanks in advance milan

3 3

RAM consumption of MonetDB
by Hassan, Ahmad 27 Mar '13

27 Mar '13

Hi Team, Please may I ask what main memory management strategy has been adopted for MonetDB in terms of memory utilization. For example I loaded the TPCH benchmark dataset in MonetDB (v11.13.5) with scale factor 40. The data on disk is 43Gb in size. But when I run the TPCH queries then the main memory used by mserver stays in the range of 0.5GB to 3GB. I was thinking that the monetDB would load the tables into memory because TPCH queries are doing joins over various tables but it seems that monetdb loads only small subset of the complete dataset. Please can someone explain what is going in the background and why I always see 0.5GB to 3GB memory resident size of mserver process even for dataset of size 43G and if this is the normal expected behaviour of monetdb? The farm size on disk is: farms/f1/db1/bat# du . -hs 48G . Thanks. Kind Regards, Ahmad

3 5

Re: Quickstart Guide
by Stefan Manegold 20 Mar '13

20 Mar '13

Sterling, Thanks for notifing! Could you please file a bug report via http://bugs.monetdb.org/ ? Thanks! Stefan Sterling Paramore <gnilrets(a)gmail.com> wrote: The link at http://www.monetdb.org/Downloads/Tutorial is broken and I don't see any way to contact the creators of the web page. How can I get information about how to install MonetDB? Thanks, Sterling

4 4