Skip to content

Commit 54a5d33

Browse files
ShivamSharma43claude
authored andcommitted
fix: Avoid ON TRUE and multiple USING joins in the ClickHouse point-in-time join
ClickHouse's old analyzer (the default before 24.3, and still used with enable_analyzer = 0) rejects two joins in the point-in-time join template: - `ON TRUE AND subquery.k = entity_dataframe.k` fails with INVALID_JOIN_ON_EXPRESSION ("Illegal expression 'true' in JOIN ON section"), for every query. - The final SELECT joins each feature view back with USING, and with two or more feature views it fails with "Code: 48. Multiple USING statements are not supported". Join the entity keys with `ON k1 = k1 AND k2 = k2`, use CROSS JOIN for a feature view without entities (it only had `ON TRUE`), and join the feature views back with `ON "<fv>"."<fv>__entity_row_unique_id" = entity_dataframe."<fv>__entity_row_unique_id"`. Fixes feast-dev#6141 Co-Authored-By: Claude Opus 5.5 <[email protected]> Claude-Session: https://claude.ai/code/session_012XPuJeHi7A5CeHSKHjHmwx Signed-off-by: Shivam Sharma <[email protected]>
1 parent 171d601 commit 54a5d33

2 files changed

Lines changed: 110 additions & 3 deletions

File tree

‎sdk/python/feast/infra/offline_stores/contrib/clickhouse_offline_store/clickhouse.py‎

Lines changed: 8 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -532,11 +532,15 @@ def _append_alias(field_names: List[str], alias: str) -> List[str]:
532532
entity_dataframe.entity_timestamp,
533533
entity_dataframe."{{featureview.name}}__entity_row_unique_id"
534534
FROM "{{ featureview.name }}__subquery" AS subquery
535+
{% if featureview.entities %}
535536
INNER JOIN "{{ featureview.name }}__entity_dataframe" AS entity_dataframe
536-
ON TRUE
537+
ON
537538
{% for entity in featureview.entities %}
538-
AND subquery."{{ entity }}" = entity_dataframe."{{ entity }}"
539+
{% if not loop.first %}AND {% endif %}subquery."{{ entity }}" = entity_dataframe."{{ entity }}"
539540
{% endfor %}
541+
{% else %}
542+
CROSS JOIN "{{ featureview.name }}__entity_dataframe" AS entity_dataframe
543+
{% endif %}
540544
WHERE TRUE
541545
AND subquery.event_timestamp <= entity_dataframe.entity_timestamp
542546
@@ -625,6 +629,7 @@ def _append_alias(field_names: List[str], alias: str) -> List[str]:
625629
,"{% if full_feature_names %}{{ featureview.name }}__{{featureview.field_mapping.get(feature, feature)}}{% else %}{{ featureview.field_mapping.get(feature, feature) }}{% endif %}"
626630
{% endfor %}
627631
FROM "{{ featureview.name }}__cleaned"
628-
) AS "{{featureview.name}}" USING ("{{featureview.name}}__entity_row_unique_id")
632+
) AS "{{featureview.name}}"
633+
ON "{{featureview.name}}"."{{featureview.name}}__entity_row_unique_id" = entity_dataframe."{{featureview.name}}__entity_row_unique_id"
629634
{% endfor %}
630635
"""

‎sdk/python/tests/unit/infra/offline_stores/test_clickhouse.py‎

Lines changed: 102 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -240,3 +240,105 @@ def test_non_entity_mode_defaults_end_date(self):
240240
fv = self._make_feature_view("test_fv")
241241
job = self._call_get_historical_features([fv])
242242
assert isinstance(job, RetrievalJob)
243+
244+
245+
class TestPointInTimeJoinTemplate:
246+
"""Render the ClickHouse point-in-time join template and check the JOIN syntax.
247+
248+
ClickHouse's old analyzer (the default before 24.3) rejects ``ON TRUE AND ...``
249+
(INVALID_JOIN_ON_EXPRESSION) and more than one ``USING`` join in the same
250+
SELECT (Code: 48).
251+
"""
252+
253+
@staticmethod
254+
def _context(name, entities, created_timestamp_column=None):
255+
from feast.infra.offline_stores.offline_utils import FeatureViewQueryContext
256+
257+
return FeatureViewQueryContext(
258+
name=name,
259+
ttl=86400,
260+
entities=entities,
261+
features=[f"{name}_value"],
262+
field_mapping={},
263+
timestamp_field="event_timestamp",
264+
created_timestamp_column=created_timestamp_column,
265+
table_subquery=f"{name}_table",
266+
entity_selections=[f'"{entity}" AS "{entity}"' for entity in entities],
267+
min_event_timestamp="2025-01-01T00:00:00",
268+
max_event_timestamp="2025-01-02T00:00:00",
269+
date_partition_column=None,
270+
timestamp_field_type=None,
271+
)
272+
273+
@staticmethod
274+
def _render(contexts):
275+
from feast.infra.offline_stores import offline_utils
276+
from feast.infra.offline_stores.contrib.clickhouse_offline_store.clickhouse import (
277+
MULTIPLE_FEATURE_VIEW_POINT_IN_TIME_JOIN,
278+
)
279+
280+
entity_columns = {
281+
"driver_id": None,
282+
"customer_id": None,
283+
"event_timestamp": None,
284+
}
285+
query = offline_utils.build_point_in_time_query(
286+
contexts,
287+
left_table_query_string="entity_df_table",
288+
entity_df_event_timestamp_col="event_timestamp",
289+
entity_df_columns=entity_columns.keys(),
290+
query_template=MULTIPLE_FEATURE_VIEW_POINT_IN_TIME_JOIN,
291+
full_feature_names=False,
292+
)
293+
return " ".join(query.split())
294+
295+
@staticmethod
296+
def _final_select(query):
297+
# The last statement, after the per-feature-view CTEs.
298+
return query[query.index("Joins the outputs of multiple time travel joins") :]
299+
300+
def test_entity_join_has_no_on_true(self):
301+
query = self._render(
302+
[
303+
self._context("driver_stats", ["driver_id"], "created"),
304+
self._context("order_stats", ["driver_id", "customer_id"]),
305+
]
306+
)
307+
assert "ON TRUE" not in query
308+
assert (
309+
'AS entity_dataframe ON subquery."driver_id" = entity_dataframe."driver_id" '
310+
"WHERE TRUE"
311+
) in query
312+
assert (
313+
'AS entity_dataframe ON subquery."driver_id" = entity_dataframe."driver_id" '
314+
'AND subquery."customer_id" = entity_dataframe."customer_id" WHERE TRUE'
315+
) in query
316+
317+
def test_feature_views_are_joined_back_with_on_not_using(self):
318+
query = self._render(
319+
[
320+
self._context("driver_stats", ["driver_id"], "created"),
321+
self._context("customer_stats", ["customer_id"]),
322+
]
323+
)
324+
final_select = self._final_select(query)
325+
assert "USING" not in final_select
326+
assert final_select.count("LEFT JOIN") == 2
327+
for name in ("driver_stats", "customer_stats"):
328+
assert (
329+
f'AS "{name}" ON "{name}"."{name}__entity_row_unique_id" = '
330+
f'entity_dataframe."{name}__entity_row_unique_id"'
331+
) in final_select
332+
333+
def test_feature_view_without_entities_uses_cross_join(self):
334+
query = self._render(
335+
[
336+
self._context("driver_stats", ["driver_id"]),
337+
self._context("global_stats", []),
338+
]
339+
)
340+
assert "ON TRUE" not in query
341+
assert (
342+
'FROM "global_stats__subquery" AS subquery CROSS JOIN '
343+
'"global_stats__entity_dataframe" AS entity_dataframe WHERE TRUE'
344+
) in query

0 commit comments

Comments
 (0)