MySQL Bugs: #95248: Manual description of DROP TABLE for multiple tables is not clear.

Bug #95248	Manual description of DROP TABLE for multiple tables is not clear.
Submitted:	4 May 2019 22:17	Modified:	15 May 2019 15:28
Reporter:	Meiji Kimura	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.6,5.7,8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
https://dev.mysql.com/doc/refman/5.6/en/drop-table.html
https://dev.mysql.com/doc/refman/5.7/en/drop-table.html
https://dev.mysql.com/doc/refman/8.0/en/drop-table.html

As above manual (maybe also earlier version), say as belows,

"If any tables named in the argument list do not exist, the statement returns an error indicating by name which nonexisting tables it was unable to drop, but also drops all tables in the list that do exist."

But this explanation is not clear. As literal, if t1 and t3 are exists and t2 is not exists, this command will indicate error for t2, and delete t1,t3. 

DROP TABLE t1,t2,t3;

But in this case, DROP TABLE itself will fail, don't drop any tables;

mysql> drop table t1,t2,t3;
ERROR 1051 (42S02): Unknown table 'deltest.t2'
mysql> show tables;
+-------------------+
| Tables_in_deltest |
+-------------------+
| t1                |
| t3                |
+-------------------+
2 rows in set (0.00 sec)

if a user want to drop tables as manual said, need to add 'IF EXISTS' keyword.
This command line will drop t1,t3 with warnings for t2.

mysql> drop table IF EXISTS t1,t2,t3;
Query OK, 0 rows affected, 1 warning (0.23 sec)

mysql> show warnings;
+-------+------+----------------------------+
| Level | Code | Message                    |
+-------+------+----------------------------+
| Note  | 1051 | Unknown table 'deltest.t2' |
+-------+------+----------------------------+
1 row in set (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

How to repeat:
mysql> create database deltest
    -> ;
Query OK, 1 row affected (0.07 sec)

mysql> use deltest
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table t1(i1 int);
Query OK, 0 rows affected (0.19 sec)

mysql> create table t3 like t1;
Query OK, 0 rows affected (0.15 sec)

mysql> show tables;
+-------------------+
| Tables_in_deltest |
+-------------------+
| t1                |
| t3                |
+-------------------+
2 rows in set (0.00 sec)

mysql> drop table t1,t2,t3;
ERROR 1051 (42S02): Unknown table 'deltest.t2'
mysql> show tables;
+-------------------+
| Tables_in_deltest |
+-------------------+
| t1                |
| t3                |
+-------------------+
2 rows in set (0.00 sec)

mysql> drop table IF EXISTS t1,t2,t3;
Query OK, 0 rows affected, 1 warning (0.23 sec)

mysql> show warnings;
+-------+------+----------------------------+
| Level | Code | Message                    |
+-------+------+----------------------------+
| Note  | 1051 | Unknown table 'deltest.t2' |
+-------+------+----------------------------+
1 row in set (0.00 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql>

Suggested fix:
Make the explanation more clear like this,

"If any tables named in the argument list do not exist, the statement returns an error indicating by name which nonexisting tables it was unable to drop, and drop no table.

If you do with 'IF EXISTS' keyword, the statement returns a warning for nonexisting tables, but also drops all tables in the list that do exist."

Thank you for the bug report.

Ah! Sorry, 'IF EXISTS' is explained in subsequent block as below.

//->Quate
 If any tables named in the argument list do not exist, the statement returns an error indicating by name which nonexisting tables it was unable to drop, but also drops all tables in the list that do exist.

Use IF EXISTS to prevent an error from occurring for tables that do not exist. Instead of an error, a NOTE is generated for each nonexistent table; these notes can be displayed with SHOW WARNINGS. See Section 13.7.5.40, “SHOW WARNINGS Syntax”. 
//<-Quate

So we should these like these. (I'm not native English Speaker, so please modify proper English :)

 If any tables named in the argument list do not exist, the statement returns an error indicating by name which nonexisting tables it was unable to drop, and drop no table.

Use IF EXISTS to prevent an error from occurring for tables that do not exist. Instead of an error, a NOTE is generated for each nonexistent table, and also drops all tables in the list that do exist. These notes can be displayed with SHOW WARNINGS. See Section 13.7.5.40, “SHOW WARNINGS Syntax”.

Original description reminds me an inplementation of Microsoft SQL Server.
In the same situation, SQL Server make an error for t2, but drop all tables in the list that do exist.

1> create table t1(i1 int);
2> create table t3(i1 int);
3> select * from sys.objects where type = 'U' and name like 't%';
4> go
name                                                                                                                             object_id   principal_id schema_id   parent_object_id type type_desc
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------ ----------- ---------------- ---- --------------------------------------------------------
t1                                                                                                                                 231671873         NULL           1                0 U    USER_TABLE
t3                                                                                                                                 247671930         NULL           1                0 U    USER_TABLE

(2 rows affected)
1> drop table t1,t2,t3;
2> go
Msg 3701, Level 11, State 5, Server MEKIMURA-JP\LOCALDB#AEF73652, Line 1
Cannot drop the table 't2', because it does not exist or you do not have permission.
1> select * from sys.objects where type = 'U' and name like 't%';
2> go
name                                                                                                                             object_id   principal_id schema_id   parent_object_id type type_desc
-------------------------------------------------------------------------------------------------------------------------------- ----------- ------------ ----------- ---------------- ---- --------------------------------------------------------

(0 rows affected)

Posted by developer:
 
The behavior changed in 8.0.3 because introduction of the data dictionary in 8.0 made it possible to make several DDL statements atomic.

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-3.html says:

DROP TABLE fails with an error if a named table does not exist, and no changes are made. Previously, the statement returned an error indicating which tables did not exist, but also dropped the tables that did exist.

Posted by developer:
 
Revised description for 5.5 through 5.7:

If any tables named in the argument list do not exist, DROP TABLE
behavior depends on whether the IF EXISTS clause is given:

* Without IF EXISTS, the statement drops all named tables that do exist,
  and returns an error indicating which nonexisting tables it was unable
  to drop.

* With IF EXISTS, no error occurs for nonexisting tables. The statement
  drops all named tables that do exist, and generates a NOTE diagnostic
  for each nonexistent table. These notes can be displayed with SHOW
  WARNINGS.

For 8.0, the description is the same except that the first bullet says this:

* Without IF EXISTS, the statement fails with an error indicating which
  nonexisting tables it was unable to drop, and no changes are made.