dataengref errors

Home / Flink / Query compilation

Flink SQL: Temporal table join requires an equality condition on fields of table

org.apache.flink.table.api.TableExceptionTemporal table join requires an equality condition on fields of table [default_catalog.default_database.dim_customer].

A lookup join (FOR SYSTEM_TIME AS OF) needs at least one equality condition between the stream and the looked-up table, so the connector knows which key to fetch. The join condition only contains non-equality predicates, or compares expressions the planner cannot turn into lookup keys.

Causes · Fixes · Source

Common causes

  1. The ON clause has no equality on a column of the dimension table, for example only a range condition.How to confirm: The ON clause has no "dim.col = stream.col" term.
  2. The equality compares a function of the dimension column, such as CAST(dim.id AS STRING) = s.id.How to confirm: The dimension side of the equality is wrapped in a function or cast.

How to fix it

Add an equality on a plain dimension-table column, and move any transformation to the stream side.

SELECT o.*, d.name
FROM orders AS o
JOIN dim_customer FOR SYSTEM_TIME AS OF o.proc_time AS d
  ON d.id = CAST(o.customer_id AS BIGINT);

Where the message comes from

Message template in the source: Temporal table join requires an equality condition on fields of %s.

References

Related errors

Last checked: