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

[polyglotAI-bot]

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

• ClickHouseCommand appends LIMIT 0/LIMIT 1 verbatim: CommandBehavior.SchemaOnly/SingleRow break on a trailing SQL comment or semicolon #471 / Fix CommandBehavior.SchemaOnly/SingleRow row limiting with a trailing comment or semicolon #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 Fix CommandBehavior.SchemaOnly/SingleRow row limiting with a trailing comment or semicolon #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.