November 2007 - users-list

[MonetDB-users] Howto query with non ascii in python?
by m h 30 Nov '07

30 Nov '07

How do I run non-ascii queries in python? Or from the command line for that matter? Naive tests on my part fail.... python: RuntimeError: !syntax error, unexpected IDENT, expecting INTERSECT or EXCEPT or UNION in: "select name from tables where system=false and name=unitéble2" commandline: sql>select name from tables where system=false and name=unitéble2; !syntax error, unexpected IDENT, expecting INTERSECT or EXCEPT or UNION in: "select name from tables where system=false and name=unitéble2" Thanks -matt

3 2

[MonetDB-users] incorrect handling of NULL values in "IN" statements?
by m h 29 Nov '07

29 Nov '07

My understanding that is that NULL is never equal to anything including itself. Thus to test for NULL values, one must use the IS operator. I'm wondering if the following is a bug. The NOT IN will return the null row, instead of the empty set. Are NULL values outside that query? CREATE TABLE query_users ( user_id INT NOT NULL AUTO_INCREMENT, user_name VARCHAR(20), PRIMARY KEY (user_id) ); INSERT INTO query_users (user_id, user_name) VALUES (1, 'matt'); INSERT INTO query_users (user_id, user_name) VALUES (2, 'fred'); INSERT INTO query_users (user_id, user_name) VALUES (3, null); SELECT user_name FROM query_users WHERE user_name IN ('matt', 'fred'); -- should return empty set, returns null SELECT user_name FROM query_users WHERE user_name NOT IN ('matt', 'fred');

2 3

[MonetDB-users] use column names as column alias?
by m h 29 Nov '07

29 Nov '07

Is it possible to use the column names as the alias? The select query below fails. CREATE TABLE query_users ( user_id INTEGER NOT NULL, user_name VARCHAR(20), PRIMARY KEY (user_id) ); INSERT INTO query_users (user_id, user_name) VALUES (1,'matt'); select query_users.user_id AS "query_users.user_id", query_users.user_name AS "query_users.user_name" from query_users; Here's the error: !Invalid identifier 'query_users.user_id' -matt

2 1

[MonetDB-users] SQL exception during query resolving. Slow join? How to submit external variables?
by Huib Verweij 28 Nov '07

28 Nov '07

Hi everyone, I'm new to MonetDB XQuery and have written a crude Cocoon generator for MonetDB using the example code for the JDBC client from the website. I am using the latest Bug Fix Release of MonetDB as announced by Sjoerd Mullender on 24-10-2007. When I execute the following query I get an error: --------------------------------------------------------- let $zz := doc('vogels.xml')//Vogels[data(Dwaalgast) = 'zeer zeldzaam'] return <vogeltjes>{$zz}</vogeltjes> --------------------------------------------------------- throws: java.sql.SQLException: ERROR: ERROR: XML Generation: NULL BAT has a 4415 head, but tail is NULL. ERROR: ERROR: xquery_print_result_loop: operation failed. However, when I change the XQuery to: --------------------------------------------------------- let $zz := doc('vogels.xml')//Vogels[data(Dwaalgast) = 'zeer zeldzaam'] return <vogeltjes>{$zz}azaz</vogeltjes> --------------------------------------------------------- everything's OK and I get the proper result. (The previous release threw the same error btw.) Also, when I join the results from the above query with another table to resolve some codes to code-descriptions, the query takes from 8.5 up to 11 seconds to execute, vs. <1 second for a query without the join. The result is a little over 1Mb in size. The query I use is this: --------------------------------------------------------- let $zz := doc('vogels.xml')//Vogels[Ecotoop_x0020_nest] return <vogeltjes> {for $vogel in doc('vogels.xml')//Vogels[Ecotoop_x0020_nest], $vc in doc('vogels-code.xml')//Vogels-code[data(Kolomnaam) = 'Ecotoop nest'][data(Code) = $vogel/Ecotoop_x0020_nest]/Codebeschrijving return <vogel> {$vogel/*} {$vc} </vogel> } </vogeltjes> --------------------------------------------------------- The latest preview release of eXist handles the join in 3.1 seconds, any ideas on why this join takes almost three times as long? I have a final question: how do I specify external variables using the JDBC client? Regards, Huib Verweij.

2 2

[MonetDB-users] Why does MonetDB take indexes not into account?
by Markus Gritsch 28 Nov '07

28 Nov '07

Hi, I basically have this schema: CREATE TABLE "act" ( "id" int NOT NULL auto_increment, "title" text NOT NULL, "is_closed" bool NOT NULL, PRIMARY KEY ("id") ); CREATE INDEX act_isClosedIndex ON act (is_closed); CREATE TABLE "entry" ( "id" int NOT NULL auto_increment, "note" text NOT NULL, "act_id" int NOT NULL, PRIMARY KEY ("id"), CONSTRAINT "entry_act_id_exists" FOREIGN KEY ("act_id") REFERENCES "act" ("id") ); CREATE INDEX entry_actIndex ON entry (act_id); Each "act" has several "entries" and each "entry" belongs to one "act". *) full-text search in the "note" column over all entries: SELECT * FROM entry WHERE entry.note LIKE ('%hui%') MySQL: 4.5 Seconds MonetDB: 0.8 Seconds So this is very good and this motivates me to switch to MonetDB. However, the majority of the acts have the "is_closed" field set. The usual case in my application is to take into account only the entries of open acts: *) full-text search in the "note" column over all entries of the open acts: SELECT * FROM entry, act WHERE entry.act_id = act.id AND act.is_closed = 0 AND entry.note LIKE ('%hui%') MySQL: 0.26 Seconds MonetDB: 0.8 Seconds Here, MySQL can take advantage of the index on the is_closed column, whereas MonetDB does not. Is this intended? Databases without indexes seem strange to me. Markus

4 16

[MonetDB-users] python cursor missing lastrowid attribute?
by m h 27 Nov '07

27 Nov '07

In MonetSQLdb BaseCursor.insert_id, the docstring says to use the lastrowid attribute. When I try to access that lastrowid on the cursor instance I get an AttributeError. Should I file a bug for this? matt

5 21

[MonetDB-users] Another query that works in MySQL and SQLite but not in MonetDB
by Markus Gritsch 26 Nov '07

26 Nov '07

Hi, I have another question with an SQL query that works in SQLite and MySQL but does not in MonetDB. SELECT * FROM test WHERE ((1) AND (id=100)) syntax error, unexpected AND in: "prepare select * from test where ((1) and" The query is forged by an ORM. I programmatically put together the WHERE clause. All the contitions are put together using AND, and in the case of no conditions, the WHERE clause simply reads (1), which yields all entries in the table. However, MonetDB cannot parse this. Markus

4 9

[MonetDB-users] Problem with colums named "begin" and "end"
by Markus Gritsch 25 Nov '07

25 Nov '07

Hi, I have a table with columns named "begin" and "end". With SQLite and MySQL the SELECT works all right, but MonetDB cannot parse the SQL: sql>CREATE TABLE "test" ("id" INT, "begin" TIMESTAMP, "end" TIMESTAMP); sql>INSERT INTO test ("id", "begin", "end") VALUES (0, '2003-02-07 12:00:00', '2003-02-07 12:00:00'); sql>SELECT id, begin end FROM test; !syntax error, unexpected BEGIN in: "select id, begin" Kind regards, Markus

3 4

[MonetDB-users] Memory Leak in mserver5.exe
by Markus Gritsch 24 Nov '07

24 Nov '07

Hi, I have a medium sized dataset of about 50 MB text data. When I repeatedly call SELECT * FROM entry WHERE LOWER(note) LIKE '%hui%' I get at some time not my result, but the following error in DbVisualizer: protocol violation, unexpected line: VirtualAlloc(1a0a0000,29806592,MEM_COMMIT,PAGE_READWRITE): failed Repeatedly calling the query from the command line like this mclient.exe -lsql -s"SELECT * FROM entry WHERE LOWER(note) LIKE '%hui%'" > result.txt produces the attached memory usage graph. After 11 times calling the query the memory is full, and some kind of garbage collection seems to happen. However, after only 6 additional times the memory is again full and the garbage collection is quite less effective than before. Subsequent calls reach the memory limit every time. Markus P.S.: I have disabled the virtual memory on my Windows XP because when the OS starts paging, it becomes unbearably slow.

2 5

[MonetDB-users] New MonetDB Bug Fix Release
by Sjoerd Mullender 24 Nov '07

24 Nov '07

The MonetDB team at CWI is pleased to announce a new bug fix release of the MonetDB suite of programs. The following packages have been released: MonetDB Clients 1.20.2 MonetDB5 Server 5.2.2 MonetDB SQL 2.20.2 There is of course also a new "Super Source Ball", a tarball containing the combined sources. All this is available on our SourceForge project page. See https://sourceforge.net/project/showfiles.php?group_id=56967 for the downloadable files. See http://monetdb.cwi.nl/projects/monetdb/Development/Releases/Version4.20/ind… for a list of fixes. -- Sjoerd Mullender

1 0