ClickHouseCommand appends LIMIT 0/LIMIT 1 verbatim: CommandBehavior.SchemaOnly/SingleRow break on a trailing SQL comment or semicolon

[claude]

Description

ClickHouseCommand.ExecuteDbDataReaderAsync implements CommandBehavior.SchemaOnly and CommandBehavior.SingleRow by textually appending a clause to the user's CommandText:

https://github.com/ClickHouse/clickhouse-cs/blob/main/ClickHouse.Driver/ADO/ClickHouseCommand.cs#L196-L207

var sqlBuilder = new StringBuilder(CommandText);
switch (behavior)
{
    case CommandBehavior.SingleRow:
        sqlBuilder.Append(" LIMIT 1");
        break;
    case CommandBehavior.SchemaOnly:
        sqlBuilder.Append(" LIMIT 0");
        break;

Because the append is verbatim, any trailing comment or statement terminator in CommandText corrupts the resulting query. Two distinct symptoms:

1. Trailing single-line comment (-- ... or # ...) — silent wrong results. The appended clause lands inside the comment, so the server never sees it. SELECT 13 AS a -- note becomes SELECT 13 AS a -- note LIMIT 0, which executes as an unbounded query. SchemaOnly returns data rows instead of metadata only, and SingleRow returns the full result set. No exception is raised — the behavior contract of CommandBehavior is silently violated, which is worse than an error for callers (including ORMs) that rely on SingleRow to bound the read.
2. Trailing semicolon — SYNTAX_ERROR (code 62). SELECT 13 AS a; becomes SELECT 13 AS a; LIMIT 0, which the server rejects with DB::Exception: Syntax error (Multi-statements are not allowed) ... failed at position 15 (end of query): ; LIMIT 0. A trailing ; is accepted on every other execution path of this driver, so this is a behavior discontinuity that only appears when a CommandBehavior is passed.

The ; + comment combination (SELECT 13 AS a; -- note) hits case 1.

Note that column metadata itself is fine in the comment cases (FieldCount/GetName are correct, since metadata comes from the RowBinaryWithNamesAndTypes response rather than from a re-query) — what breaks is the row limiting.

ClickHouse server version

26.7.1.1315 (official build), reached over HTTP at localhost:8123. Verified against a running server.

Reproduction

Scratch NUnit fixture placed in ClickHouse.Driver.Tests/SQL/:

using System.Data;
using System.Threading.Tasks;
using NUnit.Framework;

namespace ClickHouse.Driver.Tests.SQL;

public class TrailingCommentCommandBehaviorTests : AbstractConnectionTestFixture
{
    [TestCase("SELECT 13 AS a", TestName = "plain")]
    [TestCase("SELECT 13 AS a -- trailing comment", TestName = "line_comment")]
    [TestCase("SELECT 13 AS a # trailing comment", TestName = "hash_comment")]
    [TestCase("SELECT 13 AS a;", TestName = "semicolon")]
    [TestCase("SELECT 13 AS a; -- trailing comment", TestName = "semicolon_comment")]
    public async Task ExecuteReaderAsync_SchemaOnlyWithTrailingCommentOrSemicolon_ReturnsNoRows(string sql)
    {
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        using var reader = await command.ExecuteReaderAsync(CommandBehavior.SchemaOnly);
        int rows = 0;
        while (await reader.ReadAsync())
            rows++;
        Assert.That(rows, Is.Zero, "SchemaOnly must return no rows");
    }

    [TestCase("SELECT * FROM numbers(10)", TestName = "plain")]
    [TestCase("SELECT * FROM numbers(10) -- trailing comment", TestName = "line_comment")]
    [TestCase("SELECT * FROM numbers(10);", TestName = "semicolon")]
    public async Task ExecuteReaderAsync_SingleRowWithTrailingCommentOrSemicolon_ReturnsOneRow(string sql)
    {
        using var command = connection.CreateCommand();
        command.CommandText = sql;
        using var reader = await command.ExecuteReaderAsync(CommandBehavior.SingleRow);
        int rows = 0;
        while (await reader.ReadAsync())
            rows++;
        Assert.That(rows, Is.EqualTo(1), "SingleRow must return exactly one row");
    }
}

dotnet test ClickHouse.Driver.Tests --framework net9.0 --filter "FullyQualifiedName~TrailingCommentCommandBehavior" →
Failed: 6, Passed: 2, Total: 8 (only the two plain cases pass).

Expected vs. actual:

CommandText	behavior	expected rows	actual
SELECT 13 AS a	SchemaOnly	0	0 (pass)
SELECT 13 AS a -- trailing comment	SchemaOnly	0	1 row returned
SELECT 13 AS a # trailing comment	SchemaOnly	0	1 row returned
SELECT 13 AS a;	SchemaOnly	0	ClickHouseServerException code 62
SELECT 13 AS a; -- trailing comment	SchemaOnly	0	1 row returned
SELECT * FROM numbers(10)	SingleRow	1	1 (pass)
SELECT * FROM numbers(10) -- trailing comment	SingleRow	1	10 rows returned
SELECT * FROM numbers(10);	SingleRow	1	ClickHouseServerException code 62

Exact server error for the semicolon cases:

Code: 62. DB::Exception: Syntax error (Multi-statements are not allowed): failed at position 15
(end of query): ; LIMIT 0. . (SYNTAX_ERROR) (version 26.7.1.1315 (official build))

Suggested fix

In ClickHouse.Driver/ADO/ClickHouseCommand.cs:196-207, normalize CommandText before appending the clause: strip trailing whitespace, trailing statement terminators (;), and trailing comments (-- ..., # ... to end of line, and /* ... */), repeating until stable, then append " LIMIT n". The stripper must be string/quoted-identifier aware so a ;, --, # or /* inside '...', "..." or `...` is not mistaken for a terminator/comment — the repo already has a string-and-comment-aware scanner for the parameter path (SqlParameterTypeExtractor), so that logic could be shared. Alternatively, avoid text rewriting entirely and send the limit as a server setting (limit=0/limit=1 alongside the existing query settings), which is immune to trailing trivia.

Consider whether a trailing ; alone should also be tolerated on this path for consistency with the other execution paths.

Link

Analogous defect reported for clickhouse-connect (same root cause — SQL text manipulation that ignores trailing comments/semicolons — in that client's DB-API metadata re-query wrap): ClickHouse/clickhouse-connect#907

Tracking: https://github.com/ClickHouse/integrations-ai-playground/issues/325