ALTER TABLE RENAME RENAME RENAME

Posted on by

Here’s a nice challenge for you. What does the following do (or error out on?):

CREATE TABLE t1 (a int);
CREATE TABLE t2 (b int);
ALTER TABLE t1 RENAME t3, RENAME t2, RENAME t4;

I’d be interested to know what a) you think it does and then b) if you were surprised when you went and typed it into your RDBMS of choice.

6 thoughts on “ALTER TABLE RENAME RENAME RENAME”

  2. heh interesting :)

    As far as I know the standard SQL doesn’t define any RENAME clause so we should expect RDBMS-es to behave differently. That said most RDBMSes I know don’t support multiple RENAME clauses – the only one I know that does is MySQL, and I suppose drizzle does too.

    I expected MySQL to err out because of the RENAME t2 bit, since t2 already exists. But it doesn’t, it looks like the statement shortcuts the chain of renames and only executes the last RENAME clause, immediately renaming t1 to t4. (I confirmed this by creating tables t1 and t4 and then execute the ALTER statement, and then you do get the t4 already exists error)

  3. I think this is a case where MySQL/Drizzle has given the user too much rope. The behavior is confusing, at best. We could simplify the code and clear up the behavior by only supporting one RENAME op per statement, which I think we should do in Drizzle, IMO.

  5. Guessed it would throw a syntax error, and I was right…

    renametest=# CREATE TABLE t1 (a int);
    CREATE TABLE
    Time: 68.979 ms
    renametest=# CREATE TABLE t2 (b int);
    CREATE TABLE
    Time: 1.158 ms
    renametest=# \d
    List of relations
    Schema | Name | Type | Owner
    ——–+——+——-+——–
    public | t1 | table | martin
    public | t2 | table | martin
    (2 rows)

    renametest=# ALTER TABLE t1 RENAME t3, RENAME t2, RENAME t4;
    ERROR: syntax error at or near “,”
    LINE 1: ALTER TABLE t1 RENAME t3, RENAME t2, RENAME t4;
    ^
    renametest=# \d
    List of relations
    Schema | Name | Type | Owner
    ——–+——+——-+——–
    public | t1 | table | martin
    public | t2 | table | martin
    (2 rows)

    renametest=#

    Please note: _My_ RDBMS of choice isn’t MySQL :)

  6. mysql> ALTER TABLE t2 RENAME t3, RENAME t1, modify column t2.b int unsigned not null, RENAME t2;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0 Duplicates: 0 Warnings: 0

    mysql> ALTER TABLE t2 RENAME t3, RENAME t1, modify column t1.b int unsigned not null, RENAME t2;
    ERROR 1103 (42000): Incorrect table name ‘t1’

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.