Efficient v6 subnet queries

[leoluk]

Trivial with an IPv4, surprisingly non-trivial with IPv6 because it's a FixedString(16) and we can't do bitwise ops on it.

ClickHouse/ClickHouse#6808

ClickHouse/ClickHouse#11245

This is the only way to do a bitwise query:

select count(Date) from flows_raw where EType = 0x0800 and IPv6CIDRToRange(DstAddr, 104).1 = toIPv6('::44.0.0.0');

Calculate the bitmask by adding 96 to the CIDR mask (i.e. /24 == 24 + 96 == 120).

If you want to filter for the 44.0.0.0/8 network, the query for the "Custom SQL" field would be IPv6CIDRToRange(DstAddr, 104).1 = toIPv6('::44.0.0.0').

For known query patterns, it would be advisable to materialize computed columns.

[fionera]

In the current version of clickhouse there is a function for this:
https://clickhouse.com/docs/en/sql-reference/functions/ip-address-functions#isipaddressinrange

 select SrcAddr from flows_raw where isIPAddressInRange(toString(SrcAddr), '::44.0.0.0/104') == 1  limit 10;

[fionera]

Implemented with 3052806