ClickHouse / ClickHouse/ClickHouse.EntityFrameworkCore

Translate parameterized collection Contains to a native ClickHouse array parameter

Đang mở
#39 0 bình luận 0 reaction 0 người được giao Xem trên GitHub
enhancement
Ngôn ngữ chính
C#
Star
23
Fork
7
Merge trung bình
14 ngày 3 giờ
Pull request đã merge (30 ngày)
1

Mô tả

## 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.

Hướng dẫn đóng góp

Chưa lập chỉ mục được hướng dẫn đóng góp cho kho mã nguồn này

Hướng nghiên cứu

Start in ClickHouseSqlTranslatingExpressionVisitor and ClickHouseArrayMethodTranslator, tracing how parameterized Contains differs from the existing column-based has() translation. Then inspect ClickHouseQuerySqlGenerator and ClickHouseArrayTypeMapping for array parameter rendering and element types. Done means supported collections emit one Array(T) parameter, empty collections preserve EF semantics, and the listed type, mode, large-collection, and execution tests pass.

Do mô hình lập chỉ mục viết ra từ nội dung của issue.

Đánh giá

Công nghệ
clickhouse, csharp
Lĩnh vực
database
Loại issue
Tính năng
Độ khó
5/5
Thời gian dự kiến
Hơn một tuần
Mức độ hoạt động
Ít trao đổi
Độ rõ ràng
Khá rõ ràng
Mức phù hợp với người mới
48/100

Nhận issue mới trong hộp thư của bạn

Bản tóm tắt ngắn những issue GitHub phù hợp với người mới.