[
https://issues.apache.org/jira/browse/CALCITE-1917?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16110168#comment-16110168
]
Jark Wu commented on CALCITE-1917:
----------------------------------
Hi [~julianhyde] , I expect that we can continue the discussion about joining
temporal tables (i.e. whether to support column reference) under this JIRA :)
> Support column reference in "FOR SYSTEM_TIME AS OF"
> ---------------------------------------------------
>
> Key: CALCITE-1917
> URL: https://issues.apache.org/jira/browse/CALCITE-1917
> Project: Calcite
> Issue Type: New Feature
> Reporter: Jark Wu
> Assignee: Julian Hyde
>
> As discussed in mailing list[1], the standard says QSTPS can’t contain a
> column reference. So when joining the Orders to the Products table for the
> price as of the time the order was placed is impossible using "FOR
> SYSTEM_TIME AS OF". But can be expressed using a subquery, such as:
> {code}
> SELECT *
> FROM Orders AS o
> JOIN LATERAL (SELECT * FROM ProductPrices WHERE sysStart <= O.orderTime
> AND sysEnd > O.orderTime) AS P
> ON o.productId = p.productId
> {code}
> But subquery is too complex for users. We know that the standard says it
> can’t contain a column reference. We initialize this discuss as we would like
> to "extend" the standard to simplify such query:
> {code}
> SELECT *
> FROM Orders AS o
> JOIN LATERAL ProductPrices FOR SYSTEM_TIME AS OF O.orderTime AS P
> ON o.productId = p.productId
> {code}
> [1]
> https://lists.apache.org/thread.html/f877f356a8365bf74ea7d8e4a171224104d653cf73861afb2901a58f@%3Cdev.calcite.apache.org%3E
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
