This looks very interesting. Are you familiar with the proposal I created in September, "Streams, joins and temporal tables"[1]? I started thinking about stream-to-table joins, where the tables where time-varying, and ended up with temporal database semantics.
But my impression of SQL:2011 (based on what is in Oracle) was that you could execute the WHOLE QUERY as of a particular timestamp, but you couldn't choose for table A to be at timestamp X and table B to be at timestamp Y. Furthermore, it only allows the timestamp to be constant, whereas we require the timestamp to be automatically varying. I think you have come to similar conclusions. I would like to hear how your proposal fits with mine. Julian [1] https://lists.apache.org/thread.html/4914256ad347e1d2a72506e2773e59590312d820f04cfcddaffaef1e@%3Cdev.calcite.apache.org%3E On Wed, Jul 26, 2017 at 11:39 PM, 伍翀(云邪) <[email protected]> wrote: > Hi all, > > We are looking for the temporal support in Calcite, or so-called > system-versioned temporal table. The temporal database feature was introduced > in ANSI SQL 2011 [1] and was supported in SQL Server 2017 [2]. A > system-versioned temporal table is designed to keep a full history of data > changes and allow easy point in time analysis. It is very useful for > scenarios that require tracking history of data changes. > > Because system-versioned tables are intended primarily for tracking > historical data changes, queries on system-versioned tables often tend to be > concerned with retrieving the table content as of a given point in time or > between any two given points in time. SQL:2011 provides FOR SYSTEM_TIME AS OF > syntactic extension for this specific purpose. For example, the following > query retrieves the rows of Emp that were current as of Jan. 2, 2011: > SELECT ENo,EName,Sys_Start,Sys_End > FROM Emp FOR SYSTEM_TIME AS OF TIMESTAMP '2011-01-02 00:00:00' > > In addition, we need the time expression can be a relational expression whose > value is from another table. For example, the following query joins the > Orders to the Prices as the price was at the order time: > SELECT STREAM * > FROM Orders AS o > JOIN LATERAL ProductPrices FOR SYSTEM_TIME AS OF o.orderTime AS p > ON o.productId = p.productId > > So I would like to introduce the syntactic extension in Calcite. What do you > think about this? Any comments or suggestions are welcome! > > [1] > https://sigmodrecord.org/publications/sigmodRecord/1209/pdfs/07.industry.kulkarni.pdf > [2] > https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables > [3] > https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table > [4] http://sqlhints.com/tag/for-system_time-as-of/ > > Bests, > Jark Wu
