Search code examples
mysqlmariadbipv6bitwise-operators

Mysql: get IPv6 binary net mask from binary ip and subnet

I have a table that stores IPv6 addresses as BINARY(16) and subnet as INT(3)

I thought it would be simple to get the net mask by doing:

SELECT ~INET6_ATON('::') << (128 - subnet);

but this returns zero, in fact all the bitwise operators seem to deliver integer results when I use them on a binary string :-(

I'm using 10.1.30-MariaDB

+-----------------+
| VERSION()       |
+-----------------+
| 10.1.30-MariaDB |
+-----------------+

any help much appreciated.

EDIT: I completely misinterpreted Maria's version string, sorry :-(

Solution

  • Bit operations in 5.5 are limited to 64 bits. (8.0 relaxes the limitation. 5.5 is getting quite old. If you upgrade, either dump and reload, or upgrade in 3 steps: 5.5->5.6->5.7->8.0)

    You probably wanted a right shift >>. Or maybe (1 << amt) - 1. Example (using only 64-bit arithmetic):

    SELECT HEX(~((1 << 8) - 1)); --> FFFFFFFFFFFFFF00

    Some 128-bit operations are available in the "reference implementation for IPv6" link from http://mysql.rjweb.org/doc.php/ipranges . There is not a "shift" function, but you could adapt the techniques (that use HEX()) to achieve your purpose. It does have add/subtract 1 to/from an IPv6 value. This is handy for certain mask-building and masking operations.

    If you would like to explain what you will be doing with the result of that SELECT, I might be able to give you more of an answer.

    (Found in the 5.7.11 release notes):

    Bit functions and operators comprise BIT_COUNT(), BIT_AND(), BIT_OR(), BIT_XOR(), &, |, ^, ~, <<, and >>. Currently, bit functions and operators require BIGINT (64-bit integer) arguments and return BIGINT values, so they have a maximum range of 64 bits. Arguments of other types are converted to BIGINT and truncation might occur.

    An extension for MySQL 8.0 changes this cast-to-BIGINT behavior: Bit functions and operators permit binary string type arguments (BINARY, VARBINARY, and the BLOB types), enabling them to take arguments and produce return values larger than 64 bits. Consequently, bit operations on binary string arguments in MySQL 5.7 might produce different results in MySQL 8.0. To provide advance notice about this potential change in behavior, the server now produces warnings for bit operations for which binary string arguments are not converted to integer in MySQL 8.0. These warnings afford an opportunity to rewrite affected statements. To explicitly produce MySQL 5.7 behavior in a way that will not change after an upgrade to 8.0, cast bit-operation binary string arguments to convert them to integer. For more information and examples, see Bit Functions and Operators.