MySQL Bugs: #100989: servers report `Unknown table` for materialized view

Bug #100989	servers report `Unknown table` for materialized view
Submitted:	29 Sep 2020 9:25	Modified:	29 Sep 2020 9:55
Reporter:	Brian Yue (OCA)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S2 (Serious)
Version:	MySQL8.0.21	OS:	Any (rhel-7.4)
Assigned to:		CPU Architecture:	Any (intel x86)

Description:
Hello, dear Verification team:
  Here is a SQL reporting `ERROR 1051 (42S02): Unknown table`, in which a materialized view is accessed with `db.table.*`.
  Please reference to `How to repeat` for detail.

mysql> select `connect_by_test`.`vw_4_5`.* from `connect_by_test`.`vw_4_5`;
ERROR 1051 (42S02): Unknown table 'connect_by_test.vw_4_5'

How to repeat:
Executable steps like this (SQLs):

drop database if exists connect_by_test;
create database if not exists connect_by_test;
use connect_by_test;

create table t4(
   ID int not null key,
   year_1 int(4),
   month_1 int(2),
   day_1 int(2),
   num int(8),
   info varchar(100)
);

insert into t4(ID,year_1,month_1,day_1,num) values(1,1900,10,01,99110021);
insert into t4(ID,year_1,month_1,day_1,num) values(2,1902,12,12,99110214);
insert into t4(ID,year_1,month_1,day_1,num) values(3,1902,11,15,99120215);

create table t5(
   ID int not null key,
   year_1 int(4),
   month_1 int(2),
   day_1 int(2),
   num int(8),
   info varchar(100)
);

insert into t5(ID,year_1,month_1,day_1,num) values(1,1920,10,01,99110021);
insert into t5(ID,year_1,month_1,day_1,num) values(2,1932,12,02,99110214);
insert into t5(ID,year_1,month_1,day_1,num) values(3,1956,11,25,99120215);

create view vw_4_5 as select * from t4 union select * from t5;

select `connect_by_test`.`vw_4_5`.* from `connect_by_test`.`vw_4_5`;

result:
mysql> select `connect_by_test`.`vw_4_5`.* from `connect_by_test`.`vw_4_5`;
ERROR 1051 (42S02): Unknown table 'connect_by_test.vw_4_5'

Suggested fix:
nope

However, access fields only with table_name works well:

mysql> select `vw_4_5`.* from `connect_by_test`.`vw_4_5`;
+----+--------+---------+-------+----------+------+
| ID | year_1 | month_1 | day_1 | num      | info |
+----+--------+---------+-------+----------+------+
|  1 |   1900 |      10 |     1 | 99110021 | NULL |
|  2 |   1902 |      12 |    12 | 99110214 | NULL |
|  3 |   1902 |      11 |    15 | 99120215 | NULL |
|  1 |   1920 |      10 |     1 | 99110021 | NULL |
|  2 |   1932 |      12 |     2 | 99110214 | NULL |
|  3 |   1956 |      11 |    25 | 99120215 | NULL |
+----+--------+---------+-------+----------+------+
6 rows in set (0.01 sec)

Thank you for the bug report. I couldn't repeat with most recent source server built:

c:\dbs>c:\dbs\8.0\bin\mysql -uroot --port=3580 -p --prompt="mysql 8.0 > "
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.22 Source distribution BUILT: 2020-SEP-06

Copyright (c) 2000, 2020, 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 8.0 > drop database if exists connect_by_test;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql 8.0 > create database if not exists connect_by_test;
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > use connect_by_test;
Database changed
mysql 8.0 >
mysql 8.0 > create table t4(
    ->    ID int not null key,
    ->    year_1 int(4),
    ->    month_1 int(2),
    ->    day_1 int(2),
    ->    num int(8),
    ->    info varchar(100)
    -> );
Query OK, 0 rows affected, 4 warnings (0.03 sec)

mysql 8.0 >
mysql 8.0 > insert into t4(ID,year_1,month_1,day_1,num) values(1,1900,10,01,99110021);
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > insert into t4(ID,year_1,month_1,day_1,num) values(2,1902,12,12,99110214);
Query OK, 1 row affected (0.01 sec)

mysql 8.0 > insert into t4(ID,year_1,month_1,day_1,num) values(3,1902,11,15,99120215);
Query OK, 1 row affected (0.00 sec)

mysql 8.0 >
mysql 8.0 > create table t5(
    ->    ID int not null key,
    ->    year_1 int(4),
    ->    month_1 int(2),
    ->    day_1 int(2),
    ->    num int(8),
    ->    info varchar(100)
    -> );
Query OK, 0 rows affected, 4 warnings (0.02 sec)

mysql 8.0 >
mysql 8.0 > insert into t5(ID,year_1,month_1,day_1,num) values(1,1920,10,01,99110021);
Query OK, 1 row affected (0.00 sec)

mysql 8.0 > insert into t5(ID,year_1,month_1,day_1,num) values(2,1932,12,02,99110214);
Query OK, 1 row affected (0.00 sec)

mysql 8.0 > insert into t5(ID,year_1,month_1,day_1,num) values(3,1956,11,25,99120215);
Query OK, 1 row affected (0.00 sec)

mysql 8.0 >
mysql 8.0 > create view vw_4_5 as select * from t4 union select * from t5;
Query OK, 0 rows affected (0.01 sec)

mysql 8.0 > select `connect_by_test`.`vw_4_5`.* from `connect_by_test`.`vw_4_5`;
+----+--------+---------+-------+----------+------+
| ID | year_1 | month_1 | day_1 | num      | info |
+----+--------+---------+-------+----------+------+
|  1 |   1900 |      10 |     1 | 99110021 | NULL |
|  2 |   1902 |      12 |    12 | 99110214 | NULL |
|  3 |   1902 |      11 |    15 | 99120215 | NULL |
|  1 |   1920 |      10 |     1 | 99110021 | NULL |
|  2 |   1932 |      12 |     2 | 99110214 | NULL |
|  3 |   1956 |      11 |    25 | 99120215 | NULL |
+----+--------+---------+-------+----------+------+
6 rows in set (0.01 sec)

mysql 8.0 >