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_flowis declaredNullable, 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
Do not use
assumeNotNullfirst. 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.