[Monetdb-developers] SQL temporary table not working?

14 Sep 2007

      Hi,

I am having problems with SQL TEMPORARY TABLES: even when I insert  
rows into them, they remain empty. See for example:

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

Agustin Schapira

Niels Nes

tags

participants (2)