[
https://issues.apache.org/jira/browse/CALCITE-1917?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16911072#comment-16911072
]
ShuMing Li commented on CALCITE-1917:
-------------------------------------
Has this issue already resolved? I find Calcite already support such tests in
`SqlToRelConverterTest.java`:
{code:java}
@Test public void testJoinTemporalTableOnColumnReference() {
final String sql = "select stream *\n"
+ "from orders\n"
+ "join products_temporal for system_time as of orders.rowtime\n"
+ "on orders.productid = products_temporal.productid";
sql(sql).ok();
}
{code}
> 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
> Priority: Major
>
> 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
(v8.3.2#803003)
