Left join for versioned-kv streams about proton HOT 5 CLOSED

In my mind, version_kv <left> join version_kv currently has two scenarios:
one is with aggregation, we need to output changelog results
the other is without aggregation,
i. If the target table is version_kv, we only need to join to output the intermediate results (without changelog)
ii. If the target table is changelog_kv, we still output changelog results

from proton.

For syntax:

--- case 1, with aggregation , the join result is changelog, and the aggr result is append-only
select id, max(v) from kv1 join kv2 using(id);

--- case 2, with aggregation, the join result is changelog, and the aggr result is changelog
select id, max(v), _tp_delta from kv1 join kv2 using(id) emit changelog;

--- case 3, without aggregation, the join result is append-only
select id, kv1.v, kv2.v from kv1 [left] join kv2 using(id);

--- case 4, without aggregation, the join result is changelog
select id, kv1.v, kv2.v, _tp_delta from kv1 [left] join kv2 using(id) emit changelog

from proton.

In my mind, version_kv <left> join version_kv currently has two scenarios: one is with aggregation, we need to output changelog results the other is without aggregation, i. If the target table is version_kv, we only need to join to output the intermediate results (without changelog) ii. If the target table is changelog_kv, we still output changelog results

Right, we just need support another emit strategy like EMIT UPSERT, it's users responsibility to pick the right emit strategy. For aggregation, we pick changelog for them, for plain join, we pick changelog by default as well but user can override it with EMIT UPSERT ? We don't need consider target stream since that will be very complex and some times it is hard like an external target table (we don't know what it is). This seems good enough ?

from proton.

I don't like to support another emit strategy EMIT UPSERT, which is not a common strategy and is just used in versioned_kv join versioned_kv.

Introducing a special emit strategy will make it more complex and difficult for users to get started
I prefer that the default is append-only unless emit changelog is specified.

@chenziliang

from proton.

Make sense to me

from proton.