Tracking: ASOF JOIN

[lmatz]

Background

We have a few users whose workload has the need to do the following:

For each row in the first (or left) table, the join finds a single row in the second (or right) table that has the closest timestamp value.

The qualifying row on the right side is the closest match, which could be equal in time, earlier in time, or later in time, depending on the specified comparison operator.

This is exactly the semantics of ASOF JOIN.

It has been supported by a few popular DBs such as snowflake, duckdb, questdb, clickhouse, kdb.

https://duckdb.org/2023/09/15/asof-joins-fuzzy-temporal-lookups

Tracking

• Exeuctor: feat(streaming): introduce streaming AsOf JOIN executor #18242
• Frontend: feat(streaming): plan asof join #18683
• Watermark for inequality key in AsOf join #18503
• Batch version

[lmatz]

Repost @st1page's dubkdb examples on Slack:

# ok
select * from t1 asof join t2 on t1.ts < t2.ts and t1.k=t2.k;

# ok, it does not rely on the columns's datatype
select * from t1 asof join t2 on t1.ts = t2.ts and t1.k>t2.k;

# ok
select * from t1 asof join t2 on t1.k=t2.k and t1.ts + INTERVAL 5 minute > date_trunc('hour', t2.ts);

# error, only support one condition
select * from t1 asof join t2 on t1.ts < t2.ts and t1.k>t2.k;
Binder Error: Multiple ASOF JOIN inequalities

# error 
select * from t1 asof join t2 where t1.k=t2.k and t1.ts > t2.ts;
Parser Error: syntax error at or near "where"
LINE 1: select * from t1 asof join t2 where t1.k=t2.k and t1.ts > t2.ts;

# error
select * from t1 asof join t2 on t1.k = t2.k where t1.ts > t2.ts;
Binder Error: Missing ASOF JOIN inequality

[lmatz]

QuestDB has both ASOF Join (can be equal) and LT(strictly prior to) Join.
Other DBs use the comparison_operator to specify two use cases.

KDB differentiates the use cases by https://code.kx.com/q/basics/joins/#as-of-joins aj, aj0, ajf, ajf0.
0 is essentially ASOF LEFT JOIN in Clickhouse.