[CALCITE-6372] Support ASOF joins

• Type: New Feature
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 1.36.0
• Fix Version/s: 1.38.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Mihai Budiu
• Reporter: Mihai Budiu
• Created: 16/Apr/24 17:33
• Updated: 17/Oct/24 22:41
• Resolved: 17/Oct/24 22:41

Description

Seems that this new kind of JOIN named AS OF is very useful for processing time-series data. Here is some example documentation from Snowflake: https://docs.snowflake.com/en/sql-reference/constructs/asof-join

The semantics is similar to a traditional join, but the result always contains at most one record from the left side, with the last​ matching record on the right side (where "time" is any value that can be compared for inequality). This can be expressed in SQL, but it looks very cumbersome, using a JOIN, a GROUP BY, and then an aggregation to keep the last value.

Issue Links

• relates to: CALCITE-6540 RelOptUtil.pushDownJoinConditions does not correctly adjust ASOF joins match conditions
• links to: GitHub Pull Request #3883