ClickHouse offline store: ON TRUE and Multiple USING in JOIN not supported
- 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
Assessment
This issue has not been assessed yet.