Tracking view feature implement

[AndrewDi]

This issue is to track view feature implement. proposal is from #8416 .

CREATE VIEW

• ast, parser: extract create view parameters to CreateViewStmt ast, parser: extract create view parameters to CreateViewStmt parser#55
• model, parser: add viewinfo struct to TableInfo ast, parser: extract create view parameters to CreateViewStmt parser#55
• model, parser: add ActionCreateView to ddl actionMap ast, parser: extract create view parameters to CreateViewStmt parser#55
• ddl, tidb: implement create view with basic column check *: implement basic create view function #8571
• ddl, tidb: add OrReplace support when create view ddl: support "or replace" option when create view #8856
• test, tidb: add some test cases for CreateView
• ddl, tidb: view algorithm support derived_merge flag of the optimizer_switch system variable
• dml, tidb: implement CREATE_VIEW_PRIV privileges check parser: add CreateViewPriv privilege parser#138 planner, privileges: add CreateViewPriv Support #9153
• ddl, tidb: expand select's wildcard planner: unfold the wildcard when creating view #11818

DROP VIEW

• ast, parser: implement drop view parse parser: implement drop view parse parser#67 add drop view ddl action parser#131
• ddl, tidb: implement drop view func planner,executor: basic support for DROP_VIEW #8758

SHOW TABLE STATUS

• add func to support show view status

SELECT FROM VIEW

• modify func BuildDataSource to rewrite view planner: support building data source from View #8757
• support view SQL SECURITY privilege check privilege,planner: handle View's security privilege check #9194

SHOW FULL TABLS

• executor: alter func to support show view executor: show full tables support view #8860

DESCRIBE TABLE

• executor: alter func to support desc view executor: support "show columns from" for view #8863

SHOW CREATE TABLE

• executor: alter func to support show create view definition executor: support "show create table" for View #8865

Information Schema

• infoschema: support for views information_schema support for views #8889

ALTER TABLE

• ddl: ALTER TABLE should not permit on VIEW ddl: disallow alter table on view #8890

[morgo]

Can I help you with the infoschema implementation? I can see two issues:

1. make information_schema.tables show table_type, comment as view. All remaining columns as NULL.
2. add views to information_schema.views.

[AndrewDi]

Can I help you with the infoschema implementation? I can see two issues:

1. make information_schema.tables show table_type, comment as view. All remaining columns as NULL.
2. add views to information_schema.views.

@morgo Sure, Do you check validity of view during select from information_schema.views and information_schema.tables?

[morgo]

@AndrewDi I don't think so. Two things to mention though:

• The mysql server itself did not have view dependency tracking until MySQL 8.0. So we are 100% compatible to have views that will not work when queried.
• I know of this issue where metadata could have been created that is broken. But I think these should only be corrected/handled on read if there was a persistent data bug in a GA version.

I will create an issue to support information_schema.

[AndrewDi]

@morgo I have do some test on MySQL 5.7, and it seems like already have view dependency tracking?

mysql> create table t1(a int,b int);
Query OK, 0 rows affected (0.45 sec)

mysql> create view v as select * from t1;
Query OK, 0 rows affected (0.06 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.20 sec)

mysql> select * from information_schema.views where table_schema='test';
+---------------+--------------+------------+-----------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION                                                       | CHECK_OPTION | IS_UPDATABLE | DEFINER        | SECURITY_TYPE | CHARACTER_SET_CLIENT | COLLATION_CONNECTION |
+---------------+--------------+------------+-----------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
| def           | test         | v          | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` | NONE         |              | root@localhost | DEFINER       | latin1               | latin1_swedish_ci    |
+---------------+--------------+------------+-----------------------------------------------------------------------+--------------+--------------+----------------+---------------+----------------------+----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                  |
+---------+------+--------------------------------------------------------------------------------------------------------------------------+
| Warning | 1356 | View 'test.v' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
+---------+------+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MySQL Version:

root@2835f3d7b299:/# mysql -V
mysql  Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using  EditLine wrapper

[morgo]

@AndrewDi interesting behavior! I think this is the equivalent of running CHECK TABLE on each view. Since TiDB does not support CHECK TABLE, I think we should leave this to do later.

The 8.0 feature I was referring to is I_S.VIEW_TABLE_USAGE. You can find more info by searching for "information schema" within this page.

EDIT: Here is an example from MySQL 5.7 w/CHECK TABLE:

create table t1 (a int);
create view v1 as select * from t1;
drop table t1;
check table v1;

+---------+-------+----------+---------------------------------------------------------------------------------------------------------------------------+
| Table   | Op    | Msg_type | Msg_text                                                                                                                  |
+---------+-------+----------+---------------------------------------------------------------------------------------------------------------------------+
| test.v1 | check | Error    | Table 'test.t1' doesn't exist                                                                                             |
| test.v1 | check | Error    | View 'test.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them |
| test.v1 | check | error    | Corrupt                                                                                                                   |
+---------+-------+----------+---------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

[AndrewDi]

@morgo I'm agree with your idea, we could leave view check unimplement here. @XuHuaiyu