On Fri, Sep 14, 2007 at 11:11:37AM -0400, Agustin Schapira wrote:
Hi,
I am having problems with SQL TEMPORARY TABLES: even when I insert rows into them, they remain empty. See for example:
Isn't sql great. The auto commit simply cleared your data. So or have one transaction with start transaction / commit or add 'ON COMMIT PRESERVE ROWS' to your create statement. Niels
monetdb-> create local temporary table d (id int); Operation successful
monetdb-> insert into d values(1); 1 affected row
monetdb-> select * from d; +--------+ | id | +========+ +--------+ 0 rows
If I create a TEMPORARY TABLE from a SELECT ... WITH DATA statement, it gets the correct structure but no data. Also, if I later insert rows into the temporary table, it still remains empty. Here's a sample session that demonstrates the problem:
monetdb-> create table a (id int); Operation successful
monetdb-> insert into a values(1); 1 affected row
monetdb-> select * from a; +--------+ | id | +========+ | 1 | +--------+ 1 row
monetdb-> create temporary table b as select * from a with data; Operation successful
monetdb-> select * from b; +--------+ | id | +========+ +--------+ 0 rows
monetdb-> insert into b values(5); 1 affected row
monetdb-> select * from b; +--------+ | id | +========+ +--------+ 0 rows
monetdb-> insert into b select * from a; 1 affected row
monetdb-> select * from b; +--------+ | id | +========+ +--------+ 0 rows
This happens with the latest release, compiled yesterday with the -- nightly='stable' switch:
# MonetDB Server v5.0.1 # Copyright (c) 1993-2007 CWI, all rights reserved # Compiled for i686-apple-darwin8.10.1/32bit with 32bit OIDs dynamically linked Database: MonetDB 1.18.3 Driver: MonetDB Native Driver 1.6 (Steadfast/MCL-1.0 20070913) auto commit mode: on
By the way, this used to work with MonetDB Server v5.0.0_beta1_2 (although the WITH DATA option didn't load the data: I had to create the structure and then INSERT into the table to get the rows).
Any ideas? Am I doing something wrong? Are TEMPORARY TABLES not supposed to be used like this?
Thanks for your help,
-- Agustin
------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Monetdb-developers mailing list Monetdb-developers@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-developers
-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl