queryBind rejects null/undefined params instead of binding SQL NULL (diverges from @clickhouse/client)

[dfrankland]

Summary

Passing a null (or undefined) query parameter to queryBind / Session.queryBind throws a ChdbBindError, even when the placeholder type is Nullable(...). The reference client, @clickhouse/client, accepts null and binds it as SQL NULL. Since chdb-node aims to be a byte-compatible @clickhouse/client façade (see #49, #52), this is a compatibility gap, not an engine limitation.

Reproduction

const { queryBind } = require('chdb');

// Throws: ChdbBindError "null/undefined parameter values are not supported; ..."
queryBind('SELECT {v:Nullable(String)} AS v', { v: null }, 'JSONEachRow');

With @clickhouse/client (HTTP), the equivalent query succeeds and returns { v: null }.

Root cause

src/serialize.ts → formatParamValue explicitly rejects null/undefined:

if (value === null || value === undefined) {
  throw new ChdbBindError(
    'null/undefined parameter values are not supported; omit the parameter or use a typed NULL in SQL',
  )
}

@clickhouse/client-common's formatQueryParams does the opposite — it serializes null as the TSV null marker \N at the top level (and as the NULL keyword when nested inside an Array/Tuple/Map):

if (value === null || value === undefined) {
  if (printNullAsKeyword) return "NULL";
  return "\\N";
}

This is a chdb-node divergence, not an engine limitation

The chdb native bind path (chdb_query_with_params) already accepts \N as SQL NULL. Verified against the built addon:

param	placeholder	result
\N	Nullable(String)	null (IS NULL = 1)
\N	Nullable(Int64)	null (IS NULL = 1)
[1,NULL,3]	Array(Nullable(Int64))	[1, null, 3]
literal "NULL"	Nullable(String)	"NULL" (not coerced — only \N is the marker)
\N	Int64 (non-Nullable)	engine error (as with HTTP client + server)

So emitting \N — like the reference client — is the compatible behavior, and the engine already supports it.

Already tracked as a parity divergence

This is not new territory — it's an acknowledged gap in the @clickhouse/client parity suite. tests/clickhouse-js/skip_list.json currently skips two cases for exactly this reason:

• select with query binding > NULL parameter binding > should work with nulls
• select with query binding > NULL parameter binding > should with an explicit undefined

reason: "chdb's queryBindAsync NULL substitution diverges from the server's param_* URL-binding semantics (server-side strict-NULL handling)."

Note the skip reason reads as a subtle server-vs-chdb semantic mismatch, but the actual mechanism is just the hard client-side ChdbBindError above — a one-line-fixable throw, not an immovable divergence.

With the fix, both were un-skipped and pass against @clickhouse/client 1.23.0 (parity runner, ChdbConnection injected).

Proposed fix

Make formatParamValue emit \N for a top-level null/undefined (nested nulls already render as the NULL keyword via serializeValue).

---

Fix: #68