Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

schema name qualifier in create global temporary table json.table6643 ... is not honered #6643

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels

Comments

@monetdb-team
Copy link

@monetdb-team monetdb-team commented Nov 30, 2020

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.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
1 participant