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

Open
  • Is something wrong?
#520 0 comments 0 reactions 1 assignee View on GitHub

Maintainers usually reply within 1 day

@polyglotAI-bot is already working on this.

Since Aug 7, 2026.

  • #691 by @bjsneto — open

Assessment

This issue has not been assessed yet.

Description

bug
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):

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 ❌ 
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:

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<byte>(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.

Dominant language
C#
Stars
100
Forks
25
Avg merge
1d 2h
Merged PRs (30d)
8

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from ClickHouse/clickhouse-cs

All issues in ClickHouse/clickhouse-cs

Similar issues

More C# issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.