[backfill: ClickHouse/clickhouse-connect] Tuple(Nullable(...)) parameter with None fails: backslash-N rendered inside tuple

[alex-clickhouse]

Target client repo

ClickHouse/clickhouse-connect

Severity

sev:2 - visible error (server rejects the parameter with CANNOT_PARSE_QUOTED_STRING), not silent corruption. Affects a real but not-most-common type pattern (Tuple(Nullable(...)) containing None via the typed external parameter syntax {name:Type}). The only client-side workaround is to switch parameter styles (away from {name:Type} external bind to %(name)s substitution), which forfeits server-side typed binding - not a one-line flag flip, so it fits the sev:2 rubric.

Description

When using the typed external parameter syntax {name:Tuple(..., Nullable(T))} with a Python tuple containing None, clickhouse-connect renders the None as \N inside the bound tuple value (e.g. ('aaa',\N)). The ClickHouse server cannot parse \N as a Nullable(T) element inside a Tuple(...) parameter and rejects the request with CANNOT_PARSE_QUOTED_STRING.

Root cause is in clickhouse_connect/driver/binding.py. format_bind_value (the path used for external {name:Type} parameters) returns \N for None regardless of nesting depth:

def format_bind_value(value, server_tz=timezone.utc, top_level=True):
    ...
    if value is None:
        return "\\N"
    ...
    if isinstance(value, tuple):
        return f"({', '.join(recurse(x) for x in value)})"

\N is the TSV/text-format wire encoding for NULL at the top level of a scalar parameter, but it is not accepted inside a Tuple(...) literal that the server parses for typed query parameters. The server expects NULL (the SQL keyword) there, the way format_query_value already emits for the non-external substitution path (if value is None: return "NULL" at line 171-172 of the same file).

The same defect applies to Array(Tuple(..., Nullable(T))) and any other nested container holding None.

ClickHouse server version

Tested against 26.4.2.10 (local server reachable at http://localhost:8123).

Reproduction

import clickhouse_connect

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

# Case 1: Tuple(Nullable) directly as a parameter
c.command(
    "SELECT t.2 FROM (SELECT arrayJoin([{tup:Tuple(String, Nullable(String))}]) t)",
    parameters={"tup": ("aaa", None)},
)

# Case 2: Array(Tuple(Nullable))
c.command(
    "SELECT t.2 FROM (SELECT arrayJoin({tup:Array(Tuple(String, Nullable(String)))}) t)",
    parameters={"tup": [("aaa", None)]},
)

Expected: both queries succeed and return NULL.

Actual: both fail with the same server error class:

DatabaseError: Received ClickHouse exception, code: 26, server response:
Code: 26. DB::Exception: Cannot parse quoted string: expected opening quote ''', got '\':
value ('aaa', \N) cannot be parsed as Tuple(String, Nullable(String))
for query parameter 'tup'. (CANNOT_PARSE_QUOTED_STRING)

DatabaseError: Received ClickHouse exception, code: 26, server response:
Code: 26. DB::Exception: Cannot parse quoted string: expected opening quote ''', got '\':
value [('aaa', \N)] cannot be parsed as Array(Tuple(String, Nullable(String)))
for query parameter 'tup'. (CANNOT_PARSE_QUOTED_STRING)

Suggested fix

In clickhouse_connect/driver/binding.py::format_bind_value, when value is None and we are not at top level (i.e., the None lives inside a Tuple, Array, Map, etc.), emit NULL instead of \N. The recurse(...) helper already passes top_level=False, so the existing scaffolding makes this a one-line change in the None branch:

if value is None:
    return "\\N" if top_level else "NULL"

A test should cover at least Tuple(String, Nullable(String)), Array(Tuple(String, Nullable(String))), and Map(String, Nullable(String)) with None values in the typed external-bind path.

Source bug

Relayed from ClickHouse/clickhouse-js#374