Temporary tables, standard SQL

The PostgreSQL manual says:

"The standard's definition of the behavior of temporary tables is widely ignored. PostgreSQL's behavior on this point is similar to that of several other SQL databases."

The first sentence is false. The second sentence could be rephrased as "MySQL copied us", although nobody else did, as far as I know.

The standard's definition is widely followed

I base this claim on the documentation of DB2, Oracle Rdb, Oracle 12c, and Firebird.

I don't say the standard is "universally followed" -- for example SQL Server is out of step. I don't say the whole standard is followed -- only the subset related to global temporary tables. I don't say the standard is the only thing that's followed. But all these DBMSs support at least four, and sometimes all six, of these characteristics:

1. There is a syntax CREATE GLOBAL TEMPORARY TABLE <table-name> ... The word GLOBAL means "the scope of the name is global", that is, it's recognized by all modules. Since most DBMSs don't have multiple modules, the word GLOBAL is compulsory and the standard's other type of temporary table, LOCAL, is usually not there. (I will mention, though, parenthetically, that LOCAL could optionally mean a temporary table is local to a stored procedure if it's created in a stored procedure.)

2. If there is a non-temporary table already existing with the same name, then CREATE GLOBAL TEMPORARY TABLE fails.

3. There's a distinction between the metadata -- the definition of the table -- and the data -- the contents of the table.

4. The metadata is persistent; the data disappears when the session ends, or earlier.

5. The metadata is visible to all sessions in INFORMATION_SCHEMA or equivalent. The data is not visible to all sessions, and one sometimes calls the temporary table's contents "session-specific data".

6. There is an optional clause ON COMMIT {PRESERVE|DELETE} ROWS. When ON COMMIT DELETE ROWS is specified or is default, then the data disappears when COMMIT happens.

PostgreSQL's behaviour is copied by MySQL

For PostgreSQL, the CREATE TEMPORARY TABLE statement arrived in PostgreSQL 6.5 which was released in June 1999. For MySQL, CREATE TEMPORARY TABLE arrived in MySQL 3.23 in August 1999.

That was before my time, but I'm a post-hoc-ergo-propter-hoc kind of guy, and I suspect that somebody was seduced by the logical fallacy that PostgreSQL has some standard SQL, therefore copy it. So the thing that's presently in MySQL and MariaDB is CREATE TEMPORARY TABLE table_name ...;

But these DBMSs support zero (0) of the six characteristics that I described for the standard-compliant DBMSs.

1. The specification of scope is missing.

2. If there is a non-temporary table already existing with the same name, then CREATE TEMPORARY TABLE succeeds, and the existing table is hidden.

3. There's no distinction between the metadata -- the definition of the table -- and the data -- the contents of the table.

4. The metadata is not persistent; the data disappears when the session ends, or earlier.

5. The metadata is not visible to all sessions in INFORMATION_SCHEMA or equivalent ... Yes, I'm refusing to acknowledge the existence of the new table INNODB_TEMP_TABLE_INFO, for quality reasons.

6. There is no optional clause ON COMMIT {PRESERVE|DELETE} ROWS.

One funny result of this is that simple statements like SELECT * FROM table_name; can't be fully checked or optimized at the time they're checked within a stored procedure, because there's no way to know what table_name is -- maybe at execution time it will be the table that's defined and visible in information_schema, but maybe it will be a temporary table which is only temporarily visible.

A less serious but irritating result is that, if you want to use a temporary table repeatedly, you have to re-create everything -- find the original table definition somewhere, then add whatever indexes or views or triggers applied originally.

Is there any good news?

I wrote a worklog task about this long ago. It was WL#934 "Temporary tables in the standard way". But it is a private Oracle spec, and is not in the Internet archive, so the general public can't see it. There's been no action on it that I know of. Some attention has been paid to feature request #58392 "Prevent TEMPORARY tables from shadowing regular tables" and feature request #20001 "Support for temp-tables in INFORMATION_SCHEMA", but those are mere distractions.

However, it remains possible to implement standard global temporary tables without disrupting existing applications. The key is the syntax difference between

CREATE TEMPORARY TABLE ...;                    /* MySQL or MariaDB */

and

CREATE GLOBAL TEMPORARY TABLE ...;             /* standard */

They're two different statements. So one could just say that "if and only if GLOBAL is specified, then the table has to have the standard rules".

This isn't due to luck. It happened because in olden days MySQL's architects paid attention to the official ANSI/ISO framework requirements:

"In the Syntax Rules, the word shall defines conditions that are required to be true of syntactically-conforming SQL language ... The treatment of language that does not conform to the SQL Formats and Syntax Rules is implementation-dependent."

In other words, by allowing only the non-standard syntax CREATE TEMPORARY TABLE, MySQL is not violating the standard. On the other hand, the latest PostgreSQL release allows CREATE GLOBAL TEMPORARY TABLE, so PostgreSQL is violating the standard. To people who read my earlier blog post "Sometimes MySQL is more standards-compliant than PostgreSQL", that won't come as a shock.

, November 2, 2014. Category: MySQL / MariaDB, Standard SQL.

About pgulutzan

Co-author of four computer books. Software Architect at MySQL/Sun/Oracle from 2003-2011, and at HP for a little while after that. Currently with Ocelot Computer Services Inc. in Edmonton Canada.