The problem is that I can't imagine what the queries look like in relational algebra. I take your point about being a stream-to-stream join, but it's not simple, because it's not symmetric. You join every record of the left side to the most recent record of the right-hand side. I don't know whether we model the RHS as a table macro or a temporal table.
Consider also that this logical relational algebra needs to yield (assuming a particular implementation of the temporal table as a regular table with (start, end) columns) a physical plan that performs range joins. To see why this is hard, start from the physical plan and work backwards. On Sun, Jul 15, 2018 at 7:54 AM, Piotr Nowojski <[email protected]> wrote: > Hi, > > Thanks for the answer! > > Yes, you are right, this is not strictly speaking equi join. I called it > stream-stream, because it’s a join between stream of Orders and stream of > RatesHistory (underlying data stream on top of which we can construct > temporal table). > > What problem do you see here? I do not see any significant difference between > [1] and [2] (and [3] for that matter) in terms of relational algebra, since > inner join with a join condition is equivalent to cross join followed by a > filter with join condition, regardless of whether relations that we are > joining are “temporal tables”, plane “tables” or subqueries. > > Piotrek > > On 2018/07/11 19:13:36, Julian Hyde <[email protected]> wrote: >> Just to clarify the subject. We’re not talking about stream-to-stream joins >> but stream joining to a temporal table. It’s not an equi-join: when an order >> occurs, we look for the applicable rate for that order. If I am mistaken in >> this, please let me know.> >> >> All 3 syntaxes look reasonable in principle. (3 is definitely not >> implemented; I thought 1 is supported, especially after I fixed some bugs in >> LATERAL recently (see https://issues.apache.org/jira/browse/CALCITE-531 >> <https://issues.apache.org/jira/browse/CALCITE-531>), but if not, go with >> 2.)> >> >> I think you should go with whichever syntax works, and we can roll out other >> syntaxes in future.> >> >> The bigger question is what these queries look like in relational algebra. >> Let’s go with syntax 2 for now, and once we get the algebra working, then we >> can make other syntaxes map to it.> >> >> This is a hard problem, and I’ve not thought it through, so we will need to >> iterate. I have a feeling that Rates might be a table macro rather than a >> table function.> >> >> Julian> >> >> > On Jul 11, 2018, at 6:55 AM, Piotr Nowojski <[email protected]> >> > wrote:> >> > > >> > Hi,> >> > > >> > In Flink we are now discussing how to implement “enrichment joins” (stream >> > to stream) in SQL. I have create a document with our proposal how to >> > implement it [1] (relevant to this email is “Syntax / SQL / querying” >> > section). I was working on top of the Julian’s document from 2016 [2].> >> > > >> > In [2] proposal was to use the following syntax:> >> > > >> > SELECT> >> > SUM(o.amount * r.rate) AS amount> >> > FROM > >> > Orders AS o> >> > LATERAL JOIN (TABLE Rates(o.rowtime)) AS r> >> > ON r.currency = o.currency;> >> > > >> > Because we in Flink we would like to have this feature as soon as >> > possible, I proposed in [1] SQL 1999 complaint syntax (as far as I know) >> > that’s also currently supported by Calcite:> >> > > >> > SELECT> >> > SUM(o.amount * r.rate) AS amount> >> > FROM > >> > Orders AS o,> >> > LATERAL TABLE (Rates(o.rowtime)) AS r> >> > WHERE r.currency = o.currency;> >> > > >> > However I have also found “temporal tables” from SQL 2011 "FOR SYSTEM_TIME >> > AS OF” syntax and proposal to expand SQL standard to handle stream-stream >> > join case [3]:> >> > > >> > SELECT *> >> > FROM Orders AS o> >> > JOIN LATERAL ProductPrices FOR SYSTEM_TIME AS OF O.orderTime AS P> >> > ON o.productId = p.productId> >> > > >> > The question that I have is what’s the current official status from >> > calcite regarding this feature? Is [3] the way to go in the foreseeable >> > future? If so, is anyone working on it at the moment? > >> > > >> > Piotr Nowojski> >> > > >> > [1] >> > https://docs.google.com/document/d/1KaAkPZjWFeu-ffrC9FhYuxE6CIKsatHTTxyrxSBR8Sk/edit?ts=5b45fea6#heading=h.avxnnym51mbe >> > >> > <https://docs.google.com/document/d/1KaAkPZjWFeu-ffrC9FhYuxE6CIKsatHTTxyrxSBR8Sk/edit?ts=5b45fea6#heading=h.avxnnym51mbe>> >> > [2] >> > https://docs.google.com/document/d/1KaAkPZjWFeu-ffrC9FhYuxE6CIKsatHTTxyrxSBR8Sk/edit?ts=5b45fea6# >> > >> > <https://docs.google.com/document/d/1KaAkPZjWFeu-ffrC9FhYuxE6CIKsatHTTxyrxSBR8Sk/edit?ts=5b45fea6#>> >> > [3] https://issues.apache.org/jira/browse/CALCITE-1917> >> >>
