Bug #103704 Strange population of INFORMATION_SCHEMA.VIEWS
Submitted: 14 May 17:14 Modified: 15 May 8:41
Reporter: Georgi Georgiev Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:8.0.22 OS:Any
Assigned to: CPU Architecture:Any

[14 May 17:14] Georgi Georgiev
Description:
Column "VIEW_DEFINITION" in "INFORMATION_SCHEMA.VIEWS" is empty for super user upon initial select until it is populated with one of the following queries:

SHOW FULL TABLES IN <db_name>;
or
SHOW triggers from <db_name>;

The following query does not populates "VIEW_DEFINITION" even it returns correct info:
SHOW CREATE VIEW <db_name>.<view_name>;

When login directly with 'definer', column "VIEW_DEFINITION" is populated.

How to repeat:
1. Not working
- Login with regular user
- create view
- Login with superuser
- Execute "SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='db_name';" and check that column "VIEW_DEFINITION" is empty
- Execute either "SHOW FULL TABLES IN <db_name>;" or "SHOW triggers from <db_name>;"
- Execute "SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='db_name';" and check that column "VIEW_DEFINITION" is populated

2. Works
- Login with regular user
- Create view
- Logout
- Login with same user
- Execute "SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='db_name';" and check that column "VIEW_DEFINITION" is populated
[14 May 17:19] Georgi Georgiev
May be it is related with https://bugs.mysql.com/bug.php?id=22763
[14 May 20:20] MySQL Verification Team
Thank you for the bug report. I couldn't repeat with latest release 8.0.25:

d:\dbs>8.0\bin\mysql -uroot -p
Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_SCHEMA='db_name'\G
*************************** 1. row ***************************
       TABLE_CATALOG: def
        TABLE_SCHEMA: db_name
          TABLE_NAME: vtest_bug
     VIEW_DEFINITION: select 1 AS `1`
        CHECK_OPTION: NONE
        IS_UPDATABLE: NO
             DEFINER: luis@localhost
       SECURITY_TYPE: DEFINER
CHARACTER_SET_CLIENT: cp850
COLLATION_CONNECTION: cp850_general_ci
1 row in set (0.00 sec)

mysql>
[15 May 8:41] Georgi Georgiev
Thank you. I will check with latest version.