MySQL Bugs: #87726: Error 1142 when creating view with subquery without having a default schema

Bug #87726	Error 1142 when creating view with subquery without having a default schema
Submitted:	11 Sep 2017 13:44	Modified:	11 Sep 2017 22:09
Reporter:	Jesper Kristensen	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.7.7/8.0	OS:	Debian
Assigned to:		CPU Architecture:	Any

Description:
Without specifying a default schema it is not possible to create a view containing a subquery:

It might be related to bug #73590. At first the problem I encountered was a "No database selected" error while doing a select from a view that had a subquery as part of its definition. While trying to come up with a minimal example exposing that error I ended up with this instead.

How to repeat:
The commands have been tested on the official docker image version 5.7.7 and 5.7.19:

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=1234 -d mysql:5.7.19
# Wait ~10 seconds
docker run -it --link some-mysql:mysql --rm mysql:5.7.19 sh -c 'exec mysql -h"$MYSQL_PORT_3306_TCP_ADDR" -P"$MYSQL_PORT_3306_TCP_PORT" -uroot -p"$MYSQL_ENV_MYSQL_ROOT_PASSWORD"'

CREATE DATABASE tmp;
-- Query OK, 1 row affected (0.00 sec)
CREATE TABLE tmp.t1 (n INTEGER PRIMARY KEY);
-- Query OK, 0 rows affected (0.12 sec)
CREATE OR REPLACE VIEW tmp.tmp AS SELECT n FROM (SELECT n FROM tmp.t1) t;
-- ERROR 1142 (42000): ANY command denied to user 'root'@'%' for table '*'
USE tmp; -- Or any other scheme
-- Database changed
CREATE OR REPLACE VIEW tmp.tmp AS SELECT n FROM (SELECT n FROM tmp.t1) t;
-- Query OK, 0 rows affected (0.01 sec)

see https://bugs.mysql.com/bug.php?id=12755 looks like a partial fix.

miguel@URAL C:\dbs
> c:\dbs\5.7\bin\mysql -uroot --port=3570 -p --local-infile  --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.21-log Source distribution 2017-SEP-06

Copyright (c) 2000, 2017, 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 5.7 > CREATE DATABASE tmp;
Query OK, 1 row affected (0.01 sec)

mysql 5.7 > CREATE TABLE tmp.t1 (n INTEGER PRIMARY KEY);
Query OK, 0 rows affected (0.05 sec)

mysql 5.7 > CREATE OR REPLACE VIEW tmp.tmp AS SELECT n FROM (SELECT n FROM tmp.t1) t;
ERROR 1142 (42000): ANY command denied to user 'root'@'localhost' for table ''
mysql 5.7 > USE tmp
Database changed
mysql 5.7 > CREATE OR REPLACE VIEW tmp.tmp AS SELECT n FROM (SELECT n FROM tmp.t1) t;
Query OK, 0 rows affected (0.02 sec)

Thank you for the bug report.

https://dev.mysql.com/doc/refman/5.7/en/create-view.html

"A view belongs to a database. By default, a new view is created in the default database. To create the view explicitly in a given database, use db_name.view_name syntax to qualify the view name with the database name:

CREATE VIEW test.v AS SELECT * FROM t;

Unqualified table or view names in the SELECT statement...."