Re: CREATE TEMPORARY TABLE WITH DATA seems not working - Jan2014-SP3

On Sun, Nov 16, 2014 at 02:14:53PM +0900, Satoshi Nagayasu wrote:

Hi all,

As shown below, despite CREATE TABLE WITH DATA works fine, CREATE TEMPORARY TABLE WITH DATA is not able to create a temp table with data.

--------------------------------------------------------- $ mclient mydb Welcome to mclient, the MonetDB/SQL interactive terminal (Jan2014-SP3) Database: MonetDB v11.17.21 (Jan2014-SP3), 'mapi:monetdb://localhost.localdomain:50000/mydb' Type \q to quit, \? for a list of available commands auto commit mode: on sql>CREATE TABLE t2 AS SELECT 1 WITH DATA; operation successful (1.143ms) sql>SELECT * FROM t2; +------+ | v | +======+ | 1 | +------+ 1 tuple (0.453ms) sql>CREATE TEMPORARY TABLE t AS SELECT 1 WITH DATA; operation successful (0.985ms) sql>SELECT * FROM t; +---+ | v | +===+ +---+ 0 tuples (0.439ms) sql> ---------------------------------------------------------

Is this a bug? or something I misunderstand? Are you using auto commit? Then the temporary table is probably emptied at the end of your transacion. SQL standard's default for temporary tables defines an commit action 'DELETE ROWS', where you probably want 'ON COMMIT PRESERVE ROWS'

Niels

Regards,

-- NAGAYASU Satoshi _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list

-- Niels Nes, Centrum Wiskunde & Informatica (CWI) Science Park 123, 1098 XG Amsterdam, The Netherlands room L3.14, phone ++31 20 592-4098 sip:4098@sip.cwi.nl url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl