CREATE TEMPORARY TABLE WITH DATA seems not working - Jan2014-SP3
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?
Regards,
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 snaga@uptime.jp _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
Hi Niels,
On 2014/11/16 20:09, Niels Nes wrote:
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
Yes, that's it. I ran CREATE TEMPORARY TABLE under autocommit on, and using "ON COMMIT PRESERVE ROWS" works fine as I expected. Thanks!
Regards,
participants (2)
-
Niels Nes -
Satoshi Nagayasu