Why does this query return correct result in the MySQL and null result in the MariaDB?
select inet6_ntoa(inet6_aton('200d:31c4:1905:9eb2:3c7f:c45c:de78:42cd') & ((~INET6_ATON('::') << (128 - 97))))
Returns in the MySQL:
200d:31c4:1905:9eb2:3c7f:c45c:8000::
Returns in the MariaDB:
null
It looks like the issue is in the ~ operator. For MariaDB it is defined as follow:
Bitwise NOT. Converts the value to 4 bytes binary and inverts all bits.
When applied to the result of INET6_ATON() the length of the binary value get truncated. The query
select HEX(INET6_ATON('::')), HEX(~INET6_ATON('::'))
in MariaDB will produce the following values:
HEX(INET6_ATON('::')) -> 00000000000000000000000000000000
HEX(~INET6_ATON('::')) -> FFFFFFFFFFFFFFFF
(interestingly, it converts the value to 8 bytes instead of 4 bytes, at least for the MariaDB server running on https://dbfiddle.uk/)
Where as in MySQL the bitwise negate operator will produce the following value:
HEX(INET6_ATON('::')) -> 00000000000000000000000000000000
HEX(~INET6_ATON('::')) -> FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
From there on, in MariaDB, all remaining bit operations you have like shifting and & will fail or will produce incorrect values, which ultimately results in 0 or null, where in MySQL you will get the correct result.