Hi Julian, Thanks for the valuable input. We have read “Streams, joins and temporal tables” you proposed before I started the discussion. The “temporal table view” proposed in the doc is very interesting and well-defined. But we hope to support this feature based on the standard SQL 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. Regarding to “WHOLE QUERY” or “Table”, I didn't find any declaration in SQL:2011, but I think it effects the Table not the query. In SQL Server’s “FROM” syntax[1], it is a subset of table syntax. In Oracle’s example[2], it follows after table not the whole query. And I also tried in SQL Server, the following SQL works well. The TemporalRates and TemporalRates2 are both temporal table with the same schema. The query chooses different timestamp of table TemporalRates and TemporalRates2: SELECT * FROM TemporalRates2 FOR SYSTEM_TIME AS OF '2017-07-28 07:49:07.5649523' AS T2 JOIN TemporalRates FOR SYSTEM_TIME AS OF '2017-07-28 04:26:19.5126849' AS T1 ON T1.id = T2.id; So I think the only controversial point is it only allows constant timestamp. Then I find the following example from SQL Server doc [3] : DECLARE @ADayAgo datetime2 SET @ADayAgo = DATEADD (day, -1, sysutcdatetime()) /*Comparison between two points in time for subset of rows*/ SELECT D_1_Ago.[DeptID], D.[DeptID], D_1_Ago.[DeptName], D.[DeptName], D_1_Ago.[SysStartTime], D.[SysStartTime], D_1_Ago.[SysEndTime], D.[SysEndTime] FROM [dbo].[Department] FOR SYSTEM_TIME AS OF @ADayAgo AS D_1_Ago JOIN [Department] AS D ON D_1_Ago.[DeptID] = [D].[DeptID] AND D_1_Ago.[DeptID] BETWEEN 1 and 5 ; The time @ADayAgo is a variable not a constant here, so I’m wondering whether it is acceptable to support LATERAL temporal to make the timestamp automatically varying. Which is very useful in many scenarios. [1] https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql <https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql> [2] https://oracle-base.com/articles/12c/temporal-validity-12cr1 <https://oracle-base.com/articles/12c/temporal-validity-12cr1> [3] https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table <https://docs.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table> - Jark Wu > 在 2017年7月28日，上午9:03，Julian Hyde <[email protected]> 写道： > > 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] >> <mailto:[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 >> >> <https://lists.apache.org/thread.html/4914256ad347e1d2a72506e2773e59590312d820f04cfcddaffaef1e@%3Cdev.calcite.apache.org%3E> >> >> On Wed, Jul 26, 2017 at 11:39 PM, 伍翀(云邪) <[email protected] >> <mailto:[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 >>> >>> <https://sigmodrecord.org/publications/sigmodRecord/1209/pdfs/07.industry.kulkarni.pdf> >>> [2] >>> https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables >>> >>> <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 >>> >>> <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/ >>> <http://sqlhints.com/tag/for-system_time-as-of/> >>> >>> Bests, >>> Jark Wu >
