One isNotNull() crashed ClickHouse, and the fix was 5 lines

We had just moved a KPI dashboard from Postgres to ClickHouse. It’s the kind of route that gets hit all day, matters to the business, and is mostly filters. The switch paid off right away: queries that used to be “fine if nobody touches the filters” became effortless.

About 30 minutes into the day, a dev sent me a Slack message. They had added a WHERE condition, and the query no longer worked. Our tRPC backend reported ECONNRESET and a socket hang up. Usually that’s where you argue with a client library, stare at timeouts, and find out you were pointing at the wrong host.

This time the client was innocent. ClickHouse was crashing, and Docker was restarting it.

The container’s stdout and stderr didn’t say much, so I went through the ClickHouse logs. The first useful line was the one you never want from a database server: a segmentation fault. It happened while ClickHouse was planning the query, before any row was read.

That’s where “my query is wrong” stops being interesting. A valid query should run or return an error. It should never take the server down.

The setup

ClickHouse 25.12, in Docker, on bare metal.
The TypeScript client talking the native protocol on port 9000.
allow_experimental_analyzer = 1, turned on deliberately because we needed a feature behind it. The analyzer is ClickHouse’s newer query analysis layer.
One dashboard route in dev that crashed the server every time.

The reproduction below uses HTTP on port 8123 because it’s easier to paste, but the native protocol crashes the same way.

To get file names and line numbers in the stack trace, I later built ClickHouse 26.1.1.1 from source and used gdb with gef. The build took about 37 minutes of Rust, C and C++, and finished on the first try with zero warnings. That says a lot about the codebase.

Reproducing it

I cut the dashboard query down until only the ingredients that mattered were left: an ANY LEFT JOIN, a filter on the right-hand table, and an isNotNull(...) call, with the analyzer on.

Start a server:

docker run --rm -p 8123:8123 -p 9000:9000 \
  -e CLICKHOUSE_USER=default \
  -e CLICKHOUSE_PASSWORD=default \
  clickhouse/clickhouse-server:25.12

Create two small tables (both MergeTree, ClickHouse’s standard storage engine):

CREATE TABLE Session
(
  id String,
  site Enum8('STORE_A' = 1, 'STORE_B' = 2),
  device Enum8('DESKTOP' = 1, 'MOBILE' = 2)
)
ENGINE = MergeTree
ORDER BY id;

CREATE TABLE AddedToCart
(
  sessionId String,
  order Int32,
  top Nullable(Int32),
  screenHeight Nullable(Int32),
  screenWidth Nullable(Int32),
  isPromotion UInt8,
  date DateTime64(3)
)
ENGINE = MergeTree
ORDER BY (sessionId, date);

INSERT INTO Session (id, site, device) VALUES
  ('s1', 'STORE_A', 'DESKTOP'),
  ('s2', 'STORE_B', 'MOBILE');

INSERT INTO AddedToCart
  (sessionId, order, top, screenHeight,
   screenWidth, isPromotion, date)
VALUES
  ('s1', 1, 100, 400, 1024, 1,
   parseDateTime64BestEffort('2026-01-19T12:00:00.000Z', 3)),
  ('s2', 2, 100, 400, 1024, 1,
   parseDateTime64BestEffort('2026-01-19T12:00:01.000Z', 3));

Then run the query:

SET allow_experimental_analyzer = 1;

SELECT
  s.site AS site,
  if(
    (a.order IS NULL) OR (a.order <= 0) OR (a.order > 30),
    NULL,
    accurateCastOrNull(a.order, 'Int32')
  ) AS page_level,
  count() AS count
FROM AddedToCart AS a
ANY LEFT JOIN Session AS s ON a.sessionId = s.id
WHERE (a.top IS NOT NULL)
  AND (a.screenHeight IS NOT NULL)
  AND (a.screenHeight > 0)
  AND (a.isPromotion = _CAST(1, 'UInt8'))
  AND (s.device = 'DESKTOP')
  AND isNotNull(s.site)
GROUP BY site, page_level
ORDER BY site ASC, page_level ASC
FORMAT JSONEachRow;

Three quick checks pinned down the trigger. The crash went away if I:

replaced ANY LEFT JOIN with ANY INNER JOIN,
removed isNotNull(s.site),
or turned allow_experimental_analyzer off.

So it wasn’t random, and it had nothing to do with data size. It was one specific interaction: join optimization, partial evaluation of the filter, and a type-aware function (isNotNull) running at planning time.

What the stack trace said

With symbols from my 26.1.1.1 build, the stack trace always went through the same chain:

tryConvertAnyOuterJoinToInnerJoin
 -> filterResultForNotMatchedRows
      (QueryPlan/Optimizations/Utils.cpp)
 -> ActionsDAG::evaluatePartialResult
      (Interpreters/ActionsDAG.cpp)
 -> executeActionForPartialResult
 -> FunctionIsNotNull::
      getConstantResultForNonConstArguments
      (Functions/isNotNull.cpp:61)
 -> elem.type->onlyNull(), with type == nullptr

ClickHouse wasn’t crashing while executing the join. It was crashing while trying to optimize it: the optimizer evaluated part of the filter to decide whether the outer join could be simplified, and called isNotNull() with an argument in an invalid state.

Why only with the analyzer

FunctionIsNotNull::getConstantResultForNonConstArguments bails out early with the old analyzer:

if (!use_analyzer)
    return nullptr;

use_analyzer comes from allow_experimental_analyzer (since renamed enable_analyzer, with the old name kept as an alias). So the crashing path only runs with the new analyzer on.

Why the optimizer evaluates your filter

ClickHouse turns filters and projections into an ActionsDAG: a directed acyclic graph of expressions. During planning, one pass tries to turn outer joins into inner joins when that’s safe.

The idea is sound. A LEFT JOIN keeps every left row, even without a match on the right; the right side’s columns are then empty. But if the WHERE clause always throws those unmatched rows away, the outer join does extra work for nothing, and an inner join gives the same result.

Illustrative rows from an ANY LEFT JOIN. The row without a match (s3) gets an empty right side, so isNotNull(s.site) is 0 and the WHERE clause drops it. Whatever the data, the result is the same as an ANY INNER JOIN, which is what the optimizer wants to prove.

To prove it, the optimizer evaluates the filter for the “no match” case, where the right-side values are unknown. The evaluator is built for that: it folds what it can into constants (constant folding) and leaves the rest unknown.

The crash happened because “unknown” turned into “invalid”.

The root cause: `operator[]` inserted a poisoned default

ActionsDAG::evaluatePartialResult keeps a map from graph nodes to partially evaluated columns:

key: const ActionsDAG::Node *
value: ColumnWithTypeAndName

It’s a std::unordered_map<const Node *, ColumnWithTypeAndName> called node_to_column. The value type is defined like this:

/** Column data along with its data type and name.
  * Column data could be nullptr - to represent
  * just 'header' of column.
  */
struct ColumnWithTypeAndName
{
    ColumnPtr column;
    DataTypePtr type;
    String name;

    ColumnWithTypeAndName() = default;
    ...
};

The comment allows column to be null (a “header”: a column with a name and a type but no data). Nothing says type can be null. The default constructor leaves both null anyway.

Missing entries are expected here. filterResultForNotMatchedRows runs the evaluation with:

allow_unknown_function_arguments = true
throw_on_error = false

Right-side columns are supposed to be unknown, as long as their type survives.

The original code built each function’s arguments like this:

arguments[i] = node_to_column[node->children[i]];

It looks like a read. It isn’t. std::unordered_map::operator[] inserts a default-constructed value when the key is missing, and returns that.

So each missing argument became column = nullptr, type = nullptr, name = "". An argument that should have been “unknown, but well-formed” now had a null type pointer. Most of the function machinery assumes a type is always there, and isNotNull() checks it right away during constant folding:

if (elem.type->onlyNull()) ...
if (canContainNull(*elem.type)) ...

The first line is isNotNull.cpp:61, the last frame of the stack trace.

Two ways to read a key that isn’t in the map. operator[] creates an entry with a null type, and the first function that looks at the type dereferences null. find() reports the miss, so the caller can build a placeholder that keeps the type.

Nothing in the SQL is wrong. The query is valid, and the optimizer does something reasonable. The crash comes from one C++ detail: operator[] on a map, in a place where missing keys are normal.

The fix: unknown, but typed

I didn’t touch ClickHouse’s partial evaluation rules. Unknown arguments are allowed on purpose, and the optimization needs them.

What I changed is how a missing entry gets read: find() instead of operator[]. When the child isn’t in the map, the argument becomes a typed stub built from the node’s own metadata: {nullptr, child->result_type, child->result_name}. The value stays unknown (the column pointer is still null), but the type is there, so type-based functions can reason about it.

const auto * child = node->children[i];

if (auto it = node_to_column.find(child);
    it != node_to_column.end())
    arguments[i] = it->second;
else
    arguments[i] = ColumnWithTypeAndName{
        nullptr, child->result_type, child->result_name};

This is the whole change in ActionsDAG.cpp, reformatted here to fit. It matches what the struct already promises: a null column is a valid header, and now that header keeps its type.

With it, isNotNull() folds constants safely even when arguments are missing, and the optimizer decides whether the join rewrite is valid without taking the server down.

The regression test

The regression test, 03799_any_left_join_isnotnull_crash.sql, is the reproduction above: create both tables, insert the rows, turn on allow_experimental_analyzer, and run the query. The only thing it asserts is that the query runs and the server stays up.

I didn’t try to cover every join variant. The goal was to lock in the exact scenario that segfaulted, so it can’t come back silently.

What it costs

Nothing measurable. A map insertion becomes a lookup plus a small stub object, which is noise next to the rest of query planning. Query results don’t change.

What changes is the guarantee: a valid query either runs or fails with a normal error. It no longer crashes the server.

I opened issue #94597 and PR #94600 the same day. The PR was merged on January 21, two days later.

If you run into this

If you use ClickHouse with allow_experimental_analyzer = 1, the optimizer takes paths the old analyzer never did, and that’s where edge cases hide. If one query gives you ECONNRESET and nothing else looks wrong, check whether the server survived the request before you blame the client.

Also, in C++, map[key] is a surprisingly expensive way to learn a life lesson.

tl;dr