Runtime semantics

These are the ClickHouse-specific behaviors that show up at runtime. None of them are bugs — they are properties of ClickHouse itself, and Quarry exposes them rather than papering over them.

LEFT JOIN does not produce null by default

Quarry's ordinary leftJoin() explicitly executes with join_use_nulls = 0. Unmatched right-side columns use ClickHouse type defaults such as 0, '', or false. Client/session defaults do not silently change this behavior. The same policy applies when the SELECT is used as the source of an INSERT SELECT.

Use leftJoinNullable() to request nulls and infer nullable right-side columns:

const rows = await db.selectFrom("users as u")
  .leftJoinNullable("event_logs as e", "u.id", "e.user_id")
  .select("u.id", "e.event_type")
  .execute();
// { id: number; event_type: string | null }[]

This sets join_use_nulls = 1. All outer joins in the same query, including CTEs, subqueries, and UNION branches, must use the same policy. Mixed policies are rejected before execution. ClickHouse may reject nullable joins over data types that cannot be wrapped in Nullable, such as arrays; select only supported fields in a subquery when necessary.

Supported result settings

Select execution explicitly supplies these settings so they override inherited client/session defaults:

Conflicting values in .settings() or execution options are rejected. These rules apply to buffered, streaming, and totals-aware reads. Raw SQL expressions and manually supplied schema types remain assertions made by the caller; arbitrary SQL or unsupported server settings can still exceed the builder's type guarantees. When using toSQL() with another execution path, preserve these format settings.

UInt64 and count() come back as strings

The @clickhouse/client driver returns 64-bit integer types as JavaScript strings under JSONEachRow, because they cannot fit in a JS number without precision loss.

This affects:

• UInt64 columns
• Int64 columns (depending on driver settings)
• count(), sum(), and any aggregate that returns a 64-bit integer
• the length(array) array helper

Quarry types these as string in the result type so the builder stays honest about what you actually receive.

const rows = await db
  .selectFrom("users as u")
  .selectExpr((eb) => ["u.id", eb.fn.length("u.tags").as("tag_count")])
  .execute();

// rows[0].tag_count is `string`, not `number`

DateTime64 is a string

DateTime64(N) columns also come back as strings under the default JSON formats. If you need a Date object, parse it on the JavaScript side.

Decimal(p, s) is a number

By contrast, Decimal(18, 2) and friends come back as JS number. This is fine for many analytics use cases but is a precision risk for anything financial. If you need exact decimals, cast or convert on the SQL side or deserialize through a decimal library.

groupArray(...) removes NULL values

groupArray(...) does not preserve NULL entries from nullable inputs. ClickHouse drops them from the resulting array.

That means a nullable column like Nullable(String) behaves like this at runtime:

const rows = await db
  .selectFrom("typed_samples as t")
  .selectExpr((eb) => [eb.fn.groupArray("t.nickname").as("nicknames")])
  .execute();

// `nicknames` is `string[]`, not `(string | null)[]`

If every input value is NULL, the result is [].

Empty averages and quantiles return null

avg, avgIf, and numeric quantile can produce NaN when no values contribute to the aggregate. With Quarry's JSON settings, this serializes as null, so their result type is number | null. Check for null before calling number methods. This also applies to avgIf when rows exist but none match its condition.

Cast helpers preserve nulls too: casting a Nullable(String) column with toUInt32 yields number | null. Aliases and subqueries retain this nullability.

Date inputs

When no explicit or derived column type is available, JavaScript Date parameters use DateTime64(3, 'UTC'), preserving milliseconds and the UTC instant. An explicit param(date, "DateTime") deliberately uses whole seconds. Use param to control the target precision or timezone.

For inferred Date arrays, in / not in compile to has(array, value) / NOT has(array, value). This avoids ClickHouse's incompatible or narrowing IN conversions between timestamp types. Explicitly typed arrays retain native IN; use them when you need that SQL shape for index use, choosing the column's exact timestamp type and precision.

import { param } from "quarry";

const rows = await db
  .selectFrom("event_logs as e")
  .select("e.user_id", "e.created_at")
  .where("e.created_at", ">=", param("2025-01-01 00:00:00", "DateTime"))
  .execute();