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
