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:
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
== 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.
[8 Nov 2021 12:04] Najam Saqib
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

[8 Nov 2021 12:40] MySQL Verification Team
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".
[8 Nov 2021 12:40] MySQL Verification Team
Needless to say, the workaround for this bug is very easy, which is why this bug is not a high priority one.
[8 Nov 2021 13:34] Najam Saqib
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.

[8 Nov 2021 13:47] MySQL Verification Team
Your user should be logged in and using any of the existing schemas, for which the user has privileges.
[26 May 9:22] Sai Kumar G
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?
[26 May 12:38] MySQL Verification Team

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.