Bug #11551 Asymmetric + undocumented behaviour of DROP VIEW and DROP TABLE
Submitted: 24 Jun 2005 18:17 Modified: 2 Aug 2006 19:13
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0 OS:
Assigned to: Georgi Kodinov

[24 Jun 2005 18:17] Matthias Leich
Description:
Let's start with an example to show the strange observations:
DROP TABLE IF EXISTS t1, t2;
DROP VIEW  IF EXISTS v1, v2;
CREATE TABLE t1 (f1 BIGINT);
CREATE VIEW  v1 AS SELECT * FROM t1;
DROP VIEW  v2, v1;
ERROR 42S02: Unknown table 'test.v2'
SELECT * FROM v1;
f1
   That means if the drop of the first view (v2) fails, the following
   view (v1) will be not dropped.
DROP TABLE t2, t1;
ERROR 42S02: Unknown table 't2'
SELECT * FROM t1;
ERROR 42S02: Table 'test.t1' doesn't exist
   That means if the drop of the first table (t2) fails, the following
   table (t1) will be dropped. 
   BTW: The manual says DROP TABLE gets an implicit COMMIT.
            So we have here a DROP table, which earns an error 
            message for the first and a funny additional COMMIT 
            for the second table.
            I assume that is confusing for customers and they do not
            expect this behaviour.

   1. It is not documented, what will happen, if the drop of one of
       several views or tables fails.
   2. There is an ugly asymmetry between the DROP VIEW and DROP
       TABLE behaviour, when the drop of one object fails.
SET sql_mode = 'traditional';
DROP TABLE IF EXISTS t1, t2;
DROP VIEW  IF EXISTS v1, v2;
CREATE TABLE t1 (f1 BIGINT);
DROP TABLE t2, t1;
ERROR 42S02: Unknown table 't2'
SELECT * FROM t1;
ERROR 42S02: Table 'test.t1' doesn't exist
   We have here the same behaviour like above and this is very
   questionable, because we are now in "traditional" mode.
   The behaviour on tables violates the all or nothing principle,
   which is usual valid for single statements in "traditional"
   SQL systems.
   The perfect behaviour would be, that nothing is dropped if
   the action for one element fails.

I am not able to decide if most of the things above are bugs or not,
because the manual does not contain a clear and detailed
description of the intended behaviour.
Only the missing documentation of this behaviour is a clear bug.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 5.0 ChangeSet@1.1972, 2005-06-21

How to repeat:
Please execute the statements above.
[6 Jul 2006 19:32] Trudy Pelzer
Re-verified using 5.1.12-beta-debug.

This is a bug because -- since views are just a form
of table -- their behaviour should be no different from
the behaviour exhibited by tables.

Sometimes dropping multiple views does work, though.
Consider the following.

mysql> select @@version;
+-------------------+
| @@version         |
+-------------------+
| 5.1.12-beta-debug |
+-------------------+
mysql> drop table if exists t1,t2;
mysql> create table t1 (col1 int);

mysql> drop table t1,t2;
ERROR 1051 (42S02): Unknown table 't2'
mysql> show tables;
Empty set (0.00 sec)

mysql> create table t1 (col1 int);

mysql> drop table t2,t1;
ERROR 1051 (42S02): Unknown table 't2'
mysql> show tables;
Empty set (0.01 sec)

Note the difference between the two examples: In the
first case, the existing table (t1) comes first in
the list of tables to drop. In the second case, the
non-existent table (t2) comes first in the list. But,
in both cases, table t1 is dropped and MySQL returns
an error indicating that table t2 couldn't be dropped
because it doesn't exist. This is the correct behaviour.

Here's what happens with views:

mysql> drop table if exists t1,t2;
mysql> drop view if exists vt1,vt2;
mysql> create table t1 (col1 int);
mysql> create view vt1 as select * from t1;

mysql> drop view vt1,vt2;
ERROR 1051 (42S02): Unknown table 'tp.vt2'
mysql> show tables;
+--------------+
| Tables_in_tp |
+--------------+
| t1           |
+--------------+
1 row in set (0.00 sec)
-- This is the correct behaviour; matching what is done
for tables. Although view vt2 doesn't exist, the DROP 
correctly drops vt1 and returns an error indicating that 
view vt2 can't be dropped because it doesn't exist.
However, when I turn the DROP arguments around:

mysql> create view vt1 as select * from t1;

mysql> drop view vt2,vt1;
ERROR 1051 (42S02): Unknown table 'tp.vt2'
mysql> show tables;
+--------------+
| Tables_in_tp |
+--------------+
| t1           |
| vt1          |
+--------------+
2 rows in set (0.00 sec)
-- This shows the incorrect behaviour; it doesn't
match the equivalent example for tables. That is,
when the first argument to DROP VIEW is the view
that doesn't exist, MySQL does correctly return an
error indicating that view vt2 can't be dropped 
because it doesn't exist -- but does not drop view
vt1, as it should.

Suggested fix:
Make DROP VIEW behave the same as DROP TABLE. When
DROP contains multiple arguments, one or more of
which do not exist, DROP the views that do exist
and return ERROR 1051 (42S02): Unknown table ...
for the non-existent views.
[28 Jul 2006 12:15] Bugs System
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/9704
[31 Jul 2006 17:56] Bugs System
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/9844

ChangeSet@1.2222, 2006-07-31 20:56:06+03:00, gkodinov@macbook.gmz +3 -0
  Bug #11551: Asymmetric + undocumented behaviour of DROP VIEW and DROP TABLE
   
  made DROP VIEW to continue on error and report an aggregated error 
   at its end. This makes it similar to DROP TABLE.
[2 Aug 2006 18:46] Evgeny Potemkin
Fixed in 5.0.25, 5.1.12
[2 Aug 2006 19:13] Paul Dubois
Noted in 5.0.25, 5.1.12 changelogs.