Hi everyone, Thanks for the nice discussion. I’d like to move forward the work, please let me simply summarize the main opinion and current divergences.
1. The agreements have been achieved: 1.1 The motivation we're discussing temporal table DDL is just for creating temporal table in pure SQL to replace pre-process temporal table in YAML/Table API for usability. 1.2 The reason we use "TEMPORAL" keyword rather than “PERIOD FOR SYSTEM_TIME” is to make user understand easily. 1.3 For append-only table, it can convert to changelog table which has been discussed in FLIP-105, we assume the following temporal table is comes from changelog (Jark, fabian, Timo). 1.4 For temporal join syntax, using "FOR SYSTEM_TIME AS OF x" instead of the current `LATERAL TABLE(rates(x))` has come to an agreement(Fabian, Timo, Seth, Konstantin, Kurt). 2. The small divergence : About the definition syntax of the temporal table, CREATE [TEMPORAL] TABLE rates ( currency CHAR(3) NOT NULL PRIMARY KEY, rate DOUBLE, rowtime TIMESTAMP, WATERMARK FOR rowtime AS rowtime - INTERVAL '5' MINUTE) WITH (...); there is small divergence whether add "TEMPORAL" keyword or not. 2.1 one opinion is using "CREATE TEMPORAL TABLE" (Timo, Fabian, Seth), the main advantages are: (1)"TEMPORAL" keyword is intuitive to indicate the history tracking semantics. (2)"TEMPORAL" keyword illustrates that queries can visit the previous versions of a table like other DBMS use "PERIOD FOR SYSTEM_TIME" keyword. 2.2 the other is using "CREATE TABLE"(Kurt), the main advantages are: (1)Just primary key and time attribute can track previous versions of a table well. (2)The temporal behavior is triggered by temporal join syntax rather than in DDL, all Flink DDL table are dynamic table logically including temporal table. If we decide to use "TEMPORAL" keyword and treats changelog as temporal table, other tables backed queue like Kafka should also use "TEMPORAL" keyword. IMO, the statement “CREATE TEMPORARY TEMPORAL TABLE...” follows with 2.1 may confuse users much. If we take a second to think about, for source/sink table which may backed queue (like kafka) or DB (like MySQL), we did not add any keyword in DDL to specify they are source or sinks, it works well. I think temporal table is the third one, kafka data source and DB data source can play as a source/sink/temporal table depends on the position/syntax that user put them in the query. The above rates table - can be a source table if user put it at `SELECT * FROM rates;` - can be a temporal table if user put it at `SELECT * FROM orders JOIN rates FOR SYSTEM_TIME AS OF orders.proctime ON orders.currency = rates.currency;` - can be sink table if user put is at `INSERT INTO rates SELECT * FROM …; ` From these cases, we found all tables defined in Flink should be dynamic table logically, the source/sink/temporal role depends on the position/syntax in user’s query. In fact we have used similar syntax for current lookup table, we didn’t add “LOOKUP" or “TEMPORAL" keyword for lookup table and trigger the temporal join from the position/syntax(“FOR SYSTEM_TIME AS OF x") in query. So, I prefer to resolve the small divergence with “CREATE TABLE” which (1) is more unified with our source/sink/temporal dynamic table conceptually, (2) is aligned with current lookup table, (3) also make users learn less keyword. WDYT? Best, Leonard Xu