[backfill: ClickHouse/clickhouse-connect] Trailing whitespace/comment after a query-final semicolon defeats rstrip and breaks FORMAT append

[alex-clickhouse]

Target client repo

ClickHouse/clickhouse-connect

Severity

sev:1 — visible server-side error (Code 62 SYNTAX_ERROR: Multi-statements are not allowed), not silent corruption. Triggers only when the user puts trailing whitespace, a newline, a -- comment, or a /* */ comment after a query-terminating ;. The trivial workaround is "strip your own trailing junk", and the simple SELECT 1; case is already handled. Per the rubric this is "easy client-side workaround" and an "obscure / opt-in surface", hence sev:1.

Description

When the HTTP client builds the final body it appends a FORMAT clause:

• clickhouse_connect/driver/binding.py:44 in finalize_query: query = query.rstrip(";")
• clickhouse_connect/driver/binding.py:93 in bind_query: same query.rstrip(";")
• clickhouse_connect/driver/httpclient.py:245 in HttpClient._prep_query: fmt = f"\n FORMAT {self._read_format}" then final_query + fmt
• clickhouse_connect/driver/httpclient.py:262 for the JSON-stats path: f"{context.final_query}\n FORMAT JSON"

rstrip(";") only strips ; characters that sit at the very end of the string. If anything follows the final ; (whitespace, newline, line comment, block comment) the ; survives the strip and the appended FORMAT ... lands as a second statement. ClickHouse's HTTP interface rejects it with Code 62: Multi-statements are not allowed.

The original closed issue #376 in the target repo covered only the bare SELECT 1; case, which the rstrip(";") does handle. The broader pattern called out in the source bug (ClickHouse/clickhouse-rs#268 and ClickHouse/clickhouse-rs#267) is still present.

What works in clickhouse-connect:

• SELECT 1; -> stripped, OK.
• SELECT 1 --tail -> OK, the leading \n before FORMAT terminates the line comment.
• SELECT 1 /* tail */ -> OK, block comment is closed before FORMAT.

What fails:

• SELECT 1;\n
• SELECT 1; (trailing space after ;)
• SELECT 1; -- tail
• SELECT 1; /* tail */

In every failing case the server response is Code: 62. DB::Exception: Syntax error (Multi-statements are not allowed): failed at position 9 (end of query) (line 1, col 9): ;... FORMAT ....

ClickHouse server version

26.4.2.10, verified locally against http://localhost:8123 via the clickhouse-connect Python client.

Reproduction

import clickhouse_connect

c = clickhouse_connect.get_client(host="localhost", port=8123)

# Spy on what is actually sent.
orig = c._prep_query
def spy(ctx):
    out = orig(ctx)
    print("FINAL SENT:", repr(out))
    return out
c._prep_query = spy

def case(label, q):
    print(f"--- {label}: input={q!r} ---")
    try:
        r = c.query(q)
        print("OK", r.result_rows)
    except Exception as e:
        print("ERR", type(e).__name__, str(e)[:200])

case("bare trailing semi (works today)", "SELECT 1;")
case("trailing newline after semi", "SELECT 1;\n")
case("trailing space after semi", "SELECT 1; ")
case("semi then line comment", "SELECT 1; -- tail")
case("semi then block comment", "SELECT 1; /* tail */")

Observed output (key lines):

--- bare trailing semi (works today): input='SELECT 1;' ---
FINAL SENT: 'SELECT 1\n FORMAT Native'
OK [(1,)]
--- trailing newline after semi: input='SELECT 1;\n' ---
FINAL SENT: 'SELECT 1;\n\n FORMAT Native'
ERR DatabaseError ... Code: 62 ... Multi-statements are not allowed ...
--- trailing space after semi: input='SELECT 1; ' ---
FINAL SENT: 'SELECT 1; \n FORMAT Native'
ERR DatabaseError ... Code: 62 ... Multi-statements are not allowed ...
--- semi then line comment: input='SELECT 1; -- tail' ---
FINAL SENT: 'SELECT 1; -- tail\n FORMAT Native'
ERR DatabaseError ... Code: 62 ... Multi-statements are not allowed ...
--- semi then block comment: input='SELECT 1; /* tail */' ---
FINAL SENT: 'SELECT 1; /* tail */\n FORMAT Native'
ERR DatabaseError ... Code: 62 ... Multi-statements are not allowed ...

Expected: all of the above should succeed, mirroring the server's own acceptance of ; and trailing comments at the query tail and matching the behavior the clickhouse-rs fix codified.

Suggested fix

The two rstrip(";") calls in clickhouse_connect/driver/binding.py need to be a "trim trailing whitespace, line comments, block comments, and one or more ;" loop, applied until the tail is stable. Roughly: strip ASCII whitespace, then if the suffix is --...\n or /* ... */ strip the comment, then strip any trailing ;, repeat until nothing changes. The sync and async paths share bind_query / finalize_query, so a single fix in binding.py covers both. The same trim should run before httpclient.py:245 and the \n FORMAT JSON path at httpclient.py:262 so the appended FORMAT always lands directly after the last real statement token.

Source bug

ClickHouse/clickhouse-rs#268 (fix in ClickHouse/clickhouse-rs#267)

[joe-clickhouse]

Keeping this open after closing #906. The bug is valid, but the replacement needs linear parsing, nested-comment handling, and support for ClickHouse comment forms. Also note that the source references in the original report have also moved from httpclient.py to _backend/httpcommon.py.