`SHOW CREATE TABLE` doesn't include `TEMPORARY` for temporary tables

[fulghum]

Dolt Repro:

create temporary table t (pk int primary key);
show create table t;
+-------+------------------------------------------------------------------+
| Table | Create Table                                                     |
+-------+------------------------------------------------------------------+
| t     | CREATE TABLE `t` (                                               |
|       |   `pk` int NOT NULL,                                             |
|       |   PRIMARY KEY (`pk`)                                             |
|       | ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
+-------+------------------------------------------------------------------+

MySQL Behavior:

create temporary table t (pk int primary key);
show create table t;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                              |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TEMPORARY TABLE `t` (
  `pk` int NOT NULL,
  PRIMARY KEY (`pk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------+

[HeavyBR]

Hi @fulghum, I would like to tackle this one. I'm an user of Dolt but I don't have experience with the codebase, any suggestions of where to look first? 🥺

[nicktobey]

Hi @HeavyBR!

We're so pumped to hear you'd like to contribute.

This is an issue with the SQL engine, so the change will most likely need to happen in the https://github.com/dolthub/go-mysql-server/ repo.

The way the engine works is by creating an Abstract Syntax Tree that contains all the relevant information for the query, and then converting that into a tree of iterators that produce the results of the query.

sql/rowexec/show_iters.go has the iterator for the show command. The output of the command is a single table row, and showCreateTablesIter.Next function is responsible for generating that row.

In the event that showCreateTablesIter doesn't actually contain enough information to know whether the table is temporary, you may also need to make changes to the corresponding AST node, which is in sql/plan/show_create_table.go. And the function that instantiates the iterator from the node is BaseBuilder.buildShowCreateTable, found in sql/rowexec/show.go.

Hopefully this points you in the right direction. Let us know if you have any questions.

Thank you so much for the contribution!