ClickHouse / ClickHouse/clickhouse-cs

ClickHouseCommand: CommandBehavior matched by equality, so SchemaOnly/SingleRow combined with any other flag applies no row limit

Open
#520 0 comments 0 reactions 1 assignee Claimed by @polyglotAI-bot View on GitHub
bug
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

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.