BigQuery中NET函数截断IPv4与IPv6地址后显示差异的原因解析

Great question! This difference has nothing to do with BigQuery specifically—it’s all about standard IP address formatting rules, which the NET functions strictly follow. Let’s break it down step by step:

IPv4 Truncation: Why the Final 0? #

IPv4 addresses are 32-bit numbers, split into 4 8-bit "octets" (the decimal segments you see, like 254.34.78.20). When you truncate to 24 bits with NET.IP_TRUNC, you’re telling BigQuery to keep the first 3 octets (24 total bits) and set the last 8 bits (the final octet) to 0.

In your example, 254.34.78.20 becomes 254.34.78.0 because:

• The first 3 octets (254.34.78) are preserved.
• The last octet (20) is replaced with 0.

IPv4 has no rule to omit leading or trailing zeros in octets—every segment must be written explicitly. So that final 0 can’t be left out, hence you see it in the result.

IPv6 Truncation: Why the ::? #

IPv6 addresses are 128-bit numbers, split into 8 16-bit "hextets" (the hexadecimal segments, like 2a02:c7e:3f0d:e00:48e:abff:d697:9cc2). Truncating to 64 bits means keeping the first 4 hextets (64 total bits) and setting the last 4 hextets to 0.

Your original IPv6 address would become 2a02:c7e:3f0d:e00:0:0:0:0 after truncation. But IPv6 has a handy zero compression rule: any sequence of one or more consecutive all-zero hextets can be replaced with :: (and you can only use this shorthand once per address).

So 2a02:c7e:3f0d:e00:0:0:0:0 gets simplified to 2a02:c7e:3f0d:e00::—that’s why it looks like "no content" in the final segment. It’s just a standard shorthand for four consecutive zero hextets.

The Core Difference #

To sum it up:

• IPv4 doesn’t support zero compression, so truncated zero octets have to be written as 0.
• IPv6 uses zero compression to simplify long addresses, so truncated zero hextets are collapsed into ::.

BigQuery’s NET functions are just adhering to these universal IP standards—there’s no special behavior here beyond what’s expected for IP address formatting.

内容的提问来源于stack exchange，提问作者jamiet