[
https://issues.apache.org/jira/browse/CALCITE-1917?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16111521#comment-16111521
]
Julian Hyde commented on CALCITE-1917:
--------------------------------------
Your query makes sense. I think we should do it.
I see why {{LATERAL}} is necessary, and we should use it, but it is slightly
inconvenient. It forces you to write the query with the stream on the left and
the temporal table on the right. I worry that it would make more complex
queries that ought to be valid difficult or impossible to express.
Longer term, I would like to find a syntax to join several temporal tables (or
streams, which are implicitly temporal) and implicitly link their temporal
parameters.
> 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)
