Bug #36271 ALTER TABLE ... RENAME [OVERWRITE] [TO] to overwrite existing table

• Submitted: 23 Apr 2008 3:06
• Modified: 15 May 2008 13:39
• Reporter: Ondra Zizka
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any
• Tags: ALTER TABLE, DDL, Drop, overwrite, rename, stored procedures

[23 Apr 2008 3:06] Ondra Zizka

Description:
Since MySQL does not support passing result sets in stored procedures, common practice is to use TEMPORARY TABES when working with larger data sets.

To create generally usable API, one needs to define the names of temp tables with input and output data. Thus, passing temp tables between different procedures needs to rename them. To make it safely, one has to try the drop before altering:

How to repeat:
CALL PrepareSomeData(...);  -- This creates TEMP TABLE prepared_data
DROP TEMPORARY TABLE IF EXISTS input_data;
ALTER TABLE prepared_data RENAME TO input_data;
CALL ProcessData(...);      -- This procedure looks for the input_data table.

And this has to be done many times.

Suggested fix:
My suggestion is to introduce the OVERWRITE option to ALTER TABLE:

CALL PrepareSomeData(...);  -- This creates TEMP TABLE prepared_data
ALTER TABLE prepared_data RENAME OVERWRITE TO input_data;
CALL ProcessData(...);      -- This procedure looks for the input_data table.

The alter with OVERWRITE would check whether a table with such name exists, and if  so, it would drop it first.

(Of course, better solution of the problem itself would be a possibility to pass result sets in variables, but as I got to know, that is planned in far future.)

Thanks for considering.

[15 May 2008 13:39] Susanne Ebrecht

Many thanks for writing a feature request. I can see lots of issues where a user could need this.

I couldn't find something at SQL Standard about this topic.

Let me try to say it with shell syntax:

"MV [TEMP] TABLE a to b".