[Monetdb-developers] SQL temporary table not working?

Niels Nes Niels.Nes at cwi.nl
Fri Sep 14 17:29:58 CEST 2007


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 at 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 at cwi.nl




More information about the developers-list mailing list