MySQL Bugs: #100284: Querying a VIEW fails unless there is a default database

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

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.

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.

== 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.

HI Mr. R,

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

Verified as reported.

Changing category.

I want to ask that whether this issue has been fixed or not and if fixed than in which version of MySql 5.7. Currently I am using 5.7.33 and getting this error while updating MySql to 8.0.26. 
I am using AWS RDS to update MySql and their routine exists with following error log. 

Issues reported by 'check table x for upgrade' command
barfeemart.180_day_retention - No database selected
barfeemart.30_day_repurchase_rate - No database selected
barfeemart.30_day_repurchase_rate - Corrupt

Regards

Hi Mr. Saqib,

Whenever some bug report is in the "Verified" state, it means that it has not been fixed, unless by fixing some other bug report. 

Hence, you can easily check it by using the latest 5.7 release, which is 5.7.36.

Otherwise, you will have to wait that this bug report changes its status to "Closed".

Needless to say, the workaround for this bug is very easy, which is why this bug is not a high priority one.

Can you please tell me the workaround, I can only recall that we can drop views and then create again, may be I am missing some thing.

Regards

Your user should be logged in and using any of the existing schemas, for which the user has privileges.

I have the same issue while upgrading AWS RDS MySQL version from 5.7 to 8.0 through Cloud Formation template. Could you please provide a fix for this?

Hi,

Unfortunately, we can not provide a fix , since there is no progress made on this bug.

You could do two things. One is to switch to the latest available 5.7 on dev.mysql.com.

Second one is to make a backup and try starting 8.0.33, for the upgrade, over that same datadir and see if the bug is fixed there.
To our knowledge, this bug is not in the works yet, nor is it known when will it be. Any way, a severity of the bug is quite low.

That does not mean that it is , may be, fixed by a patch for some other bug.