[
https://issues.apache.org/jira/browse/CALCITE-968?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15005650#comment-15005650
]
Milinda Lakmal Pathirage commented on CALCITE-968:
--------------------------------------------------
Below is [~julianhyde]'s response from the developer mailing list on this
issue.
{quote}
The design falls into 3 parts:
* Validation. We should allow any combination: table-table, stream-table and
stream-stream joins, as long as the query can make progress. That often means
that where a stream is involved, the join condition should involve a monotonic
expression. If it is a stream-table join you can make progress without the
monotonic expression, but if there are 2 streams you will need it.
* Translation to relational algebra. Inspired by differential calculus’ product
rule[1], "stream(x join y)" becomes "x join stream(y) union all stream(x) join
y". Suppose that products is a table (i.e. we do not receive notifications of
new products); then "stream(products)" is empty. Suppose that orders is a both
a stream and a table; i.e. a stream with history. Because stream(products) is
empty, "stream(products join orders)" is simply “products join stream(orders)”.
These rewrites would happen in a DeltaJoinTransposeRule.
* Updates to relations. Suppose that the products table is updated two or three
times during each day. How quickly does the end user expect those updated
records to appear in the output of the stream-table join? If the table is
updated at 10am, should the new data be loaded only when processing
transactions from 10am (which might not hit the join until say 10:07am). There
is no ‘right answer’ here; we should offer the end user a choice of policies. A
good basic policy would be “cache for no more than T seconds” or “cache as long
as you like” but give a manual way to flush the cache.
{quote}
> 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)
