Not possible to create a temporary table without using "SELECT * FROM {existing_table}"

Problem/Motivation

TLDR: Cannot make $this->database->queryTemporary() work with a table structure definition (such as "zip INT, distance INT"), only with a select from an existing table.

Making a View where i am bringing in external data to use for filtering and sorting, and the only way i can find to make that possible is with a hook_views_query_alter() and then creating a temporary table for joining to the existing Drupal data.

Steps to reproduce

Try:

  $this->database->queryTemporary("zip VARCHAR(10), distance VARCHAR(10)");

or any variation of that, at least with MySQL/MariaDB.

Proposed resolution

It seems likely that #2409243: MySQL Temporary tables currently hard code memory Storage Engine might provide a workaround, as with drush sqlc i get a similar error unless i remove the "Engine=MEMORY" from the query.

MariaDB [db]> CREATE TEMPORARY TABLE test_temp_table Engine=MEMORY (zip char(10) PRIMARY KEY, distance float);

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'char(10) PRIMARY KEY, distance float' at line 1


MariaDB [db]> CREATE TEMPORARY TABLE test_temp_table (zip char(10) PRIMARY KEY, distance float);

Query OK, 0 rows affected (0.017 sec)

The workaround i am going to try is to create a permanent empty table so i can create my structure using the "SELECT * FROM" approach but that is really silly.

Remaining tasks

User interface changes

Introduced terminology

API changes

Data model changes

Release notes snippet