Date: 2018-09-13 18:54:31 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.31.7 (Aug2018)

Last updated: 2019-01-14 17:29:13 +0100

Comment 26616

Date: 2018-09-13 18:54:31 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Fedora; Linux x86_64; rv:62.0) Gecko/20100101 Firefox/62.0
Build Identifier:

When creating a global temporary table with a fully qualified name such as json.table6643 the command is accepted but the table is not created in the designated schema. Instead it is ignored and silently created in the tmp schema.
This is confusing for the user.
For global temp tables it is desirable to be placed in a specific schema, as it can be shared with other users. Also it prevents name clashes with local temp tables of other users.

Reproducible: Always

Steps to Reproduce:

CREATE LOCAL TEMP TABLE json.table6643a (id int NOT NULL PRIMARY KEY, name VARCHAR(99) NOT NULL) ON COMMIT PRESERVE ROWS;
-- fails with error: CREATE TABLE: local temporary tables should be stored in the 'tmp' schema
CREATE GLOBAL TEMP TABLE json.table6643b (id int NOT NULL PRIMARY KEY, name VARCHAR(99) NOT NULL) ON COMMIT PRESERVE ROWS;
-- this is accepted without an error
SELECT * FROM json.table6643b;
-- this fails with error: SELECT: no such table 'table6643b
SELECT * FROM sys.table6643b;

-- this fails with error: SELECT: no such table 'table6643b
SELECT * FROM table6643b;
-- this succeeds
SELECT * FROM tmp.table6643b;
-- this succeeds

Expected Results:

When a fully qualified name is given for a create global temporary table, the schema name should be honered, not silently changed into tmp.

Note that global temp tables are stored in system table sys._tables, and not in tmp._tables!
Local temp tables are created in tmp schema only and are recorded in system table tmp._tables.

Comment 26709

Date: 2018-12-03 17:02:26 +0100
From: MonetDB Mercurial Repository <>

Changeset 3fd88283c3b4 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=3fd88283c3b4

Changeset description:

Fix for bug #6643 (i.e. allow creation of global temporary tables ony on 'tmp' schema).

Please keep order of BugTracker tests by number.