Bug #77509 Using an alias to perform DELETE statement in information_schema

Description:
Using an alias to perform DELETE statement in the information_schema database ，You will get an error like this ：
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

How to repeat:
root@localhost : test 02:30:22> use test
Database changed
root@localhost : test 02:30:30> show create table t;
+-------+------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost : test 02:30:34> select * from t;
+------+-------+
| id   | name  |
+------+-------+
|    1 | MySQL |
+------+-------+
1 row in set (0.00 sec)

root@localhost : test 02:30:38> start transaction;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 02:31:05> delete t from test.t  t where t.id=1;
Query OK, 1 row affected (0.09 sec)

root@localhost : test 02:31:19> rollback;
Query OK, 0 rows affected (0.00 sec)

root@localhost : test 02:31:23> use mysql
Database changed
root@localhost : mysql 02:31:29> start transaction;
Query OK, 0 rows affected (0.00 sec)

root@localhost : mysql 02:31:31> delete t from test.t  t where t.id=1;
Query OK, 1 row affected (0.01 sec)

root@localhost : mysql 02:31:33> rollback;
Query OK, 0 rows affected (0.00 sec)

root@localhost : mysql 02:31:35> use information_schema
Database changed
root@localhost : information_schema 02:31:40> start transaction;
Query OK, 0 rows affected (0.00 sec)

root@localhost : information_schema 02:31:42> delete t from test.t  t where t.id=1;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

root@localhost : information_schema 02:32:50> start transaction;
Query OK, 0 rows affected (0.01 sec)

root@localhost : information_schema 02:32:52> delete test.t from test.t  where test.t.id=1;
Query OK, 1 row affected (0.00 sec)

Exactly speaking,Using an alias of table_name to perform DELETE statement in the information_schema database ，You will get an error like this ：
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

Hello Pu Hot,

Thank you for the report and test case.

Thanks,
Umesh

// 5.6.27

mysql>  show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.6.27                                                  |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.6.27-enterprise-commercial-advanced                   |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | linux-glibc2.5                                          |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)

mysql>  show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>  use test
Database changed
mysql>  CREATE TABLE `t` (
    ->    `id` int(11) DEFAULT NULL,
    ->    `name` varchar(16) DEFAULT NULL
    ->  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql>  insert into t values(1,'mysql');
Query OK, 1 row affected (0.00 sec)

mysql>  select * from t;
+------+-------+
| id   | name  |
+------+-------+
|    1 | mysql |
+------+-------+
1 row in set (0.00 sec)

mysql>  start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql>  delete t from test.t  t where t.id=1;
Query OK, 1 row affected (0.00 sec)

mysql>  rollback;
Query OK, 0 rows affected (0.00 sec)

mysql>  use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql>  delete t from test.t  t where t.id=1;
Query OK, 1 row affected (0.00 sec)

mysql>  rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql>  delete t from test.t  t where t.id=1;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'
mysql>  start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql>  delete test.t from test.t  where test.t.id=1;
Query OK, 1 row affected (0.00 sec)

mysql>

// 5.7.8

mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name           | Value                                                   |
+-------------------------+---------------------------------------------------------+
| innodb_version          | 5.7.8                                                   |
| protocol_version        | 10                                                      |
| slave_type_conversions  |                                                         |
| version                 | 5.7.8-rc-enterprise-commercial-advanced                 |
| version_comment         | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64                                                  |
| version_compile_os      | Linux                                                   |
+-------------------------+---------------------------------------------------------+
7 rows in set, 1 warning (0.00 sec)

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql>
mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> CREATE TABLE `t` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `name` varchar(16) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t;
Empty set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete t from test.t  t where t.id=1;
Query OK, 0 rows affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>  start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete t from test.t  t where t.id=1;
Query OK, 0 rows affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete t from test.t  t where t.id=1;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'

mysql> delete test.t from test.t  where test.t.id=1;
Query OK, 0 rows affected (0.00 sec)

// 5.5.46

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.5.46                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| version                 | 5.5.46                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux2.6                     |
+-------------------------+------------------------------+
7 rows in set (0.01 sec)

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> CREATE TABLE `t` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `name` varchar(16) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ERROR 1050 (42S01): Table 't' already exists
mysql> drop table if exists t;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `t` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `name` varchar(16) DEFAULT NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values(1,'mysql');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t;
+------+-------+
| id   | name  |
+------+-------+
|    1 | mysql |
+------+-------+
1 row in set (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete t from test.t  t where t.id=1;
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete t from test.t  t where t.id=1;
Query OK, 1 row affected (0.00 sec)

mysql>  rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete t from test.t  t where t.id=1;
ERROR 1044 (42000): Access denied for user 'root'@'localhost' to database 'information_schema'
mysql>
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete test.t from test.t  where test.t.id=1;
Query OK, 1 row affected (0.00 sec)

mysql>