- users-list - monetdb.org

[MonetDB-users] limit resident memory usage
by Roberto Cornacchia 23 Nov '09

23 Nov '09

Hi, Is there a way to limit the usage of -resident- memory? Say I have 8GB RAM. What I would like is being able to say: no matter what, use up to 2GB resident memory, and as much virtual memory as you like. I have tried to play around with gdk_mem_maxsize and gkd_mmap_minsize, but with no luck so far. Maybe I haven't very clear yet how they work? Roberto

1 0

[MonetDB-users] strange UNION exception
by Alex Bo. 19 Nov '09

19 Nov '09

Hi, Every once in a while when using a UNION query I get an exception that looks like this: !UNION: column counts (3 and 2) do not match (the numbers '3' and '2' vary from case to case) The message isn't right, because the queries I run do have matching column counts between the Unions. This happens to me in various scenarios. The thing that is common to all the cases is that it seems to happen when some sort of threshold is passed. For example, I can have a working query with 50 OR clauses in the WHERE statement that works, but as soon as I add the 51th OR clause, I get the exception. In other cases I get the same exception with a different amount of OR clauses, but again I see the behavior of the exception appearing when adding one more OR clause to a working query. The thing that is in common to all the failing queries is that there's always a join on two tables and I get data from both tables with a lot of OR clauses. If necessary I can upload data and queries that illustrate this, but the queries are rather long. Please advise. Thanks. -- View this message in context: http://old.nabble.com/strange-UNION-exception-tp26421401p26421401.html Sent from the monetdb-users mailing list archive at Nabble.com.

2 2

[MonetDB-users] COPY with empty fields
by pphillips99＠gmail.com 19 Nov '09

19 Nov '09

COPY 1 OFFSET 100 RECORDS INTO tlbc FROM '/mnt/data/myfile.txt' DELIMITERS '\t', '\r\n' Trying to use COPY to import tab delimited text files. The text file has a header row and I've verified the delimiters and eols. I have a number of empty columns at the end of each row in the text file and those colums in the table are a combination of integers and decimals. Received the following error at the first of the empty columns: !SQLException:sql:value '' while parsing from line 0 field 213 not inserted, expecting type lng !SQLException:importTable:failed to import table !ERROR: Decimal cannot be parsed from lng I realize I could do manipulations but it's second choice given the data size, but the file is 8G. How can I overcome? Paul

4 7

[MonetDB-users] CSV file to a table
by Nafees Ur Rehman 17 Nov '09

17 Nov '09

Hi, I am trying to read a CSV file content to a table. But I think Im making mistake in the syntax somehow. Can any one please guide me a little? Regards Nafees Ur Rehman

3 3

[MonetDB-users] reading from a file
by Nafees Ur Rehman 17 Nov '09

17 Nov '09

Hi, I am trying to insert records from a file into a table WQ using the following code. Please if someone can help me write my code correctly. copy into WQ "C:\Document and Settings\Rehman\Desktop\wq.txt" USING DELIMITERS field_seperator ',' record_seperator '\n'; Regards Nafees Ur Rehman

2 1

[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

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

07 Nov '09

Does the MonetDB have a high-availability solution?

1 0

[MonetDB-users] limit the number of rows in the result set
by Roman Sokolyuk 06 Nov '09

06 Nov '09

Hi, Could someone please tell me what the syntax is for selecting only say the top 20 rows ?

2 1

[MonetDB-users] Best way to avoid division by zero?
by Guillaume Theoret 06 Nov '09

06 Nov '09

I seem to have hit a bug (I just reported it) and now I'm using an ugly hack to get around it but I was wondering if there's a better way to avoid division by zero errors. The bug is that MonetDB seems to evaluate both branches of a CASE regardless of whether the branch will be taken or not: sql>SELECT more>CASE SUM(payout_units_cpc) more>WHEN 0 THEN 0 more>ELSE 1 more>END AS avg_cost_cpc more>FROM daily.stats_2009_week_31; SELECT +--------------+ | avg_cost_cpc | +==============+ | 0 | +--------------+ 1 tuple Timer 27.990 msec 1 rows sql>SELECT more>CASE SUM(payout_units_cpc) more>WHEN 0 THEN 0 more>ELSE cast(SUM(payout_units_cpc * payout_cpc) as numeric(12,4)) / SUM(payout_units_cpc) more>END AS avg_cost_cpc more>FROM daily.stats_2009_week_31; !MALException:calc./:Illegal argument Division by zero 0 tuples Timer 16.415 msec 0 rows So what I did was I changed my query to: SELECT CASE SUM(payout_units_cpc) WHEN 0 THEN 0 ELSE cast(SUM(payout_units_cpc * payout_cpc) as numeric(12,4)) / (SUM(payout_units_cpc) + 0.000001) END AS avg_cost_cpc FROM daily.stats_2009_week_31 WHERE date_added >= 1249099200 AND date_added <= 1249185599 Because of the decimal precision it won't affect my numbers but it feels wrong.

1 0

[MonetDB-users] substring extraction
by Roman Sokolyuk 06 Nov '09

06 Nov '09

Hi, If I have a column that contains email addresses, is there a way for me to select just the domain from SQL, so for dave(a)gmail.com, bob(a)yahoo.com, I will get just "gmail.com", and "yahoo.com"?

2 4

[MonetDB-users] Mathematical functions
by Roman Sokolyuk 05 Nov '09

05 Nov '09

Hi, I am encountering the following issue: SELECT field1, //returns 4 field2, //returns 1 ( ( field2 / field1) * 100) //this returns zero - not what I want - I am looking for 25 Am I doing something wrong with type conversion (integer/floating point)?

3 7

[MonetDB-users] Where is JAR file for monetdb Driver?
by Nafees Ur Rehman 05 Nov '09

05 Nov '09

Hi, I want to configure DBVisualizer with monetdb/sql. When I register for a driver, I can't find the driver files. I follow the instruction listed on the monetdb webpages. Please note that I am using WindowsXP. I would appreciate a kind response. Regards Nafees Ur Rehman __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com

2 1

[MonetDB-users] Group By bug?
by Guillaume Theoret 04 Nov '09

04 Nov '09

I think this is a bug in Group By, I would just like to confirm before I submit a bug report. sql>SELECT payout_type, count(*) FROM test.aggregated_detail_data_2009_week_33 stats WHERE date_added >= 1250222400 AND date_added < 1250308799 GROUP BY payout_type; SELECT payout_type, count(*) FROM test.aggregated_detail_data_2009_week_33 stats WHERE date_added >= 1250222400 AND date_added < 1250308799 GROUP BY payout_type; +-------------+-------+ | payout_type | L206 | +=============+=======+ | 1 | 99021 | | 5 | 8719 | | 4 | 8940 | | 2 | 72 | | 3 | 39 | +-------------+-------+ 5 tuples Timer 13.464 msec 5 rows sql>SELECT 0 as breakdown_id, payout_type, count(*) FROM test.aggregated_detail_data_2009_week_33 stats WHERE date_added >= 1250222400 AND date_added < 1250308799 GROUP BY breakdown_id, payout_type; !TypeException:user.s2_1[6]:'group.new' undefined in: (ext14:any, grp12:any) := group.new(_11:bte) !TypeException:user.s2_1[33]:'group.derive' undefined in: (ext52:any, grp50:any) := group.derive(ext14:any, _14:any, _48:bat[:oid,:int]) !TypeException:user.s2_1[35]:'bat.mirror' undefined in: _53:any := bat.mirror(ext52:any) !TypeException:user.s2_1[36]:'algebra.join' undefined in: _54:any := algebra.join(_53:any, _48:bat[:oid,:int]) !TypeException:user.s2_1[39]:'aggr.count' undefined in: _57:bat[:any,:wrd] := aggr.count(_56:bat[:oid,:any], _52:any, _53:any) !SQLException:SQLengine:Program contains errors 0 tuples Timer 1.234 msec 0 rows

1 0

[MonetDB-users] COUNT on varchar column, counts nulls as well - how can I prevent that?
by Roman Sokolyuk 02 Nov '09

02 Nov '09

Hi, I am trying to run AGGR functions on a table - COUNT (column_name) and it looks like it counts <null>'s as well. Is there a way to prevent it?

4 3

[MonetDB-users] UTF-8 support
by Roman Sokolyuk 30 Oct '09

30 Oct '09

Hi, I installed MonetDB on Vista and am experiencing issues with the COPY utility and uploading text. Text that comes from CVS files and looks plain in Notepad generates errors like this one: *!SQLException:sql:value '∩╗┐505' while parsing '∩╗┐505,787, !SQLException:importTable:failed to import table* Is it possible I installed Monet without UTF-8 support? How can I check? How can I fix it?

2 1

[MonetDB-users] Convert date field to its proper name
by Roman Sokolyuk 30 Oct '09

30 Oct '09

Hi, Is there a way to convert a month number to its name? For example, I am executing the following statement: EXTRACT(MONTH FROM signup_date), but I want "1" to display as "January". Thanks.

2 2