In umbrella task FLINK-10232 we have introduced CREATE TABLE grammar in our new module flink-sql-parser. And we proposed to use computed column to describe the time attribute of process time in the design doc FLINK SQL DDL, so user may create a table with process time attribute as follows: create table T1( a int, b bigint, c varchar, d as PROCTIME, ) with ( 'k1' = 'v1', 'k2' = 'v2' );
The column d would be a process time attribute for table T1. Besides that, computed columns have several other use cases, such as these [2]: • Virtual generated columns can be used as a way to simplify and unify queries. A complicated condition can be defined as a generated column and referred to from multiple queries on the table to ensure that all of them use exactly the same condition. • Stored generated columns can be used as a materialized cache for complicated conditions that are costly to calculate on the fly. • Generated columns can simulate functional indexes: Use a generated column to define a functional expression and index it. This can be useful for working with columns of types that cannot be indexed directly, such as JSON columns. • For stored generated columns, the disadvantage of this approach is that values are stored twice; once as the value of the generated column and once in the index. • If a generated column is indexed, the optimizer recognizes query expressions that match the column definition and uses indexes from the column as appropriate during query execution(Not supported yet). Computed columns are introduced in SQL-SERVER-2016 [1], MYSQL-5.6 [2] and ORACLE-11g [3]. This is the design doc: https://docs.google.com/document/d/110TseRtTCphxETPY7uhiHpu-dph3NEesh3mYKtJ7QOY/edit?usp=sharing Any suggestions are appreciated, thanks. [1] https://docs.microsoft.com/en-us/sql/relational-databases/tables/specify-computed-columns-in-a-table?view=sql-server-2016 [2] https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html [3] https://oracle-base.com/articles/11g/virtual-columns-11gr1 Best, Danny Chan
