XML

Word

Printable

Details

Type: New Feature
Status: Stalled (View Workflow)
Priority: Critical
Resolution: Unresolved
Fix Version/s: 12.3
Component/s: None
Labels:

Sprint:
Q4/2025 Server Development

Description

GLOBAL TEMPORARY TABLE is a feature found in Oracle, MSSQL, PostgreSQL etc.

Syntax in Oracle:

CREATE GLOBAL TEMPORARY TABLE tmp_table

{ (create_definition, ...)

  | [SELECT * FROM tbl_NAME WHERE conditions ] }

ON COMMIT [DELETE | PRESERVE] ROWS

The purpose of this syntax is to create temporary table where each user can have
their own data, which is destroyed at the end of the session.
Any user with access right to this table can access and use it.

One suggested way to implement this in MariaDB is to have "CREATE GLOBAL TEMPORARY t1" create a 'template' for future temporary tables with name t1.
Internally in MariaDB we would only create a .frm file for the table.
On first access to the table, MariaDB would internally execute
"CREATE TEMPORARY TABLE t1 LIKE t1" on the engine used when "CREATE GLOBAL ..." was created. Any future usage of t1 would use the temporary table t1.
"DROP TABLE t1" would drop the internal temporary table.
To drop the global one, one would have to use "DROP GLOBAL TEMPORARY TABLE".

Existing implementations

ORACLE

ORACLE has GLOBAL TEMPORARY TABLE syntax as part of their normal CREATE TABLE definition.
Both CREATE TABLE …SELECT and CREATE TABLE (create_definition…) work.
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6__I2153132
https://oracle-base.com/articles/misc/temporary-tables

Examples:
https://dbfiddle.uk/O8KdF2kw
CREATE... SELECT: https://dbfiddle.uk/rY75s2Y5, https://dbfiddle.uk/7LipZHrC

TRUNCATE TABLE behavior:
When you truncate a temporary table, only the rows created during the current session are removed

IBM DB2

DB2 supports Declared Global Temporary Tables (DGTT), which are declared with a DECLARE GLOBAL TEMPORARY TABLE statement rather than CREATE …. The data is session-private.
https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-declare-global-temporary-table

MS SQL Server

Uses a concept of local (#table) and global (##table) temporary tables, but these are not the same as the standard’s “global temporary table”. They have their own rules about scoping and session handling.

PostgreSQL

Docs state that GLOBAL keyword has no effect.

However, there is an Oracle compatibility module, pgtt, which adds support.

But:

PostgreSQL temporary table clause ON COMMIT DROP is not supported by the extension, GTT are persistent over transactions.

There is also an ad-hoc trigger based suggestion.

MS SQL

Global temporary tables in MSSQL are created as a normal table with the a prefix of ## for the table name.

Documentation notes

GTTs doc page deserves a replication section.

What should be written there is:

Global temporary tables replicate only DDL statements. Data stored in Global temporary tables is never replicated. That is, DML statements involving Global temporary tables are forced to be replicated in the row mode.GTTs doc page deserves a replication section. What should be written there is:

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

Hide
MariaDBGttAwareConnection.zip
2025-02-11 18:55
9 kB
Patrizio Tamorri
Extracting archive...
Show
MariaDBGttAwareConnection.zip
2025-02-11 18:55
9 kB
Patrizio Tamorri

Issue Links

blocks

MCOL-5642 support querying temporary tables with cross engine joins

Open

causes

MDEV-37368 Assertion failed in close_thread_tables on UPDATE referring bad field

Closed

MDEV-37369 SIGSEGV on NEXTVAL from Global temporary table

Closed

MDEV-37378 SIGSEGV in MDL_ticket::has_stronger_or_equal_type and Assertion `!table->s->tmp_table' failed in wait_while_table_is_used on CREATE TRIGGER

Closed

MDEV-37379 Assertion `index->is_readable()' failed in dict_create_index_tree_in_mem on REPLACE DELAYED

Closed

MDEV-37380 SIGSEGV in thr_upgrade_write_delay_lock and ASAN use-after-poison in Delayed_insert::handle_inserts on INSERT DELAYED

Closed

MDEV-37381 SIGSEGV in mysql_ha_close_table after HANDLER OPEN of Global temporary table

Closed

MDEV-37382 SIGSEGV and UBSAN null-pointer-use in wait_while_table_is_used on CoR GTT

In Testing

MDEV-37383 Crash in end_read_record after REPAIR of Global temporary table

Closed

MDEV-37384 SIGSEGV in mysql_unpack_partition on CREATE GTT ... LIKE partitioned table

Closed

MDEV-37385 Thread hang on CoR where the original table is a GTT

Closed

MDEV-37386 SIGSEGV in close_thread_table, UBSAN null-pointer-use in close_thread_table and Assertion `thd->open_tables == table->table' failed in mysql_create_like_table on CREATE GTT ... LIKE

Closed

MDEV-37394 SIGSEGV in handler::ha_external_lock on CREATE GTT ... ENGINE=INNODB SELECT, ASAN heap-use-after-free in unlock_external

Closed

MDEV-37395 SIGSEGV on CREATE TABLE ... SELECT where source table is a GTT

Closed

MDEV-37576 Assertion `!global_table.versioned()' failed after ALTER TABLE GTT ADD SYSTEM VERSIONING

Closed

MDEV-37577 Assertion `has_temporary_tables() || (rgi_slave && rgi_slave->is_parallel_exec)' failed in THD::open_temporary_table on CREATE TABLE

Closed

MDEV-37578 Assertion failed in TDC_element::flush on ALTER TABLE GTT DISCARD TABLESPACE

Closed

MDEV-37579 use-after-free in MDL_context::release_lock on FLUSH TABLE GTT

Closed

MDEV-37594 Thread hang on TRUNCATE GTT after server_id change

Closed

MDEV-37595 Assertion `thd->transaction->stmt.is_empty()' failed in mysql_ha_open when using mrg_myisam GTT

Closed

MDEV-37596 enforce_storage_engine has an effect on child global temporary tables

Closed

MDEV-37597 InnoDB: Failing assertion: table->get_ref_count() == 0 in dict_sys_t::remove on UPDATE

Closed

MDEV-37612 SIGSEGV in mysql_ha_close_table (opt) and in MDL_ticket::has_pending_conflicting_lock (dbg) after HANDLER OPEN

Closed

MDEV-37656 Thread hang in 'starting' state on FLUSH TABLES and shutdown hang, where the original table is a LOCKED GTT

Closed

MDEV-37657 SIGSEGV in mysql_ha_flush on SHOW CREATE TABLE after HANDLER OPEN GTT

Closed

MDEV-37660 Make Global Temporary Tables DDL errors more specific

Open

MDEV-37666 Global temporary table can be created with system versioning using CREATE LIKE

Closed

MDEV-37667 SIGSEGV in MDL_context::release_lock on ALTER on locked GTT in low memory env

Closed

MDEV-37668 SIGSEGV on DROP TABLE GTT under LOCK TABLES and different server_id

Open

MDEV-37681 SIGSEGV on TRUNCATE GTT after failed RENAME under LOCK TABLE

Closed

MDEV-37690 SIGSEGV and UBSAN null-pointer-use in myrocks::Rdb_transaction_impl::prepare on CREATE TABLE ... AS SELECT

Closed

MDEV-37693 use-after-free in mysql_ha_flush after 2nd HANDLER OPEN execution

Closed

MDEV-37694 ASAN heap-use-after-free in check_column_name on CoR ... LIKE

Closed

MDEV-37700 DROP TABLE on GTT table succeeds in MariaDB only

Closed

MDEV-37718 Assertion '!thd->rgi_slave' failed in open_global_temporary_table on CREATE/ANALYZE GTT

Closed

MDEV-37720 use-after-free on CREATE OR REPLACE GTT under LOCK TABLES and pseudo_slave_mode

Stalled

MDEV-37742 SIGSEGV in Gtid_index_writer::write_current_node on SET GLOBAL binlog_checksum (opt builds)

Closed

MDEV-37793 Behavior of DROP TABLE / DROP DATABASE is inconsistent with GTT

Open

MDEV-37798 Incomplete SAVEPOINT support for InnoDB global temporary tables

Closed

MDEV-37799 Incorrect behavior for DELETE statements on a GTT table

Closed

MDEV-37817 DROP TABLE GTT doesn't succeed for ON COMMIT DELETE ROWS

Closed

MDEV-37850 Wrong error on DROP TABLE GTT after DROP DATABASE

Closed

MDEV-37871 SIGSEGV I_P_List_iterator, UBSAN reference binding to null pointer on DROP DATABASE test

Closed

MDEV-37872 SIGSEGV in MDL_context::release_loc, Assertion `mdl_ticket->m_duration == MDL_TRANSACTION && duration != MDL_TRANSACTION' failed, ASAN heap-use-after-free in I_P_List on CoR

Confirmed

MDEV-37873 Assertion `!memcmp(trx_id->data, reset_trx_id, 6)' failed in btr_cur_optimistic_insert on CREATE GTT

Confirmed

MDEV-37896 MTR Tests main.global_temporary_table and rpl.global_temporary_table are not stable on 2nd run

In Testing

MDEV-37897 Previously fixed MDEV-37690 bug was re-introduced into GTT feature branch

In Testing

MDEV-37898 No GTT MTR coverage on optimized builds due to debug_sync requirement

Confirmed

MDEV-37909 GTT: "errno: 0 "Internal error/check" on CoR ... LIKE under lock

In Testing

MDEV-37928 Assertion !thd->rgi_slave failed on ANALYZE TABLE on replica

In Testing

MDEV-37929 Assertion !thd->rgi_slave' failed on REPAIR TABLE on replica

In Testing

MDEV-37934 Assertion `thd->transaction->stmt.is_empty() || (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)' failed in GRANT on GTT

In Testing

MDEV-37940 Assertion `!thd->rgi_slave' failed on START SLAVE

Confirmed

MDEV-37941 Assertion `!thd->rgi_slave' failed on INSERT w/ parallel slave

Confirmed

MDEV-37956 ASAN heap-use-after-free and SIGSEGV in mysql_ha_close_table on DROP DATABASE

In Testing

MDEV-37957 Assertion `(*tables)->reginfo.lock_type >= TL_READ_SKIP_LOCKED' failed on HANDLER READ

In Testing

MDEV-37958 SIGSEGV and UBSAN invalid-enum-load in ha_mroonga::storage_create_foreign_key on INSERT

In Testing

MDEV-37963 wait_while_table_is_used() variation on MDEV-26805 stack in GTT branch

Closed

MDEV-37968 Assertion `table->s->table_category == TABLE_CATEGORY_SYSTEM || table->s->table_category == TABLE_CATEGORY_STATISTICS' failed in bool open_system_tables_for_read(THD *, TABLE_LIST *)

Open

MDEV-38121 Master GTT overrides the slave table with the same name

Closed

MDEV-38125 Assertion !thd->rgi_slave failed on INSERT under LOCK TABLES

In Testing

MDEV-38151 GTT: missing FUNCTION support

In Testing

MDEV-38152 Spider GTT: table creation succeeds, INSERT fails with ER_ILLEGAL_HA_CREATE_OPTION

Open

MDEV-38266 Infinite loop after LOCK+REPAIR

In Testing

MDEV-38292 If GTT is in use in a replica session, DROP TABLE isn't applied.

Open

is blocked by

MDEV-37348 Logged CREATE TEMPORARY TABLE LIKE from unlogged table fails

Closed

MDEV-37877 Assertion `!mysql_bin_log.is_open()' failed in THD::mark_tmp_table_as_free_for_reuse on UPDATE

Stalled

MDEV-38254 replicated temporary tables must not be accessed by start_new_trans

Stalled

is part of

MDEV-32521 SQL Server compatibility

Open

MDEV-35388 PostgreSQL-compatible syntax

Open

MDEV-35973 Oracle Compatibility Project 1 for 2025

Stalled

relates to

MDEV-37877 Assertion `!mysql_bin_log.is_open()' failed in THD::mark_tmp_table_as_free_for_reuse on UPDATE

Stalled

MDEV-10872 Providing compatibility Oracle database

Open

MDEV-37686 rpl.create_or_replace_mix2 fails in MDEV-35915 branch

Closed

MDEV-37719 use-after-free on logged CREATE OR REPLACE TEMPORARY TABLE LIKE not-logged-table

Stalled

MDEV-37720 use-after-free on CREATE OR REPLACE GTT under LOCK TABLES and pseudo_slave_mode

Stalled

links to

MSSQL T-SQL syntax

(60 causes, 3 is blocked by, 3 is part of, 5 relates to, 1 links to)

Activity

People

Assignee:: Nikita Malyavin

Reporter:: Michael Widenius

Assigned for Implementation:: Nikita Malyavin

Assigned for Review:: Michael Widenius

Assigned for Testing:: Roel Van de Paar

Assigned for Documentation:: Stefan Hinz

Votes:: 1 Vote for this issue

Watchers:: 14 Start watching this issue

Dates

Due:: 2025-06-16

Created:: 2025-01-23 13:42

Updated:: Yesterday 02:01

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.