MySQL Bugs: #91122: Failed to create a view containing a from subquery(No selected database)

Bug #91122	Failed to create a view containing a from subquery(No selected database)
Submitted:	4 Jun 2018 7:40	Modified:	5 Jun 2018 5:49
Reporter:	Lehman Michael	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	>5.7.7, 5.7.22	OS:	CentOS
Assigned to:		CPU Architecture:	Any
Tags:	create a view, from subquery

Description:
login mysql with a new connection,don't use "use database" syntax,and create a view containing a from subquery by dbname.view_name,then report error "ANY command denied to user"
mysql> create view backup_test.v1 as select * from (select id from backup_test.t_temp_node) t;
ERROR 1142 (42000): ANY command denied to user 'root'@'%' for table '
mysql> show grants;
+------------------------------------------------------------+
| Grants for root@%                                           |
+------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'%' WITH GRANT OPTION        |
+------------------------------------------------------------+
2 rows in set (0.01 sec)

but,when I use "use database",Successful sql execution
mysql> use backup_test;
Database changed
mysql> create view backup_test.v1 as select * from (select id from backup_test.t_temp_node) t;
Query OK, 0 rows affected (0.00 sec)

How to repeat:
mysql version > 5.7.7
mysql user root 
#mysql>crete database d1;
#mysql>use d1;
#mysql>create table t1(id int primary key);

login mysql with a new connection without database 
#mysql>create view d1.v1 as select id from (select * from d1.t1) tmp;

Hello Michael,

Thank you for the report.

Thanks,
Umesh

## 8.0.11 - this seems to be fixed in 8.0.11

[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11:
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database backup_test;
Query OK, 1 row affected (0.09 sec)

mysql> create table backup_test.t_temp_node(id int not null);
Query OK, 0 rows affected (0.09 sec)

mysql> \q
Bye
[umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-8.0.11: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  create view backup_test.v1 as select * from (select id from backup_test.t_temp_node) t;
Query OK, 0 rows affected (0.11 sec)

mysql> SELECT CURRENT_USER(),USER();
+----------------+----------------+
| CURRENT_USER() | USER()         |
+----------------+----------------+
| root@localhost | root@localhost |
+----------------+----------------+
1 row in set (0.00 sec)

Thank you for your reply.
I look at the 8.0.11 bug list,One of th  bug fixes:
CREATE VIEW statements that used JSON_TABLE() in the view definition failed if no database was selected. (Bug #27189940)
My problem may have been fixed in this bug.But I don't know how to see the details of this bug.

Here is an extension of the problem.When I create a view that contains two layers of from subqueries like:

mysql> create view backup_test.v2 as select id from (select id from (select * from backup_test.t_temp_node) a) b;
Query OK, 0 rows affected (0.05 sec)

Then "show create table backup_test.v2"without using "use database".

mysql> show create table backup_test.v2;
ERROR 1046 (3D000): No database selected

mysql> use backup_test;
Database changed
mysql> show create table backup_test.v2;
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View | Create View                                                                                                                                                                                                        | character_set_client | collation_connection |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| v2   | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `b`.`id` AS `id` from (select `a`.`id` AS `id` from (select `t_temp_node`.`id` AS `id` from `t_temp_node`) `a`) `b` | utf8mb4              | utf8mb4_0900_ai_ci   |
+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

The tests above exist in mysql5.7(>5.7.7) 、mysql8.0.11