[
https://issues.apache.org/jira/browse/CALCITE-968?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15007612#comment-15007612
]
Julian Hyde commented on CALCITE-968:
-------------------------------------
Regarding {quote} the join condition should involve a monotonic
expression{quote} I was mistaken. Stream-relation joins do not need any
monotonic expression in the join condition to make progress. (I was thinking of
some temporal database thing where an order would be joined to the version of
the product table that was current at the moment the order was placed. But that
can wait until later!)
Your stream-to-relation examples look good. Let's create a new example of
stream-to-relation-to-relation example, implied by expanding the view in the
2nd example, and switch to USING just because we can:
{code}// stream-to-relation example #3
SELECT STREAM o.rowtime, o.orderId, p.supplierId, s.location
FROM Orders AS o
JOIN Products AS p USING (productId)
JOIN Suppliers AS s USING (supplierId){code}
Regarding stream-to-stream join. I don't think it is ever necessary for a FROM
item to have an OVER clause. (Please give a counter example if you have one! In
all the real cases I have seen the left rowtime is joined to a range from the
right, or vice versa.) I don't think that users will enjoy learning a new and
unnecessary SQL construct, so let's see if we can do without it. Here are the
queries with "monotonic expression" replaced:
{code}// stream-to-stream example #1
SELECT STREAM GREATEST(PacketsR1.rowtime, PacketsR2.rowtime) AS rowtime,
PacketsR1.sourcetime,
PacketsR1.packetId,
PacketsR2.rowtime - PacketsR1.rowtime AS timeToTravel
FROM PacketsR1
JOIN PacketsR2 ON PacketsR1.rowtime
BETWEEN PacketsR2.rowtime - INTERVAL '2' SECOND
AND PacketsR2.rowtime + INTERVAL '2' SECOND
AND PacketsR1.packetId = PacketsR2.packetId{code}
{code}// stream-to-stream example #2
SELECT STREAM Asks.rowtime,
Asks.askId as askId, Bids.bidId as bidId,
Asks.rowtime as askRowtime, Bids.rowtime as bidRowtime,
Asks.ticker, Asks.shares as askShares, Asks.prices as askPrice,
Bids.shares as bidShares, Bids.price as bidPrice
FROM Bids
JOIN Asks ON Asks.rowtime BETWEEN Bids.rowtime AND Bids.rowtime + INTERVAL '1'
MINUTE
AND Asks.ticker = Bids.ticker{code}
Note that I added a 'rowtime' expressions to each SELECT clause. Whereas
SQLstream's join operator generates an implicit rowtime column (rowtime is a
system column in SQLstream, but not in Calcite streams), we have to generate it
explicitly. But I think it's clearer that way.
> Support stream joins
> --------------------
>
> Key: CALCITE-968
> URL: https://issues.apache.org/jira/browse/CALCITE-968
> Project: Calcite
> Issue Type: New Feature
> Components: core, stream
> Reporter: Milinda Lakmal Pathirage
> Assignee: Julian Hyde
>
> Stream joins are used to relate information from different streams or stream
> and relation combinations. Calcite lacks (proper) support for
> stream-to-relation joins and stream-to-stream joins.
> stream-to-relation join like below fails at the SQL validation stage.
> select stream orders.orderId, orders.productId, products.name from orders
> join products on orders.productId = products.id
> But if 'products' is a stream, the query is valid according to Calcite, even
> though the stream-to-stream join in above query is not valid due to unbounded
> nature of streams.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
