Fantastic! We're also very interested in this feature. +Boxiu
On Tue, Sep 17, 2019 at 11:31 AM Danny Chan <yuzhao....@gmail.com> wrote: > 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 >
