- users-list - monetdb.org

Problem: Relational query without result
by Martin Schwitalla 22 Jul '15

22 Jul '15

Hi, I have a problem. I have tested on MonetDB 11.19.9 a query and wanted to test the same query on a better Workstation and installed MonetDB 11.19.15. But now I get every time the message „realtional query without result“ when I try to run the query. Can anybody help? Kind regards, Martin

4 8

How to work with MAL-statements?
by Martin Schwitalla 22 Jul '15

22 Jul '15

Hello, to improve execution time I wanted to work with MAL-statements, so that I could directly control the query plan. But it seems that the generated MAL-Plans don`t „just work“ when I type them. I tried to get used to MAL with the simple query „select * from table;“. This got me the following plan: function user.s1_1{autoCommit=true}():void; barrier X_59 := language.dataflow(); X_2 := sql.mvc(); X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","diseases"); X_6:bat[:oid,:int] := sql.bind(X_2,"sys","diseases","id",0); (X_9,r1_9) := sql.bind(X_2,"sys","diseases","id",2); X_12:bat[:oid,:int] := sql.bind(X_2,"sys","diseases","id",1); X_14 := sql.delta(X_6,X_9,r1_9,X_12); X_15 := algebra.leftfetchjoin(X_3,X_14); X_23:bat[:oid,:str] := sql.bind(X_2,"sys","diseases","name",0); (X_25,r1_25) := sql.bind(X_2,"sys","diseases","name",2); X_27:bat[:oid,:str] := sql.bind(X_2,"sys","diseases","name",1); X_28 := sql.delta(X_23,X_25,r1_25,X_27); X_29 := algebra.leftfetchjoin(X_3,X_28); X_16:bat[:oid,:str] := sql.bind(X_2,"sys","diseases","short_name",0); (X_18,r1_18) := sql.bind(X_2,"sys","diseases","short_name",2); X_20:bat[:oid,:str] := sql.bind(X_2,"sys","diseases","short_name",1); X_21 := sql.delta(X_16,X_18,r1_18,X_20); X_22 := algebra.leftfetchjoin(X_3,X_21); language.pass(X_3); exit X_59; X_30 := sql.resultSet(3,1,X_15); sql.rsColumn(X_30,"sys.diseases","id","int",32,0,X_15); sql.rsColumn(X_30,"sys.diseases","short_name","varchar",255,0,X_22); sql.rsColumn(X_30,"sys.diseases","name","varchar",255,0,X_29); X_40 := io.stdout(); sql.exportResult(X_40,X_30); end s1_1; But when I try to run this statements I get the following error: MAPI = (monetdb) /tmp/.s.monetdb.5250 QUERY = end s1_1; ERROR = !TypeException:user.s1_1[7]:'sql.delta' undefined in: X_14:any := sql.delta(X_6:bat[:oid,:int], X_9:bat[:oid,:oid], r1_9:bat[:oid,:any], X_12:bat[:oid,:int]) !ERROR: MSresetVariablescould not allocate space for What am I`m missing that it doesn`t work? Kind regards, Martin

3 3

QUANTILE( colname , 0 ) and QUANTILE( colname , 1 ) problem
by Anthony Damico 21 Jul '15

21 Jul '15

hi monetdb team, i am getting errors below on data i can't share. before i spend a bunch of time trying to create a reproducible example for this: i've confirmed that MIN and MAX both do work on this column, and also that QUANTILE works with 0.01 thru 0.99 on this column. is there a reason QUANTILEs of 0 and 1 don't simply call MIN and MAX? thanks! Error in .local(conn, statement, ...) : Unable to execute statement 'SELECT QUANTILE( tot_pay , 0 ) FROM acons WHERE yr = 2013 AND ( dattyp1 = 1 OR dattyp2 = 1 OR dattyp...'. Server says 'overflow in conversion of 43553.818965373482 to bte.' [#22003]. > > unwtd_pct <- dbGetQuery( db , paste( "SELECT QUANTILE( tot_pay , 1 ) FROM acons" , where_clause ) ) Error in .local(conn, statement, ...) : Unable to execute statement 'SELECT QUANTILE( tot_pay , 1 ) FROM acons WHERE yr = 2013 AND ( dattyp1 = 1 OR dattyp2 = 1 OR dattyp...'. Server says 'overflow in conversion of 43553.818965373482 to bte.' [#22003]. >

3 3

CAST and CONVERT string to int
by Dennis Wiest 20 Jul '15

20 Jul '15

Hello, I have a question for the Operation Cast and CONVERT. I have the following table and wants to run this query. select * from value v2 WHERE v2.xpath_id = 378 AND v2.value BETWEEN 1000 AND 2000 LIMIT 10; This provides me with the wrong result because v2.value is a textfield. Now I wanted to with the following query to fix this problem, but I get an error. Is it at all possible to the cast or convert? select * from value v2 WHERE v2.xpath_id = 378 AND CAST(CAST(v2.value AS float)AS integer) > 1000 LIMIT 10; conversion of string 'RNAV' to type dbl failed. But this one Works select CAST(CAST(v2.value AS float)AS integer) from value v2 WHERE v2.xpath_id = 378 LIMIT 10; The problem is, "value" must be a text field, "value" can take all possible values. CREATE TABLE value ( id int NOT NULL AUTO_INCREMENT, element_id int NOT NULL, xpath_id int NOT NULL, value text, PRIMARY KEY (id) ); Thanks 4 Help Greetings Dennis ________________________________ Avitech GmbH Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany Court Registration: Amtsgericht Ulm | HRB 728293 Gesch?ftsf?hrer/Managing Director: Antonio Maria Gonzalez Gorostiza VAT No.: DE223719716 http://avitech.aero

3 5

combined conditions in user defined function made monetdb exited.
by integrity 19 Jul '15

19 Jul '15

Dear experts, I tried to create an function in monetdb, but the first one successful, the second one successful, but the combo failed. create function neighbor19() returns table (ra_avg double) begin declare table uzone (id bigint, ra_avg double); insert into uzone select id, ra_avg from uniquecatalog11; return table( select ra_avg from uzone as u0 where id between 10 and 20 ); end; successful. create function neighbor20() returns table (ra_avg double) begin declare table uzone (id bigint, ra_avg double); insert into uzone select id, ra_avg from uniquecatalog11; return table( select ra_avg from uzone as u0 where ra_avg between 10 and 20 ); end; successful. create function neighbor30() returns table (ra_avg double) begin declare table uzone (id bigint, ra_avg double); insert into uzone select id, ra_avg from uniquecatalog11; return table( select ra_avg from uzone as u0 where id between 10 and 20 and ra_avg between 10 and 20 ); end; failed with monetdb exited. in merovingian.log: 2015-07-19 13:34:36 MSG merovingian[25753]: database 'gwacdb' (633) was killed by signal SIGSEGV I tried on default and Jul2015 branch, all the same with each other. Any help or hints are appreciated! Best, Meng

3 5

Re: MonetDB performance disappointing
by Niels Nes 18 Jul '15

18 Jul '15

Martin Then trace should be somewhat different as the generated plan changed. Could you send the output of plan . Niels So put plan infront of the query. -------- Original message -------- From: Martin Schwitalla <MSchwitalla1(a)gmx.de> Date: 18/07/2015 12:52 (GMT+01:00) To: Communication channel for MonetDB users <users-list(a)monetdb.org> Cc: Niels Nes <Niels.Nes(a)cwi.nl> Subject: Re: MonetDB performance disappointing Hi, sorry that it took so long but I could test the patch and it didn`t seem to change anything. The trace looks the same and the performance didn`t improve. Regards, Martin _______________________________________________ users-list mailing list users-list(a)monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

1 0

nearest value question on stackoverflow
by Anthony Damico 17 Jul '15

17 Jul '15

https://stackoverflow.com/questions/30641876/monetdb-sql-method-to-locate-o… i'm thinking i can do this with some costly self-join, but i'd appreciate any other eyes on the problem

4 12

No subject
by Bogdan Dimitrov 17 Jul '15

17 Jul '15

This is by no means minimal, but here I've listed some of the issues I had after manually building MonetDB version 11.19.9 with R integration: https://www.monetdb.org/pipermail/users-list/2015-April/008034.html Bogdan ------------------------------ Message: 4 Date: Thu, 25 Jun 2015 23:48:41 +0200 From: musa <pehlewan(a)gmail.com> To: users-list(a)monetdb.org Message-ID: <CAH1mZ_GQScbSTuDhDoy3W65FETH2e0Ntx8a7aScYwcVTjRh0WA(a)mail.gmail.com> Content-Type: text/plain; charset="utf-8" Can someone please provide a minimal example (how to start the server) for running the embedded R feature in windows? Regards, mpa ________________________________

2 1

Should MonetDB really be used in production?
by Frédéric Jolliton 16 Jul '15

16 Jul '15

Greetings, We filled a bug few days ago about simple SAVEPOINT usage corrupting the database (both in Oct2014-SP4 and tip of hg). This is worrying us a lot, because that's yet another bug easy to trigger. We're studying MonetDB extensively for our future needs. We've written optimizers, custom aggregate functions in C, and we've studied many internal aspects. But yet, from time to time, we are hit by bugs when performing simple, trivial queries. You can see the history of bugs we filled on the bugtracker (regarding UNION, SELECT, ..). This become time consuming. This make us nervous because we might have hard time trusting our complexes queries run by MonetDB. The performance are stunning, but the quality seems not high enough for production. Projects like PostgreSQL shows that even Open Source projects can attains incredible stability and robustness. (Admittedly there are many more contributors to PostgreSQL.) Note that we understand that it is a complex software, and that it is hard to cover all possible corner cases in tests. But we're wondering how stable MonetDB is expected to be. Is this really used in production? Regarding the MonetDB website, the primary source of information for the project, we've not found any reference documentation regarding all the supported SQL features. Sure, there is a page listing what is *not* supported, there are pages on various topics, but it is unclear if SAVEPOINT is really supported. (If I can say, I think that the website probably needs some refresh and better navigation.) Here is the latest bug I was talking about: $ bin/mclient -p 50005 test Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014-SP4) Database: MonetDB v11.19.13 (Oct2014-SP4), 'mapi:monetdb://fjolliton:50005/test' Type \q to quit, \? for a list of available commands auto commit mode: on sql>start transaction; auto commit mode: off sql>create table foo (a int); operation successful (0.528ms) sql>savepoint sp1; auto commit mode: off sql>release savepoint sp1; auto commit mode: off sql>commit; auto commit mode: on sql>select * from foo; +---+ | a | +===+ +---+ 0 tuples (0.9s) So far, so good. Now try to connect again: $ bin/mclient -p 50005 test Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014-SP4) Database: MonetDB v11.19.13 (Oct2014-SP4), 'mapi:monetdb://fjolliton:50005/test' Type \q to quit, \? for a list of available commands auto commit mode: on sql>select * from foo; SELECT: no such table 'foo' Whoops ! Regards, -- Frédéric Jolliton Sécuractive

9 10

Re: Re: Provide --password option for msqldump tool
by GMail 16 Jul '15

16 Jul '15

1 0