- users-list - monetdb.org

[MonetDB-users] Recursing into a tree via SQL?
by Anton Berezin 16 Nov '09

16 Nov '09

Hi, I've been wondering whether MonetDB/SQL provides something equivalent to Oracle's START WITH ... CONNECT BY ... and if not, how difficult would it be to emulate the behavior with user functions. For example, PostgreSQL provides connectby() function which does that. The table in question is like that: id int, parent_id int, other stuff And it holds multiple small trees, with the root node for each having NULL parent_id. Thanks, \Anton. -- Matters of elegance ought to be left to the tailor and to the cobbler. -- L. Boltzmann

1 0

[MonetDB-users] Bug- & Test- status assessment: Please help!
by Stefan Manegold 15 Nov '09

15 Nov '09

Dear fellow MonetDB developers, Dear MonetDB users, preparing for the upcming Nov2009 release we spent quite some time and efford in cleaning-up the "Stable" TestWeb ( cf., http://monetdb.cwi.nl/Development/TestWeb/Stable/ ) and fixing as many open bugs as possible. Big thansk go to all of you that were actively involved! To further assess the status of (failing) tests and in particular open bug reports ( cf., https://sourceforge.net/tracker/?words=tracker_browse&sort=priority&sortdir… ) I ask all of you to 1) check the open bugs submitted by you, https://sourceforge.net/tracker/?words=tracker_browse&sort=submitted_by&sor… a) whether there is a test in CVS for this bug or not; b) whether the problem still occurs, i.e., whether the test still fails, or whether the problem still occurs for you with the latest Nov2009 CVS code base. Please report any findings in detail via the bug tracker. 2) check the open bugs assigned to you https://sourceforge.net/tracker/?words=tracker_browse&sort=assigned_to&sort… a) whether there is a test in CVS for this bug or not; b) whether the problem still occurs, i.e., whether the test still fails, or whether the problem still occurs for you with the latest Nov2009 CVS code base. Please report any findings in detail via the bug tracker. 3) check all bug reports closed since Aug2009, whether they are (if applicable) backed-up by a test in CVS. 4) check all failing test in the TestWeb http://monetdb.cwi.nl/Development/TestWeb/Stable/ whether they are documented by a bug report. Thank you very much in advance! Stefan -- | Dr. Stefan Manegold | mailto:Stefan.Manegold@cwi.nl | | CWI, P.O.Box 94079 | http://www.cwi.nl/~manegold/ | | 1090 GB Amsterdam | Tel.: +31 (20) 592-4212 | | The Netherlands | Fax : +31 (20) 592-4312 |

1 0

[MonetDB-users] help with MALException meaning
by Alex Bo. 11 Nov '09

11 Nov '09

Hi, I got this exception when running a query on Aug-SP1 (Windows): ExecuteNonQuery failed: !MALException:!ERROR: BATpropcheck: BAT tmpr_1566(-886) with 35392 tuples seqbase of dense oid bat is wrong! 0 != 12306 !COMMIT: failed !ERROR: COMMIT: transaction is aborted, will ROLLBACK instead What does it mean? -- View this message in context: http://old.nabble.com/help-with-MALException-meaning-tp26300205p26300205.ht… Sent from the monetdb-users mailing list archive at Nabble.com.

2 1

[MonetDB-users] High availability
by XueHJ 11 Nov '09

11 Nov '09

Hi: Does the MonetDB have a high-availability solution? best regards, allegro

2 1

[MonetDB-users] Error when uploading integers
by Roman Sokolyuk 10 Nov '09

10 Nov '09

Hi, I ran into the following problem: I used the COPY command to upload a large number of records and I got an error saying that a particular field was expecting an int. In the file which I am trying to upload fields are separated with commas ",", and records are separated with new-line characters - "\n". The value it was trying to upload and failed was basically an empty field - ",,". I worked around the problem by setting every entry in the column that was not set to zero - so the field would get its int, and later changing it back to null in the table. But it seems like a common task to upload null values into an integer column, so why would it fail?

2 1

[MonetDB-users] HEAPalloc: Insufficient space for HEAP
by tbohr＠quantentunnel.de 10 Nov '09

10 Nov '09

Hi, when I run the following query (complete testcase given), the server throws an exception !MALException:bbp.project:Can not create object !ERROR: HEAPalloc: Insufficient space for HEAP of 91226112 bytes. The result should be 11390625 (15^6) rows. The corresponding count query succesfully delivers this value. The amount of used memory of the mserver5.exe process is about 1.2GB at the time of failure. The testcase is: create table d (id integer, ordinal integer, value varchar(50)); insert into d (id, ordinal, value) values (1,1,'a'), (2,2,'b'), (3,3,'c'), (4,4,'d'), (5,5,'e'), (6,6,'f'), (7,7,'g'), (8,8,'h'), (9,9,'i'), (10,10,'j'), (11,11,'k'), (12,12,'l'), (13,13,'m'), (14,14,'n'), (15,15,'o'); create table M_189 (d1 integer, d11 integer, d19 integer, d4 integer, d6 integer, d8 integer, value double); SELECT G.M_ID, G.VALUE, G.d1, G.d11, G.d19, G.d4, G.d6, G.d8 FROM (SELECT DISTINCT 189 AS M_ID, 1 AS M_ORDINAL, M_189.VALUE AS VALUE, D1.ID AS d1, D11.ID AS d11, D19.ID AS d19, D4.ID AS d4, D6.ID AS d6, D8.ID AS d8 FROM D D11 CROSS JOIN D D1 CROSS JOIN D D4 CROSS JOIN D D6 CROSS JOIN D D8 CROSS JOIN D D19 LEFT OUTER JOIN M_189 ON M_189.d1=D1.ID AND M_189.d11=D11.ID AND M_189.d19=D19.ID AND M_189.d4=D4.ID AND M_189.d6=D6.ID AND M_189.d8=D8.ID ) G JOIN D D11 ON D11.ID=G.d11 JOIN D D6 ON D6.ID=G.d6 JOIN D D8 ON D8.ID=G.d8 JOIN D D19 ON D19.ID=G.d19 JOIN D D1 ON D1.ID=G.d1 JOIN D D4 ON D4.ID=G.d4 ORDER BY G.M_ORDINAL, D11.ORDINAL, D6.ORDINAL, D8.ORDINAL, D19.ORDINAL, D1.ORDINAL, D4.ORDINAL; Kind regards, TB

3 6

[MonetDB-users] Possible wrong results when Querying
by LeonardF 10 Nov '09

10 Nov '09

Hi There, I have some problem with the row_number() over() function. Consider the following table: create table "table1" ("customer" varchar(40), "product" varchar(40), "price" double) insert into "table1" values ('cust1', 'p1', 100) insert into "table1" values ('cust1', 'p2', 200) insert into "table1" values ('cust1', 'p3', 150) insert into "table1" values ('cust2', 'p1', 300) insert into "table1" values ('cust2', 'p3', 200) The following query over this table: SELECT "customer", "product", "sumprice", (Row_number() OVER(PARTITION BY "customer" ORDER BY "sumprice")) as "rank" FROM ( SELECT "customer", "product", (Sum("price")) AS "sumprice" FROM "table1" GROUP BY "customer", "product") AS "temp" Returns: Customer product sumprice rank Cust1 p1 100 1 Cust1 p2 200 2 Cust1 p3 150 3 Cust2 p1 300 1 Cust2 p3 200 2 But doesn’t it suppose to return the following result set?: Customer product sumprice rank Cust1 p1 100 1 Cust1 p3 150 2 Cust1 p2 200 3 Cust2 p3 200 1 Cust2 p1 200 2 Thanks -- Leonard Forgge -- View this message in context: http://www.nabble.com/Possible-wrong-results-when-Querying-tp22821802p22821… Sent from the monetdb-users mailing list archive at Nabble.com.

3 2

[MonetDB-users] type mismatch on COPY command
by Roman Sokolyuk 09 Nov '09

09 Nov '09

Hi, I am trying to upload 500000 records using copy command and I am getting the following error: !SQLException:sql:value 'somestring' while parsing 'somestring,1,,,2009-05-11 00:00:00,0,,' from line 250108 field 3 not inserted, expecting type str !SQLException:importTable:failed to import table the type of the field is varchar(100). Why wouldn't it insert something like 'somestring'. Also, each row has another "string" column - "name" and it didn't complain about the names at all.

2 2

[MonetDB-users] Pf:attribute syntax
by Floris Ouwendijk (DT) 09 Nov '09

09 Nov '09

Hi, Can anybody shed some light on the pf:attribute syntax? I have some straightforward queries that match on a unique attribute but do not perform as desired. I'm hoping that using pf:attribute the corresponding element can directly be located. Kind regards, Floris

3 3

[MonetDB-users] MALException:algebra.join, HEAPextend: failed to extend
by tbohr＠quantentunnel.de 09 Nov '09

09 Nov '09

Hi, I am trying to execute a rather complex aggregation query (referencing a nested VIEW hierarchy) on a star schema. The server reports this exception: Error: MALException:algebra.join:GDK reported error. ERROR: HEAPextend: failed to extend to 115574952 for 76\7667tail The memory used by the mserver5.exe process after running the query the first time is about 780K. Running the query the second time even crashes the instance. The server (Release 5.14.2) runs on a 3,5GB Dual Core machine with Win XP 32bit. This is the last memory debug info after starting the server: #GDKmemchk (memcur=8910680,memmax=3061052579) (vmcur=58720256,vmmax=1610612736) What I am noticing is that after having executed the query the first time two comparably big files (the above reported 76\7667tail and a tail file) with 94MB each are generated in the bat folder. All the other files there are in the lower KB range. Is there a limit on the view nesting hierarchy (other than memory limitations)? Can I tune the accepted memory limits somehow? It seems that MonetDB does not use all available memory. Kind regards, TB.

1 0