Part 10: Advanced concepts

References: - ClickHouse functions for working with nullable values

---

10a. Nullability

When you start doing more involved arithmetic, particularly anything that divides one aggregate by another, you will eventually hit an error like this:

Error: Cannot convert NULL to a non-nullable type: while converting source
column Percent_Of_Connections to destination column Percent_Of_Connections

NULL is not the same as zero. Zero is a value, and dividing by it gives you a defined, if unhelpful, result. NULL is the complete absence of any value, and most operations involving one produce NULL in turn. When a column's declared type does not permit NULL and a NULL arrives anyway, the query fails outright rather than returning partial results.

The fix is to make the query tolerate it, so the offending value comes back empty and every other column still returns.

Function	Behaviour
ifNull(<expression>, <fallback>)	Returns the fallback when the expression is NULL. Usually what you want in a report
coalesce(<a>, <b>, ...)	Returns the first argument that is not NULL
toNullable(<expression>)	Declares the expression nullable, so NULL is displayed rather than raising an error
assumeNotNull(<expression>)	Strips nullability. Only safe when you are certain a NULL cannot occur

Where NULL comes from here. Nothing in network_flow is declared Nullable, so the NULL is not coming out of a column. It is produced by the query. A scalar sub-query is not guaranteed to return a row, so ClickHouse types its result as nullable, and anything built on top of it inherits that. This is why the error names a calculated column rather than a source one.

The query below fails with the error above. Work out where the NULL originates and modify the query to handle it, so the remaining columns still return.

SELECT
  'Remote_Access' AS Protocol_Family,
  application_protos AS protocol_type,
  uniqExact(source_hostname) AS Source_Devices,
  COUNT(*) AS Connections_Count,
  round(
    Connections_Count / (
      SELECT
        COUNT(*)
      FROM
        network_flow
      WHERE
        _time_observed >= toUnixTimestamp(subtractDays(now(), 3)) * 1000
        AND hasAny(application_protos, ['ssh','rdp'])
        AND (
          dest_network = 'external'
          AND source_network = 'external'
        )
    ) * 100,
    2
  ) AS Percent_Of_Connections,
  round(
    SUM(source_bytes_sent + dest_bytes_sent) / 1000000000,
    2
  ) AS GB_Transferred
FROM
  network_flow
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 3)) * 1000
  AND hasAny(application_protos, ['ssh','rdp'])
  AND (
    dest_network = 'external'
    AND source_network = 'external'
  )
GROUP BY
  protocol_type

Hint - read the error

The error names the column it failed on. Find that column in the `SELECT` clause and look at what it is built from, rather than hunting through the source columns.

Hint - which part of that column

`Connections_Count` is a plain `COUNT(*)`, which always returns a number. That leaves the divisor, and the divisor is a sub-query.

Answer

`Percent_Of_Connections` is the column that fails, and the nullability enters through the scalar sub-query used as the divisor. Wrapping the calculation in `ifNull` gives it a fallback:

SELECT
  'Remote_Access' AS Protocol_Family,
  application_protos AS protocol_type,
  uniqExact(source_hostname) AS Source_Devices,
  COUNT(*) AS Connections_Count,
  ifNull(
    round(
      Connections_Count / (
        SELECT
          COUNT(*)
        FROM
          network_flow
        WHERE
          _time_observed >= toUnixTimestamp(subtractDays(now(), 3)) * 1000
          AND hasAny(application_protos, ['ssh','rdp'])
          AND (
            dest_network = 'external'
            AND source_network = 'external'
          )
      ) * 100,
      2
    ),
    0
  ) AS Percent_Of_Connections,
  round(
    SUM(source_bytes_sent + dest_bytes_sent) / 1000000000,
    2
  ) AS GB_Transferred
FROM
  network_flow
WHERE
  _time_observed >= toUnixTimestamp(subtractDays(now(), 3)) * 1000
  AND hasAny(application_protos, ['ssh','rdp'])
  AND (
    dest_network = 'external'
    AND source_network = 'external'
  )
GROUP BY
  protocol_type

`toNullable(round(...))` also clears the error, but it displays `null` in the column instead of a number. Prefer `ifNull` when the result feeds a report, and `toNullable` when you would rather see plainly that the value could not be calculated.

Do not reach for assumeNotNull first. It clears the same error by asserting the value cannot be NULL. If you are wrong, you get an incorrect number rather than a failed query, which is considerably worse in a report someone acts on.

A note on the sub-query: it repeats the outer query's WHERE clause exactly, which is deliberate. The numerator counts connections in one protocol group and the denominator counts them across all matching protocols, so the filters must be identical or the percentages will not total 100. This is the same rule as Part 7b.

---

Congratulations, you have worked through the whole Hunt Lab training series. From here the most useful thing you can do is take a real question from an investigation and answer it yourself.

For the table reference and query limits, see the Hunt Lab overview.

---