ClickHouse / ClickHouse/ClickHouse.EntityFrameworkCore

Translate parameterized collection Contains to a native ClickHouse array parameter

Ouverte
#39 0 commentaires 0 réactions 0 personnes assignées Voir sur GitHub
enhancement
Langage dominant
C#
Étoiles
23
Forks
7
Merge moyen
14 j 3 h
PR mergées (30 j)
1

Description

## Summary

Translate `Contains` over a **parameterized** collection (a captured local `int[]`/`List`/etc.) into a single native ClickHouse **array parameter** — `column IN {ids:Array(T)}` (or `has({ids:Array(T)}, column)`) — instead of expanding it to one bound parameter per element.

This removes the parameter-count ceiling users hit on large `WHERE ... IN (...)` queries (see #38) and keeps query text small.

## Background / current behavior

For a captured collection, EF Core's relational pipeline currently expands the query to one parameter per element:

```sql
WHERE `p`.`Id` IN ({ids1:Int32}, {ids2:Int32}, … , {idsN:Int32})
```

Investigation findings (verified against real ClickHouse via Testcontainers, capturing the executed command with a `DbCommandInterceptor`):

- **`ParameterTranslationMode` is currently a no-op in this provider.** `MultipleParameters`, `Constant`, and `Parameter` all produce the identical `IN (p1 … pN)` SQL with N real driver parameters (confirmed at 5 and 12,000 ids). A captured collection is not a ClickHouse `Array` column, so the existing `has(...)` translation in `ClickHouseArrayMethodTranslator` does not fire, and EF falls back to its legacy multiple-parameter expansion, where the mode has no effect.
- The provider's `ValuesExpression` / `SELECT … UNION ALL` rewrite only covers **inline** literal collections, not parameterized ones.
- A large list therefore sends N parameters; on some servers/proxies this hits a limit around ~10k (the OSS container in testing accepted 12k, so the exact ceiling is environment-specific — `max_query_size`, server profile, or a proxy).

## Why the array approach is the right fix

Verified working end-to-end via the raw driver — a single array parameter executes correctly:

```sql
SELECT count() FROM param_rows WHERE Id IN {ids:Array(Int32)}
-- p.Value = int[12000] → 1 parameter, 12000 rows matched
```

ClickHouse.Driver's parameter formatter serializes arrays natively, so this needs no driver changes. Benefits:

- **No parameter-count ceiling** — one parameter regardless of collection size.
- **Small query text** — avoids `max_query_size` pressure that both `MultipleParameters` and `Constant` (inline) can hit.
- ClickHouse is OLAP and does not reuse query plans by parameterization, so there is no downside to a single bound array vs. inlined constants.

## Proposed implementation

Scope is isolated to the query pipeline:

1. Detect `Contains` where the source is a **collection parameter** (not a column, not an inline collection) in `ClickHouseSqlTranslatingExpressionVisitor` / `ClickHouseArrayMethodTranslator`.
2. Emit an `Array(T)`-typed `SqlParameterExpression` and render `column IN {p:Array(T)}` (or `has({p:Array(T)}, column)`) in `ClickHouseQuerySqlGenerator`.
3. Serialize the array parameter with the element's store type (the existing `ClickHouseArrayTypeMapping.ElementMapping` alignment used by the column-based `has()` path covers the element-type concern).
4. Ideally route EF Core's `ParameterTranslationMode.Parameter` through this path so the standard knob works as documented.

## Tests

- Multiple element types (Int32/Int64/String/Guid/Enum/etc.).
- Empty-collection semantics (must match `WHERE 1=0` / no rows, consistent with EF).
- Large collection (e.g. 50k) sends a single parameter and executes.
- `MultipleParameters` and `Constant` modes still behave correctly (and document what each emits).

## Notes

- This is the EF-idiomatic answer to large `Contains` and supersedes the `UseParameterizedCollectionMode(Constant)` workaround discussed in #38.
- Related: #38.

Guide de contribution

Aucun guide de contribution indexé pour ce dépôt

Piste de recherche

Commencez dans ClickHouseSqlTranslatingExpressionVisitor et ClickHouseArrayMethodTranslator, en examinant en quoi Contains paramétré diffère de la traduction has() existante basée sur les colonnes. Examinez ensuite ClickHouseQuerySqlGenerator et ClickHouseArrayTypeMapping pour le rendu des paramètres de tableau et des types d’éléments. Le travail est terminé lorsque les collections prises en charge produisent un seul paramètre Array(T), que les collections vides préservent la sémantique d’EF et que les tests indiqués de type, de mode, de grandes collections et d’exécution passent.

Rédigé par le modèle d'indexation à partir du texte de l'issue.

Évaluation

Stack technique
clickhouse, csharp
Domaine
database
Type d'issue
Fonctionnalité
Difficulté
5/5
Temps estimé
Plus d'une semaine
Activité
Calme
Clarté
Plutôt claire
Accessibilité débutants
48/100

Recevez les nouvelles issues par e-mail

Un résumé court des issues GitHub adaptées aux débutants.