ClickHouse / ClickHouse/clickhouse-cs
ClickHouseCommand: CommandBehavior matched by equality, so SchemaOnly/SingleRow combined with any other flag applies no row limit
- Dominant language
- C#
- Stars
- 94
- Forks
- 22
- Avg merge
- 11h 26m
- Merged PRs (30d)
- 22
Description
### Describe the bug
`ClickHouseCommand.ExecuteDbDataReaderAsync` decides whether to append `LIMIT 0` / `LIMIT 1` with an **exact-equality** switch on `CommandBehavior` (`ClickHouse.Driver/ADO/ClickHouseCommand.cs:197-207`, on `main` @ `d5ae56c`):
```csharp
switch (behavior)
{
case CommandBehavior.SingleRow: sqlBuilder.Append(" LIMIT 1"); break;
case CommandBehavior.SchemaOnly: sqlBuilder.Append(" LIMIT 0"); break;
default: break;
}
```
`System.Data.CommandBehavior` is a `[Flags]` enum, and ADO.NET consumers routinely pass **combinations**. Any combination fails the equality test, falls to `default`, and **no row limit is applied at all**:
* `SchemaOnly | KeyInfo` (what `DbDataAdapter.FillSchema` sends) executes the query and returns all data rows, even though `SchemaOnly` means the query should not be executed.
* `SingleRow | SequentialAccess | SingleResult` (what **Dapper**'s `QueryFirst`/`QueryFirstOrDefault` sends) streams the entire result set instead of `LIMIT 1`.
There is no error and no warning — the limit is silently dropped. Since the ADO.NET layer exists specifically for ORM compatibility (Dapper / EF Core / linq2db, per `AGENTS.md`), the combined-flag form is the *common* case for those consumers, not an edge case.
### Steps to reproduce
1. Point the driver at any ClickHouse server.
2. Run a reader with a bare flag and then with the same flag OR'd with any other flag, and count rows.
3. Or: call Dapper's `QueryFirstOrDefaultAsync` on a query whose later rows fail, and observe the failure.
### Expected behaviour
The flags should be tested as flags: `SchemaOnly` should suppress rows and `SingleRow` should limit to one row regardless of which additional, orthogonal flags (`KeyInfo`, `SequentialAccess`, `SingleResult`, `CloseConnection`) accompany them. `SchemaOnly` should take precedence over `SingleRow` when both are present.
### Code example
Row counts against `SELECT number FROM numbers(5)` (NUnit, `net10.0`, driver built from `main` @ `d5ae56c`):
| behavior | numeric value | rows returned | expected |
| --- | --- | --- | --- |
| `SchemaOnly` | 2 | 0 | 0 ✅ |
| `SingleRow` | 8 | 1 | 1 ✅ |
| `SchemaOnly \| KeyInfo` | 6 | **5** | 0 ❌ |
| `SchemaOnly \| SequentialAccess` | 18 | **5** | 0 ❌ |
| `SchemaOnly \| SingleResult` | 3 | **5** | 0 ❌ |
| `SingleRow \| SequentialAccess` | 24 | **5** | 1 ❌ |
| `SingleRow \| CloseConnection` | 40 | **5** | 1 ❌ |
```csharp
using var command = connection.CreateCommand();
command.CommandText = "SELECT number FROM numbers(5)";
using var reader = await command.ExecuteReaderAsync(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo);
var rows = 0;
while (await reader.ReadAsync())
rows++;
// rows == 5; SchemaOnly should have produced 0
```
A second probe proves the `LIMIT` never reaches the server (and shows the ORM impact). `throwIf` fires only if the server reads past row 0:
```csharp
const string probe = "SELECT throwIf(number = 3, 'boom') FROM numbers(5)";
// passes: LIMIT 1 is appended, the server never reads row 3
await command.ExecuteReaderAsync(CommandBehavior.SingleRow);
// throws Code 395 FUNCTION_THROW_IF_VALUE_IS_NON_ZERO: no LIMIT was appended
await command.ExecuteReaderAsync(CommandBehavior.SingleRow | CommandBehavior.SequentialAccess);
// throws the same: Dapper 2.1.79 sends a flag combination for QueryFirst
await connection.QueryFirstOrDefaultAsync(probe);
```
### Error log
```
ClickHouse.Driver.ClickHouseServerException : Code: 395. DB::Exception: boom: while executing
'FUNCTION throwIf(equals(__table1.number, 3_UInt8) :: 0, 'boom'_String :: 2) -> throwIf(...) UInt8 : 1'.
(FUNCTION_THROW_IF_VALUE_IS_NON_ZERO) (version 26.5.1.882 (official build))
at ClickHouse.Driver.ADO.ClickHouseCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
```
### Suggested fix
Match on the flag bits rather than the whole value, in `ExecuteDbDataReaderAsync`:
* `behavior.HasFlag(CommandBehavior.SchemaOnly)` → `LIMIT 0`, checked **first** so it wins over `SingleRow`;
* else `behavior.HasFlag(CommandBehavior.SingleRow)` → `LIMIT 1`;
* else no limit.
Contrast case that must keep its current behavior: `CommandBehavior.Default` (0) must send `CommandText` verbatim — note `HasFlag` returns `true` for a zero flag, so `Default` has to stay an exact/zero check rather than a `HasFlag` test.
Worth pinning in tests: each bare flag, each of the combinations above, `SchemaOnly | SingleRow` (SchemaOnly wins), and `Default`.
### Related
* #471 / #473 concern the *same* two behaviors but a different root cause (the `LIMIT` being appended verbatim after a trailing comment or semicolon). This defect is independent of that one and is not addressed by #473, which preserves the equality match. If both land, the flag check and the append mechanism are separate changes.
### Configuration
#### Environment
* Client version: built from `main` @ `d5ae56c`
* Language version: C# / .NET SDK 10.0.203
* .NET version: `net10.0` test target (the code path is framework-independent)
* OS: Ubuntu 24.04 (container)
#### ClickHouse server
* ClickHouse Server version: 26.5.1.882 (official build, Docker)
* ClickHouse Server non-default settings, if any: none
* `CREATE TABLE` statements for tables involved: none — reproduction uses the `numbers()` table function
* Sample data: n/a
---
Found by automated analysis of this client while working on #471, and verified against a live ClickHouse server (not by code inspection alone). Filed for triage; no fix has been pushed.
Contributor guide
Assessment
This issue has not been assessed yet.