Skip to content

Part 10: Advanced concepts

References: - ClickHouse functions for working with nullable values


10a. Nullability

More complex arithmetic can cause errors, especially when you divide one aggregate by another. At some time, you will get an error like this one:

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 a division by zero gives a defined result, but not a helpful result. NULL is the complete absence of a value. Most operations that include a NULL also give NULL as a result. If the declared type of a column is not nullable, but a NULL occurs, the query stops with an error. It does not return partial results.

To correct this problem, make the query accept the NULL. Then the value with the problem is empty, and all the other columns return as usual.

Function Behaviour
ifNull(<expression>, <fallback>) Returns the fallback when the expression is NULL. Usually the correct function for a report
coalesce(<a>, <b>, ...) Returns the first argument that is not NULL
toNullable(<expression>) Declares the expression nullable. Thus, the query shows NULL and does not give an error
assumeNotNull(<expression>) Removes nullability. Safe only when you are sure that a NULL cannot occur

💡 Where NULL comes from here. No column in network_flow is declared Nullable, thus the NULL does not come from a column. The query makes the NULL. It is possible that a scalar sub-query does not return a row. Thus, ClickHouse gives its result a nullable type, and all calculations that use the result also become nullable. This is why the error names a calculated column, not a source column.

The query that follows stops with the error in the previous example. Find where the NULL comes from. Change the query so that it accepts the NULL and the other 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 that caused the problem. Find that column in the `SELECT` clause. Look at the parts of that column. Do not look through all the source columns.
Hint - which part of that column `Connections_Count` is a plain `COUNT(*)`, which always returns a number. Thus, the problem is in the divisor, and the divisor is a sub-query.
Answer `Percent_Of_Connections` is the column with the error. The nullability comes from the scalar sub-query that is the divisor. Put the calculation in `ifNull` to give 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 removes the error, but it shows `null` in the column, not a number. Use `ifNull` when the result goes into a report. Use `toNullable` when you want to see clearly that the calculation was not possible.

âš  Do not use assumeNotNull first. This function removes the same error, because it asserts that the value cannot be NULL. If the value can be NULL, you get an incorrect number, not a query error. In a report that people use to make decisions, an incorrect number is much worse.

The sub-query repeats the WHERE clause of the outer query exactly. This is intentional. The numerator counts the connections in one protocol group. The denominator counts the connections for all the protocols that match. Thus, the filters must be identical, or the total of the percentages will not be 100. This is the same rule as in Part 7b.


Congratulations. You completed the full Hunt Lab training series. Now, the most useful thing that you can do is to take a real question from an investigation and answer it yourself.

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