November 2005 - developers-list

[Monetdb-developers] 2nd MonetDB-Bug-Day: Wed, Nov 9 2005 (next week)
by Stefan Manegold 20 Dec '05

20 Dec '05

(some of you might already have see this one coming; cf. http://www.cwi.nl/htbin/cwwwi/agenda?datum=2005_11+dag=09 ;-)) Dear all, given the success of the 1st MonetDB-Bug-Day on Oct 06 2005, 8 participants managed to check 347 of the then 721 bug reports, added 97 new test scripts, re-opened some bugs that still/again fail and filed 9 new bug reports; see https://sourceforge.net/mailarchive/forum.php?thread_id=8403629&forum_id=39… for details, there will by a 2nd MonetDB-Bug-Day to (hopefully) check the remaining 347 bug reports (plus those filed since Oct 06 2005), and produce proper test scripts for our automatic Nightly Multi-Platform Regression Test System (aka "TestWeb"; cf. http://monetdb.cwi.nl/Development/TestWeb/) When: Wednesday, Nov 9, 2005, "all day" (or as much time as you can spent) Where: Mainly at CWI, but you're also welcome to join "virtually" working from home or any other place in the world; for those who (again) want to turn the Bug-Day into a Bug-"Party", I reserved our meeting room (C0,01) from 8:00 - 23:00 --- you need to bring your own "equipment" though ... Who: Primarily everyone who is in touch with MonetDB, and/or one of its companions/front ends (SQL, XQuery, etc.), has rights to check-in to the MonetDB CVS repository on SourceForge, and is reasonably familiar with "Mtest.py" (cf. http://monetdb.cwi.nl/monet/src/testing/README) What: I will prepare a list of all bugs that have no test script, yet, and chop this in chunks, and assign each chunk to one participant so that [s]he can check them, and prepare the respective test scripts. More detailed instructions can be found at https://sourceforge.net/mailarchive/forum.php?thread_id=8403629&forum_id=39… and/or will follow before next Wednesday. Note: Though any help is appreciated, no one is "obliged" to help. Especially, there is no need to spent the whole day (though nine will complain if you do ;-)), anyone can join-in and leave whenever [s]he wants. For those of you participating at CWI, there will be free snacks and soft-drinks during the day (for the time you're "active"; as long as supplies lasts). Furthermore, there will be "real" drinks at the end of the Bug-Day (for participants, only; as long as supplies lasts; (maximum) amount per person will be proportional to the number of test scripts added). I hope to see especially (though not only) participants that I haven't seen on the 1st MonetDB-Bug-Day ;-) Once again thank you very much for your cooperation! CU next Wednesday, Stefan ps: Testing & the TestWeb are not (only?) my "toys"; it's "serious business", and helps all of you to (1) get a stable and reliable MonetDB, (2) monitor when your own bug reports get fixed, and (3) prevent the bugs you once thankfully detected, reported and/or fixed from occurring again! -- | 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 |

8 53

[Monetdb-developers] JDBC/MonetDB performance test
by Fabian Groffen 18 Dec '05

18 Dec '05

Inspired by a student's preliminary test program, I conducted a -- at first quite innocent -- experiment to see how 'fast' MonetDB is and it's upcoming major release; MonetDB/Five. The conducted experiment mainly consists of inserting 8000, 19200 and 152800 tuples into three tables respectively. There are no constraints in use, as such it is quite 'dumb', but aggressive inserting. This inserting of values was done in three different ways: 1) using plain INSERT INTO statements (DBTestI) 2) using INSERT INTO statements batched by the JDBC driver (DBTest) [1] 3) using PreparedStatements to insert the date (DBTestP) I timed the wall-clock times of PostgreSQL 8.0.3 (PG8), MySQL 4.0.25 with InnoDB (My4), MonetDB 4.8.2 (M4) and todays CVS version of MonetDB/Five (M5). The JDBC drivers used for PostgreSQL and MySQL were the latest stable versions available, for MonetDB the released version and a progressive/experimental version were tested. This is marked with an 's' for the stable released version and 'p' for the progressive version. The databases run on a Dual Athlon MP 1600+ with 1GB of main-memory running Gentoo Linux with a 2.6.12 kernel (pegatoo). Two series of tests were performed; a local and remote version. In the local variant both the client application (with JDBC driver) and database server run on the same machine (as mentioned before). In the remote variant the client is on a different machine that is hardware wise equal to the machine that runs the database (crux). Both machines are directly connected to each other. The results follow in the table below: PG8 My4 M4s M4p M5s M5p JDBC local - DBTestI 37s 27s 90s 86s 62s 60s - DBTest 19s 27s 60s 59s 46s 46s - DBTestP 14s 27s 63s 64s 59s 60s JDBC remote - DBTestI 49s 37s 84s 85s 62s 62s - DBTest 19s 37s 57s 58s 43s 43s - DBTestP 14s 37s 66s 62s 53s 53s JDBC remote [2] - DBTestI 197s 168s >1201s >1545s - DBTest 67s 168s 76s 77s 61s 63s - DBTestP 68s 168s 75s 77s 66s 70s The results are somewhat 'confusing'. [1] Note: the program was not called DBTestB because it was submitted by the students under this name. [2] To simulate a slow link, we used an ssh tunnel to the database server as connection link, instead of a direct connection

2 4

[Monetdb-developers] MonetDB Bug-Etiquette
by Stefan Manegold 24 Nov '05

24 Nov '05

Dear MonetDB-users and -developers, bug reports and especially test script play a crucial role with maintaining and improving the stability and correct functioning of MonetDB. However, adding test scripts "a posteriori" as we did recently during the two "MonetDB-Bug-Days" (a third one is still to come before the end of this year!) is a very tedious and time-consuming task, especially since who ever is adding a test script first needs to get familiar with the details of the very bug report. Since both the user who submits a bug report and the developer(s) who take(s) care of fixing a bug are by definition very familiar with the details of a bug report, it seems more than obvious that they should share the task of adding a test script to the CVS repository while dealing with the respective bug, i.e., *before* closing the bug report. Ideally, the user/submitter provides the query or script that triggers the bug as well as the expected/correct output with his/her bug report. In case the user/submitter has CVS check-in privileges, he/she could add the test script to the CVS repository him-/herself. Otherwise, the developer/assignee should add the user's test script to the CVS repository. The test script helps both the developer/assignee and the user/submitter to monitor the status of the bug by running "Mtest[.py]" (cf., http://monetdb.cwi.nl/monet/src/testing/README) or by checking the TestWeb (http://monetdb.cwi.nl/Development/TestWeb/) To coordinate this procedure, I made a first draft for a "MonetDB Bug Etiquette", which you find attached to this mail. I'd be very pleased, if you could comment on this. Thank you all very much for your contribution to make MonetDB even better than it already is! Kind regards, 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 |

4 10

[Monetdb-developers] SQL: Snodgrass' examples
by Steffen Goeldner 21 Nov '05

21 Nov '05

Attached is a SQL script with a collection of examples from 'Developing time-oriented database applications in SQL', chapter 3. <http://www.cs.arizona.edu/people/rts/tdbbook.pdf> I find this book very useful because the SQL standard is a very dry read. I changed a few errata, which are already confirmed by Mr. Snodgrass. It would give me great pleasure if the examples are of any use for you (e.g. as test script, quick reference, todo list ;-) Steffen /* Examples from Developing time-oriented database applications in SQL Richard T. Snodgrass ISBN 1-55860-436-7 Morgan Kaufmann Publishers <http://www.cs.arizona.edu/people/rts/tdbbook.pdf> Chapter 3: Instants and Intervals ---------------------------------------------------------------------- Boolean results should be true! ROLLBACK: expected error (not always thrown by MonetDB) rollback: MonetDB error */ SELECT DATE '1997-01-12'; SELECT TIME '11:35:29'; SELECT TIME '00:00:00'; SELECT TIME '11:08:27-07:00'; SELECT TIME '11:08:27.123456-07:00'; SELECT TIMESTAMP '1997-01-15 11:35:29.123456'; SELECT TIMESTAMP '1997-01-15 11:35:29.123456-07:00'; SELECT INTERVAL '3' YEAR; SELECT INTERVAL '7' MONTH; SELECT INTERVAL '3-7' YEAR TO MONTH; SELECT INTERVAL -'3-7' YEAR TO MONTH; SELECT INTERVAL +'0-0' YEAR TO MONTH; SELECT INTERVAL -'0-0' YEAR TO MONTH; SELECT INTERVAL '1 23:45:12' DAY TO SECOND; SELECT INTERVAL '0-0' YEAR TO MONTH = INTERVAL +'0-0' YEAR TO MONTH; SELECT INTERVAL '0-0' YEAR TO MONTH = INTERVAL -'0-0' YEAR TO MONTH; SELECT INTERVAL '3-4' YEAR TO MONTH = INTERVAL +'3-4' YEAR TO MONTH; SELECT INTERVAL '3-4' YEAR TO MONTH = INTERVAL '+3-4' YEAR TO MONTH; SELECT INTERVAL '3-4' YEAR TO MONTH = INTERVAL +'+3-4' YEAR TO MONTH; SELECT INTERVAL '3-4' YEAR TO MONTH = INTERVAL -'-3-4' YEAR TO MONTH; SELECT INTERVAL '3-4' YEAR TO MONTH = INTERVAL '3-4' YEAR TO MONTH; SELECT INTERVAL '3-7' YEAR TO MONTH = INTERVAL '43' MONTH; SELECT INTERVAL '3-7' YEAR TO MONTH = INTERVAL '23' DAY; -- incomparable ROLLBACK; -- `< ', ` < = ', `> ', `>= ', and`<> SELECT DATE '1996-02-24' + INTERVAL '7' DAY = DATE '1996-03-02'; SELECT DATE '1996-02-24' + INTERVAL '12:30' HOUR TO MINUTE; -- disallowed ROLLBACK; SELECT DATE '1996-02-24' + INTERVAL '2 12' DAY TO HOUR; -- disallowed ROLLBACK; SELECT INTERVAL '7' DAY + DATE '1996-02-24' = DATE '1996-03-02'; SELECT DATE '1996-03-02' - INTERVAL '7' DAY = DATE '1996-02-24'; SELECT TIMESTAMP '1996-02-24 12:34:56' AT LOCAL; -- MST: TIMESTAMP '1996-02-24 19:34:56' rollback; SELECT TIMESTAMP '1996-02-24 12:34:56+02:00' AT LOCAL; -- (MET DST) MST: TIMESTAMP '1996-02-24 03:34:56' rollback; SELECT TIMESTAMP '1996-02-24 12:34:56' AT TIME ZONE INTERVAL '-7:00' HOUR TO MINUTE = TIMESTAMP '1996-02-24 19:34:56'; rollback; SELECT CURRENT_DATE; SELECT CURRENT_TIME; SELECT CURRENT_TIMESTAMP; -- simultaneously SELECT CAST('1996-02-24' AS DATE) = DATE '1996-02-24'; SELECT CAST('12:34:56' AS TIME) = TIME '12:34:56'; SELECT CAST(TIME '12:34:56' AS TIMESTAMP WITH TIME ZONE) = TIMESTAMP '1997-07-23 12:34:56-07:00'; rollback; SELECT CAST( TIME '12:34:56.123' AS TIME(6) ) = TIME '12:34:56.123000'; SELECT CAST( TIME '12:34:56.123' AS TIME(1) ) = TIME '12:34:56.1'; SELECT CAST( TIMESTAMP '1997-07-23 12:34:56.123' AS TIME(6) ) = TIME '12:34:56.123000'; rollback; SELECT CAST( TIMESTAMP '1997-07-23 12:34:56.123' AS DATE ) = DATE '1997-07-23'; SELECT CAST( DATE '1997-01-01' AS TIMESTAMP(4)) = '1997-01-01 00:00:00.0000'; rollback; SELECT INTERVAL '3' DAY + INTERVAL '4' DAY = INTERVAL '7' DAY; SELECT INTERVAL '3' DAY + INTERVAL '4' HOUR = INTERVAL '3 4' DAY TO HOUR; SELECT INTERVAL '3' DAY + INTERVAL '8 4' DAY TO HOUR = INTERVAL '11 4' DAY TO HOUR; SELECT INTERVAL '3' DAY - INTERVAL '4' DAY = INTERVAL -'1' DAY; SELECT INTERVAL '3' DAY - INTERVAL -'8 4' DAY TO HOUR = INTERVAL '11 4' DAY TO HOUR; SELECT ( DATE '1997-01-01' - DATE '1996-01-01') DAY = INTERVAL '366' DAY; rollback; SELECT ( DATE '1997-01-01' - DATE '1996-01-01') YEAR TO MONTH = INTERVAL '1-0' YEAR TO MONTH; rollback; SELECT INTERVAL '4' DAY * 3 = INTERVAL '12' DAY; SELECT 3 * INTERVAL '4' DAY = INTERVAL '12' DAY; SELECT INTERVAL '12:30' HOUR TO MINUTE * 3 = INTERVAL '37:30' HOUR TO MINUTE; SELECT INTERVAL '4' DAY / 2 = INTERVAL '2' DAY; SELECT 2 / INTERVAL '4' DAY; -- not permitted ROLLBACK; SELECT - INTERVAL '4' DAY = INTERVAL -'4' DAY; SELECT + INTERVAL '4' DAY = INTERVAL '4' DAY; SELECT CAST('2' AS INTERVAL MONTH ); SELECT CAST('3-7' AS INTERVAL MONTH ); -- wrong ROLLBACK; SELECT CAST(INTERVAL '8-7' YEAR TO MONTH AS INTERVAL MONTH(2) ); -- exception is raised ROLLBACK; SELECT CAST( INTERVAL '3' YEAR AS INTERVAL YEAR TO MONTH ) = INTERVAL '3-0' YEAR TO MONTH; SELECT CAST( 103 AS INTERVAL MONTH ) = INTERVAL '103' MONTH; SELECT CAST( 103 AS INTERVAL MONTH(2) ); -- overflow exception ROLLBACK; SELECT CAST('2 12:34' AS INTERVAL DAY TO MINUTE ); SELECT CAST('12:34' AS INTERVAL DAY TO MINUTE ); -- works not ROLLBACK; SELECT CAST( INTERVAL'86 00:00:00' DAY TO SECOND AS INTERVAL HOUR TO SECOND ) = INTERVAL '2064:00:00' HOUR TO SECOND; SELECT CAST('86 00:00:00' AS INTERVAL HOUR(3) TO SECOND ); -- overflow exception ROLLBACK; SELECT CAST( CAST( 7430400 AS INTERVAL SECOND ) AS INTERVAL DAY TO SECOND ) = INTERVAL '86 00:00:00' DAY TO SECOND; SELECT CAST(DATE '1997-01-01' AS CHARACTER) = '1997-01-01'; SELECT CAST(INTERVAL '7430400' SECOND AS INTEGER) = 7430400; SELECT CAST(CAST(INTERVAL '2064:00:00' HOUR TO SECOND AS INTERVAL SECOND) AS INTEGER) = 7430400; SELECT EXTRACT( YEAR FROM DATE '1970-01-01') = 1970; SELECT EXTRACT( MINUTE FROM INTERVAL '12:34:56' HOUR TO SECOND ) = 34; SELECT EXTRACT( TIMEZONE_HOUR FROM TIME '12:34:56-07:00') = -7; rollback; SELECT EXTRACT( TIMEZONE_MINUTE FROM TIME '12:34:56-07:00') = 0; rollback; CREATE TABLE Employee( Id INTEGER, BirthDate DATE ); INSERT INTO Employee VALUES( 77, '1970-01-01'); INSERT INTO Employee VALUES( 88, '1971-12-13'); COMMIT; SELECT * FROM Employee WHERE BirthDate = DATE '1970-01-01'; SELECT * FROM Employee WHERE BirthDate =ANY ( VALUES( ( DATE '1970-01-01') ) ); rollback; SELECT * FROM Employee WHERE BirthDate =ALL ( VALUES( ( DATE '1970-01-01') ) ); rollback; SELECT * FROM Employee WHERE BirthDate =SOME ( VALUES( ( DATE '1970-01-01') ) ); rollback; SELECT * FROM Employee WHERE BirthDate IN ( VALUES( ( DATE '1970-01-01') ) ); rollback; SELECT * FROM Employee WHERE NOT BirthDate NOT IN ( VALUES( ( DATE '1970-01-01') ) ); rollback; SELECT * FROM Employee WHERE BirthDate MATCH ( VALUES( ( DATE '1970-01-01') ) ); rollback; SELECT * FROM Employee WHERE NOT BirthDate <> DATE '1970-01-01'; rollback; SELECT * FROM Employee WHERE NOT BirthDate <>ANY ( VALUES( ( DATE '1970-01-01') ) ); rollback; SELECT * FROM Employee WHERE NOT BirthDate <>ALL ( VALUES( ( DATE '1970-01-01') ) ); rollback; SELECT * FROM Employee WHERE NOT BirthDate <>SOME ( VALUES( ( DATE '1970-01-01') ) ); rollback; SELECT * FROM Employee WHERE BirthDate BETWEEN DATE '1970-01-01' AND DATE '1970-01-01'; SELECT * FROM Employee WHERE NOT BirthDate NOT BETWEEN DATE '1970-01-01' AND DATE '1970-01-01'; rollback; SELECT * FROM Employee WHERE ( BirthDate, INTERVAL '0' DAY ) OVERLAPS ( DATE '1970-01-01', INTERVAL '0' DAY ); rollback; SELECT * FROM Employee WHERE ( BirthDate, BirthDate ) OVERLAPS ( DATE '1970-01-01', INTERVAL '0' DAY ); rollback; SELECT * FROM Employee WHERE ( BirthDate, INTERVAL '0' DAY ) OVERLAPS ( DATE '1970-01-01', DATE '1970-01-01'); rollback; SELECT * FROM Employee WHERE ( BirthDate, BirthDate ) OVERLAPS ( DATE '1970-01-01', DATE '1970-01-01'); rollback; SELECT * FROM Employee WHERE ( BirthDate, NULL ) OVERLAPS ( DATE '1970-01-01', INTERVAL '0' DAY ); rollback; SELECT * FROM Employee WHERE ( BirthDate, NULL ) OVERLAPS ( DATE '1970-01-01', NULL ); rollback; SELECT * FROM Employee WHERE ( BirthDate, NULL ) OVERLAPS ( NULL , DATE '1970-01-01'); rollback; SELECT * FROM Employee WHERE CAST( BirthDate AS CHAR ) = '1970-01-01'; SELECT * FROM Employee WHERE CAST( BirthDate AS CHAR ) LIKE '1970-01-01'; SELECT * FROM Employee WHERE CAST( ( DATE '1971-01-01' - BirthDate ) DAY AS INT ) = 365 AND CAST( ( DATE '1971-01-01' - BirthDate ) YEAR AS INT ) = 1; rollback; -- XXX: SELECT * FROM Employee WHERE EXTRACT( YEAR FROM BirthDate ) = 1970 AND EXTRACT( MONTH FROM BirthDate ) = 1 AND EXTRACT( DAY FROM BirthDate ) = 1; DROP TABLE Employee; COMMIT;

2 2

[Monetdb-developers] Re: [MonetDB-users] AMD64 version of MonetDB for Windows?
by Peter Boncz 15 Nov '05

15 Nov '05

Hi Fabian, I just got my new workstation and it is an AMD64 machine with windows64 installed. I also got myself both the microsoft 64 bits compiler as well as the intel 64 bits compiler. With the particular goal of course to port MonetDB. I am not promising any timeframe but if we don't have a native windows64 port in the next couple of months I would be very surprised if not disappointed. Peter >> >> >> >> Just a small note. I don't have any windows building experiences, but >> we currently don't have access to such box running that operating >> system. As far as I can tell, there is no real priority to do so >> either. There are build instructions for windows, but it is unknown how >> 64-bits are enabled and if this will compile and run. This seems an >> unexplored area to me. >> >> Regards, >> Fabian >> >> >> Cardillo Raymond A Civ AFRL/IFEA wrote: >> > Has anyone out there taken the time to build an MSI for the AMD64 versions >> > of Windows? The x64 version of Windows was released a few months ago and >> > was available in Beta for a while, so I'm hoping someone has either done >> > this already, or can provide some advice on how to build/compile for this >> > platform. Anyone know of future plans to make this one of the standard >> > platform builds? Windows x64 is only going to grow in popularity from here, >> > especially amongst researchers and others who can benefit from 64 bits. >> > >> > Thanks for any info, >> > Ray Cardillo >> >> >> >> >>

2 1

[Monetdb-developers] MonetDB::CLI: MapiLib alternatives
by Steffen Goeldner 15 Nov '05

15 Nov '05

I'd like to add a more perlish layer around MapiLib (working title: MonetDB::CLI). This may look like: my $Cxn = MonetDB::CLI->connect( ... ); my $Req = $Cxn->query('select * from env'); while ( my $Cnt = $Req->fetch_row ) { print $Req->fetch_field( $_ ) for 0 .. $Cnt-1; } Still much in the spirit of Mapi, but handles are now objects (with destructors), functions are methods and the methods can throw exceptions. Once we have such an interface, additional implementations are possible. For instance, a XS module could skip the MapiLib layer and use libMapi directly. Later on, an implementation for MCL will be added (I hope) - either in pure perl or on top of libMCL (in case such a library will emerge). Of course, I'd like to use this new interface from DBD::monetdb to be able to choose between various implementations and to ease the transition to MCL. On the downside: additional modules means additional installation efforts. Especially the people resposible for the RPM's will be affected. If this sounds too screwy, it's now the chance to stop me ;-) Steffen

3 5