[
https://issues.apache.org/jira/browse/CALCITE-1917?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Jark Wu updated CALCITE-1917:
-----------------------------
Description:
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.
[1]
https://lists.apache.org/thread.html/f877f356a8365bf74ea7d8e4a171224104d653cf73861afb2901a58f@%3Cdev.calcite.apache.org%3E
was:
you mean it can be covered by standard. I agree, the query I posted in the
previous mail can be rewrote as a subquery:
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.
> 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.
> [1]
> https://lists.apache.org/thread.html/f877f356a8365bf74ea7d8e4a171224104d653cf73861afb2901a58f@%3Cdev.calcite.apache.org%3E
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
