I think we should also discuss https://issues.apache.org/jira/browse/CALCITE-4274 here.
We've never discussed what should happen if you apply TUMBLE to TUMBLE (or TUMBLE to HOP, etc.). What happens now is that you get duplicate columns. Is it reasonable to apply TUMBLE to TUMBLE? If so, would people generally want two sets of window_start, window_end columns? Julian On Wed, Sep 23, 2020 at 2:41 AM Danny Chan <[email protected]> wrote: > > Thanks for the feedback, I agree we should keep the verbose part > > **L.window_start = R.window_start AND L.window_end =R.window_end** > > Which would make the semantic more clear ~ > > Best, > Danny Chan > 在 2020年9月23日 +0800 PM3:24，Viliam Durina <[email protected]>，写道： > > You can also use > > > > SELECT L.f0, R.f2, L.window_start, L.window_end > > FROM > > Tumble(table T1, descriptor(T1.ts), INTERVAL ‘5’ MINUTE) L > > JOIN > > Tumble(table T2, descriptor(T2.ts), INTERVAL ‘5’ MINUTE) R > > USING (f0, window_start) > > > > Viliam > > > > On Wed, 23 Sep 2020 at 08:02, Rui Wang <[email protected]> wrote: > > > > > Regarding to **L.window_start = R.window_start AND L.window_end = > > > R.window_end**: > > > > > > In general, the current table function windowing model is to append window > > > metadata to table directly, thus window metadata becomes a part of table > > > (or call it data). So as a part of table, these two columns should be > > > treated as normal columns thus they should be in the join on condition. > > > > > > If you want to make it optional, it makes window start/end columns special > > > and has a semantic binding with special table functions (TUMBLE, HOP, > > > SESSION), which then becomes really not a SQL thing. For example, we can > > > allow users to define their own windowing table function. In that case, > > > how > > > will you utilize window start/end produced by a customized windowing table > > > function? What if users produce wired windows that have overlapped window > > > starts or window ends? > > > > > > Keeping windows start/end as a part of the table, treating them no > > > different from other columns, could give a consistent behavior for either > > > built-in table function or user-defined table function. > > > > > > If you think it is too verbose, there are two options to optimize: > > > > > > 1. for TUMBLE/HOP/SESSION, to identify a unique window, you will only need > > > either window start or end, so you can simplify it, for example, to > > > L.window_start = R.window_start only. > > > 2. (not recommended), you can cut off **L.window_start = R.window_start > > > AND > > > L.window_end = R.window_end**, but add window metadata comparison to join > > > implicitly by execution engine. E.g. you can make up the join condition in > > > your JoinRel if two inputs are TUMBLE. > > > > > > > > > > > > -Rui > > > > > > > > > > > > > > > On Tue, Sep 22, 2020 at 10:27 PM Danny Chan <[email protected]> wrote: > > > > > > > Yes, the red part is **L.window_start = R.window_start AND L.window_end > > > > = > > > > R.window_end** > > > > > > > > > Is this a limitation for "triggered by the watermark of the stream”? > > > > > > > > No, because in most of the cases, there is no need to output the > > > > intermediate/partial join records then send retractions. > > > > > > > > > > > > So, how do you think about the condition syntax **L.window_start = > > > > R.window_start AND L.window_end = R.window_end** ? > > > > > > > > Best, > > > > Danny Chan > > > > 在 2020年9月23日 +0800 PM12:47，[email protected]，写道： > > > > > > > > > > L.window_start = R.window_start AND L.window_end = R.window_end > > > > > > > > > > > > > -- > > Viliam Durina > > Jet Developer > > hazelcast® > > > > <https://www.hazelcast.com> 2 W 5th Ave, Ste 300 | San Mateo, CA 94402 | > > USA > > +1 (650) 521-5453 | hazelcast.com <https://www.hazelcast.com> > > > > -- > > This message contains confidential information and is intended only for the > > individuals named. If you are not the named addressee you should not > > disseminate, distribute or copy this e-mail. Please notify the sender > > immediately by e-mail if you have received this e-mail by mistake and > > delete this e-mail from your system. E-mail transmission cannot be > > guaranteed to be secure or error-free as information could be intercepted, > > corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. > > The sender therefore does not accept liability for any errors or omissions > > in the contents of this message, which arise as a result of e-mail > > transmission. If verification is required, please request a hard-copy > > version. -Hazelcast
