feast-dev / feast-dev/feast

ClickHouse offline store: ON TRUE and Multiple USING in JOIN not supported

Open
#6,141 1 comment 18 reactions 0 assignees View on GitHub
kind/bug priority/p2
Dominant language
Python
Stars
7.3k
Forks
1.4k
Avg merge
1d 21h
Merged PRs (30d)
15

Description

## Expected Behavior

`get_historical_features()` with ClickHouse offline store should work when querying from multiple FeatureViews.

## Current Behavior

Two SQL compatibility issues in the `MULTIPLE_FEATURE_VIEW_POINT_IN_TIME_JOIN` template:

1. **`ON TRUE` in JOIN** → ClickHouse error `INVALID_JOIN_ON_EXPRESSION`. Affects all queries (single and multi-FV).
2. **Multiple `USING` clauses** → ClickHouse error `Code: 48. Multiple USING statements are not supported`. Affects queries with 2+ FeatureViews.

## Steps to reproduce

```python
# Query features from 2+ FeatureViews
retrieval_job = store.get_historical_features(
entity_df=entity_df,
features=[
"feature_view_1:feature_a",
"feature_view_2:feature_b",
],
)
df = retrieval_job.to_df() # raises DatabaseError
```

### Specifications

- Version: 0.61.0 (also present on `master`)
- Platform: any
- Subsystem: ClickHouse contrib offline store

## Possible Solution

File: `sdk/python/feast/infra/offline_stores/contrib/clickhouse_offline_store/clickhouse.py`

**Fix 1** — `ON TRUE` → conditional `ON`/`AND` (~line 532):

Before:
```jinja
ON TRUE
{% for entity in featureview.entities %}
AND subquery."{{ entity }}" = entity_dataframe."{{ entity }}"
{% endfor %}
```

After:
```jinja
{% for entity in featureview.entities %}
{% if loop.first %}ON{% else %}AND{% endif %} subquery."{{ entity }}" = entity_dataframe."{{ entity }}"
{% endfor %}
```

**Fix 2** — `USING` → `ON` in final SELECT (~line 620):

Before:
```jinja
) AS "{{featureview.name}}" USING ("{{featureview.name}}__entity_row_unique_id")
```

After:
```jinja
) AS "{{featureview.name}}" ON "{{featureview.name}}"."{{featureview.name}}__entity_row_unique_id" = entity_dataframe."{{featureview.name}}__entity_row_unique_id"
```

Both use standard SQL compatible with ClickHouse and PostgreSQL.

I'm attaching the patched file with both fixes applied.
[clickhouse.py](https://github.com/user-attachments/files/26179225/clickhouse.py)

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.