Bug #100284 Querying a VIEW fails unless there is a default database
Submitted: 22 Jul 2020 8:07 Modified: 23 Jul 2020 12:29
Reporter: Niranjan R Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.7.16, 5.7.31 OS:Any
Assigned to: CPU Architecture:Any
Tags: Deafult database, Views

[22 Jul 2020 8:07] Niranjan R
Description:
== Querying the VIEW without having an active database gives an error.

mysql> select * from northwind.testview;
ERROR 1046 (3D000): No database selected

== Check command reports it as corrupted
mysql> check table northwind.testview;
+--------------------+-------+----------+----------------------+
| Table              | Op    | Msg_type | Msg_text             |
+--------------------+-------+----------+----------------------+
| northwind.testview | check | Error    | No database selected |
| northwind.testview | check | error    | Corrupt              |
+--------------------+-------+----------+----------------------+
2 rows in set (0.00 sec)

== When we set any database as active, we can query that table:

mysql> select * from northwind.testview;
+------------+----------------+-----------------------+---------------------------+
| CategoryID | CategoryName   | Actual Avg Unit Price | Would-Like Avg Unit Price |
+------------+----------------+-----------------------+---------------------------+
|          1 | Beverages      |                 30.80 |                     37.98 |
|          2 | Condiments     |                 28.54 |                     23.06 |
|          3 | Confections    |                 17.41 |                     25.16 |
|          4 | Dairy Products |                 34.80 |                     28.73 |
|          5 | Grains/Cereals |                 25.63 |                     20.25 |
|          6 | Meat/Poultry   |                 39.00 |                     54.01 |
|          7 | Produce        |                 35.20 |                     32.37 |
|          8 | Seafood        |                 13.40 |                     20.68 |
+------------+----------------+-----------------------+---------------------------+
8 rows in set (0.00 sec)

mysql> check table northwind.testview for upgrade;
+--------------------+-------+----------+----------+
| Table              | Op    | Msg_type | Msg_text |
+--------------------+-------+----------+----------+
| northwind.testview | check | status   | OK       |
+--------------------+-------+----------+----------+
1 row in set (0.00 sec)

How to repeat:
== Loaded data into the database and created a VIEW with 'derived tables'

create VIEW `northwind`.`testview` AS select `y`.`CategoryID` AS `CategoryID`,`y`.`CategoryName` AS `CategoryName`,round(`x`.`actual_unit_price`,2) AS `Actual Avg Unit Price`,round(`y`.`planned_unit_price`,2) AS `Would-Like Avg Unit Price` from ((select avg(`a`.`unit_price`) AS `actual_unit_price`,`c`.`CategoryID` AS `CategoryID` from ((`northwind`.`order_details` `a` join `northwind`.`products` `b` on((`b`.`ProductID` = `a`.`product_id`))) join `northwind`.`categories` `c` on((`b`.`CategoryID` = `c`.`CategoryID`))) group by `c`.`CategoryID`) `x` join (select `a`.`CategoryID` AS `CategoryID`,`b`.`CategoryName` AS `CategoryName`,avg(`a`.`UnitPrice`) AS `planned_unit_price` from (`northwind`.`products` `a` join `northwind`.`categories` `b` on((`b`.`CategoryID` = `a`.`CategoryID`))) group by `a`.`CategoryID`) `y` on((`x`.`CategoryID` = `y`.`CategoryID`)));

== Connect to the server without default database.
>> Throws an error when you access the view.

== Set the Database as active and there is no error.

== This further causes issue during the MySQL engine version upgrade pre-check.

Tested the scenario on the MySQL 5.7.16 and the latest version of 5.7 (5.7.30), and the issue persists.

Suggested fix:
This looks like the same bug which was reported under "https://bugs.mysql.com/bug.php?id=90160" for MySQL 8.0.11

Though it was reported only for JSON_TABLE and querying INFORMATION_SCHEMA.KEYWORDS table, its actually applicable for the views with derived tables, recursive reference, table functions as well. 
[+] https://github.com/mysql/mysql-server/commit/9ad5e32e833bd09805307cf4bc166f39bb6e3ec7

Tested the scenario on 8.0.13 and above, and could see that there is no issue.
[22 Jul 2020 12:52] MySQL Verification Team
Hi Mr. R,

Thank you for your bug report.

From what I can see, you have a table with the same name as the view. Am I correct or not ???

Also, do you have that schema `northwind` with that table and is your filesystem case sensitive or not ??? If it is , is the case of the schema title correct ?????

You have concluded that this is fixed in MySQL 8.0. Hence, we will also have to check whether this is fixable in 5.7 as well.

Your feedback is truly welcome.
[22 Jul 2020 15:20] Niranjan R
== From what I can see, you have a table with the same name as the view. Am I correct or not ???
>> No. I do not have a table name with the same name as the view.
View name : "testview"
I do not have any table with the same name.

== do you have that schema `northwind` with that table?
>> Like i told, schema does not have the table. It has other tables, using which I have created a VIEW named "testview"

== is your filesystem case sensitive or not?
>> It is not case sensitive. 
That being said, the case of the schema title is indeed correct.

== You have concluded that this is fixed in MySQL 8.0
>> just to re-iterate, the issue persists in 8.0.11, and is fixed in 8.0.13 and above

== If my repro steps aren't clear, then just to put it in simple words, create a view with derived tables.

I hope this answers all your queries.
Let me know if you need more information. I'm okay to attach the SQL script.
[23 Jul 2020 12:29] MySQL Verification Team
HI Mr. R,

I have managed to reproduce your test case, but only on 5.7.

Verified as reported.
[23 Jul 2020 13:07] MySQL Verification Team
Changing category.