MySQL Bugs: #53976: ALTER TABLE RENAME is allowed on views (not documented, broken)

Bug #53976	ALTER TABLE RENAME is allowed on views (not documented, broken)
Submitted:	25 May 2010 15:54	Modified:	7 Jul 2010 19:20
Reporter:	Konstantin Osipov (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.0, 5.1, 5.6.99	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
ALTER TABLE ... RENAME is allowed for views. 
However, it is not documented and violates the metadata locking protocol.

History if the report is this:
On 11 Nov 2005 Oleksandr Byelkin wrote in Bug#5508:

Could you please fill a new bugreport (with ALTER TABLE, because a view is a table, and we
do not want invent new command in this case). If you do not make bugreport I will. But if
you make it you will be able to track progress of bugfixing.

[15 Nov 2005 21:56] Oleksandr Byelkin

I've made BUG#14959

Later Bug#14959 was fixed post-GA, but not documented.
This has never been a part of the original view specification.

How to repeat:
Inspect the code:
 /*
    If this is just a rename of a view, short cut to the
    following scenario: 1) lock LOCK_open 2) do a RENAME
    2) unlock LOCK_open.
    This is a copy-paste added to make sure
    ALTER (sic:) TABLE .. RENAME works for views. ALTER VIEW is handled
    as an independent branch in mysql_execute_command. The need
    for a copy-paste arose because the main code flow of ALTER TABLE
    ... RENAME tries to use open_ltable, which does not work for views
    (open_ltable was never modified to merge table lists of child tables
    into the main table list, like open_tables does).
    This code is wrong and will be removed, please do not copy.
  */
  frm_type= mysql_frm_type(thd, new_name_buff, &table_type);
  /* Rename a view */
  /* Sic: there is a race here */
  if (frm_type == FRMTYPE_VIEW && !(alter_info->flags & ~ALTER_RENAME))
  {

Suggested fix:
Remove this feature, produce ER_WRONG_OBJECT on attempt to rename.

Additional history on RENAME and ALTER TABLE RENAME for views, 
provided by Peter Gulutzan:

Regardless of views about ALTER TABLE ... RENAME,
RENAME TABLE view_name is there because of a deliberate
decision (from Monty), and would be okay in DB2.

WL#946 "Views" says ...
"
However, these are some exceptions:

HANDLER <View_name> is legal.
LOAD DATA ... INTO TABLE <View_name> is legal.
LOCK TABLES <View_name> is legal.
RENAME TABLE <View_name> is legal.
REPLACE ... <View_name> is legal.
TRUNCATE TABLE <View_name> is legal.

I couldn't see what extra functionality such statements
give users. But they're not impossible or inconceivable.
And Monty believes they should be legal.
"

With DB2 (r).
For ALTER TABLE t, t "must not be a view".

For RENAME [TABLE] t, "The RENAME statement renames a table, view, or
index."

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/db2/rbafzmstrena.ht...

With standard SQL.
ALTER TABLE <view name> is illegal, RENAME doesn't exist.

With Oracle.
I tried ALTER TABLE view_name RENAME to view_name_2, and got an error
message.

Thank you for the report.

Verified as described.

Though this fact was documented in 5.0.30 change log ("ALTER TABLE was not able to rename a view. (Bug#14959)")

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109404

3026 Dmitry Lenev	2010-05-27
      Tentative patch that addresses bug #53976 "ALTER TABLE RENAME
      is allowed on views (not documented, broken)".
      
      Remove support of ALTER TABLE RENAME for views as:
      a) this feature was not documented,
      c) does not add any compatibility with other databases,
      b) its implementation doesn't follow metadata locking
         protocol by accessing .FRM without holding any
         metadata lock,
      c) its implementation complicates ALTER TABLE's code
         by introducing  yet another separate branch to it.
      
      After this patch one can rename a view by using the
      documented way - RENAME TABLE statement.
     @ mysql-test/r/rename.result
        Adjusted existing test case for bug #14959 "ALTER TABLE isn't
        able to rename a view" after fixing bug #53976: "ALTER TABLE
        RENAME is allowed on views (not documented, broken)".
     @ mysql-test/r/view.result
        Adjusted test case after removing support for renaming
        of views through ALTER TABLE ... RENAME.
     @ mysql-test/t/rename.test
        Adjusted existing test case for bug #14959 "ALTER TABLE isn't
        able to rename a view" after fixing bug #53976: "ALTER TABLE
        RENAME is allowed on views (not documented, broken)".
     @ mysql-test/t/view.test
        Adjusted test case after removing support for renaming
        of views through ALTER TABLE ... RENAME.
     @ sql/sql_table.cc
        Removed support for ALTER TABLE ... RENAME for views.
        This feature was not properly documented and its
        implementation was not following metadata locking
        protocol.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109445

3029 Dmitry Lenev	2010-05-28
      Patch that addresses bug #53976 "ALTER TABLE RENAME
      is allowed on views (not documented, broken)".
      
      Remove support of ALTER TABLE RENAME for views as:
      a) this feature was not documented,
      c) does not add any compatibility with other databases,
      b) its implementation doesn't follow metadata locking
         protocol by accessing .FRM without holding any
         metadata lock,
      c) its implementation complicates ALTER TABLE's code
         by introducing  yet another separate branch to it.
      
      After this patch one can rename a view by using the
      documented way - RENAME TABLE statement.
     @ mysql-test/r/rename.result
        Adjusted existing test case for bug #14959 "ALTER TABLE isn't
        able to rename a view" after fixing bug #53976: "ALTER TABLE
        RENAME is allowed on views (not documented, broken)".
     @ mysql-test/r/view.result
        Adjusted test case after removing support for renaming
        of views through ALTER TABLE ... RENAME.
     @ mysql-test/t/rename.test
        Adjusted existing test case for bug #14959 "ALTER TABLE isn't
        able to rename a view" after fixing bug #53976: "ALTER TABLE
        RENAME is allowed on views (not documented, broken)".
     @ mysql-test/t/view.test
        Adjusted test case after removing support for renaming
        of views through ALTER TABLE ... RENAME.
     @ sql/sql_table.cc
        Removed support for ALTER TABLE ... RENAME for views.
        This feature was not properly documented and its
        implementation was not following metadata locking
        protocol.

Patch removing support of ALTER TABLE RENAME for views has been queued into mysql-trunk-runtime tree.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/109552

3031 Dmitry Lenev	2010-05-30
      Follow-up for patch that addresses bug #53976 "ALTER TABLE
      RENAME is allowed on views (not documented, broken)".
      
      Adjusted test cases in funcs_1 suite after removing support
      of ALTER TABLE RENAME for views.

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100622130139-u05awgya93zvbsop) (version source revid:marko.makela@oracle.com-20100603095032-v5ptkkzt1bhz0m1d) (merge vers: 5.1.48) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100622130623-r7yhm89fz9n5t9nb) (version source revid:alik@sun.com-20100622130528-187gd949sa9b6pa6) (pib:16)

Noted in 5.5.5 changelog.

ALTER TABLE for views is not legal but did not produce an error. (If
you need to rename a view, use RENAME TABLE.)