Jark, One more thing.
I am reading Tyler Akidau’s "Robust Streaming SQL in Apache Apex, Beam, Calcite, & Flink”[1] now. It will be a couple of days before I have concrete feedback but I will say now that Tyler has been giving temporal joins a lot of thought, seems to have incorporated my thinking, and generally does excellent work. So, definitely give his document serious consideration, as I am. When we all agree that we have the concepts right, I think it likely that we can embrace the syntax and semantics of temporal support that were introduced in SQL:2011. It’s important that we stay within the SQL standard for areas that it already covers. And by the way, I added PERIOD support to Calcite a while ago because that looked useful and un-contraversial. Julian [1] http://s.apache.org/streaming-sql-spec <http://s.apache.org/streaming-sql-spec> [2] https://issues.apache.org/jira/browse/CALCITE-715 <https://issues.apache.org/jira/browse/CALCITE-715> > On Jul 27, 2017, at 10:17 AM, Julian Hyde <[email protected]> wrote: > > 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
