The data type for IPv6/IPv4 addresses in MariaDB

Original task description

MariaDB will support the data type to store IPv6 addresses in table columns.
IPv4 addresses will be also supported by this type assuming conventional mapping of IPv4 addresses into IPv6 addresses

The objects of the IPv6 type can be used in any context where other MySQL data types are used.
All comparison operators over operands of this data type will be supported.
Also the basic function specific for this type will be supported.

The internal representation of the values of this type will support the conventional compression used for IPv6 addresses when any group of 4 hexadecimal '0000' is represented by one hexadecimal '0'.

Implementation details

The new data type will work as follows:

General information

• The data type name is INET6

  CREATE TABLE t1 (a INET6);

• Values are stored as a 16-byte fixed length binary string, with most significant byte first.
• Storage engines see INET6 as BINARY(16).
• Clients see INET6 as CHAR(39) and get text representation on retrieval.

Inserting values to INET6 columns

• Values can be inserted using short text address notation, according to RFC-5952:

  INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');

  or long text address notation:

  INSERT INTO t1 VALUES ('2001:0db8:0000:0000:0000:ff00:0042:8329');

  or 16-byte binary string notation:

  INSERT INTO t1 VALUES (0x20010DB8000000000000FF0000428329);

  INSERT INTO t1 VALUES (UNHEX('20010DB8000000000000FF0000428329'));

• INET6 can also store IPv4 addresses, using IPv4-mapped and IPv4-compatible notations:

  INSERT INTO t1 VALUES ('::ffff:192.0.2.128'); -- mapped

  INSERT INTO t1 VALUES ('::192.0.2.128'); -- compatible

  The IPv4-compatible notation is considered as deprecated. It is supported for compatibility with the INET6_ATON() function, which also understands this format. It's recommended to use the mapped format to store IPv4 addresses in INET6.

  When an IPv4 mapped (or compatible) value is stored in INET6, it still occupies 16 bytes:

  CREATE OR REPLACE TABLE t1 (a INET6);

  INSERT INTO t1 VALUES ('::ffff:192.0.2.128');

  SELECT * FROM t1;

  SELECT HEX(a) FROM t1;

  +----------------------------------+

  | HEX(a) |

  +----------------------------------+

  | 00000000000000000000FFFFC0000280 |

  +----------------------------------+

Retrieving values from INET6 columns

• On retrieval, in the client-server text protocol, INET6 values are converted to the short text representation, according to RFC-5952:

  SELECT a FROM t1;

  +------------------------+

  | a |

  +------------------------+

  | 2001:db8::ff00:42:8329 |

  +------------------------+

  i.e. with all leading zeroes in each group removed and with consequent zero groups compressed.

• There is no a way to retrieve INET6 value using long text representation. One can create a simple stored function for this purpose. If we have user requests, we can later add a built-in function to convert INET6 values to long text representation.

CAST from and to INET6

• CAST from a character string to INET6 understands addresses in short or long text notation (including IPv4 mapped and compatible addresses). NULL is returned if the format is not understood.
• CAST from a binary string to INET6 requires a 16-byte string as an argument. NULL is returned if the argument length is not equalt to 16.
• CAST from other data types to INET6 first converts data to a character string, then CAST from character string to INET6 is applied.
• CAST from INET6 to CHAR returns short text address notation.
• CAST from INET6 to BINARY returns its 16-byte binary string representation.
• CAST from INET6 to data types other than CHAR (e.g. SIGNED, UNSIGNED, TIME, etc) returns an error:

  SELECT CAST(a AS DECIMAL) FROM t1;

  ERROR 4079 (HY000): Illegal parameter data type inet6 for operation 'decimal_typecast'

Comparing INET6 values

An INET6 expression can be compared to:

• another INET6 expression

  CREATE OR REPLACE TABLE t1 (a INET6);

  CREATE OR REPLACE TABLE t2 (a INET6);

  INSERT INTO t1 VALUES ('2001:db8::ff00:42:8328'),('2001:db8::ff00:42:8329');

  INSERT INTO t2 VALUES ('2001:db8::ff00:42:832a'),('2001:db8::ff00:42:8329');

  SELECT t1.* FROM t1,t2 WHERE t1.a=t2.a;

  +------------------------+

  | a |

  +------------------------+

  | 2001:db8::ff00:42:8329 |

  +------------------------+

• to a character string expression with a text (short or long) address representation:

  CREATE OR REPLACE TABLE t1 (a INET6);

  INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');

  SELECT * FROM t1 WHERE a='2001:db8::ff00:42:8329';

  +------------------------+

  | a |

  +------------------------+

  | 2001:db8::ff00:42:8329 |

  +------------------------+

• or to a 16-byte binary string expression:

  CREATE OR REPLACE TABLE t1 (a INET6);

  INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');

  SELECT * FROM t1 WHERE a=X'20010DB8000000000000FF0000428329';

  +------------------------+

  | a |

  +------------------------+

  | 2001:db8::ff00:42:8329 |

  +------------------------+

An attempt to compare INET6 to an expression of other data types returns an error:

SELECT * FROM t1 WHERE a=1;

ERROR 4078 (HY000): Illegal parameter data types inet6 and int for operation '='

Mixing INET6 values for result

An INET6 expression can be mixed for result (i.e. UNION, CASE..THEN, COALESCE etc) with:

• another INET6 expression. The result data type is INET6.

  CREATE OR REPLACE TABLE t1 (a INET6, b INET6);

  INSERT INTO t1 VALUES (NULL,'2001:db8::ff00:42:8329');

  SELECT a FROM t1 UNION SELECT b FROM t1;

  +------------------------+

  | a |

  +------------------------+

  | NULL |

  | 2001:db8::ff00:42:8329 |

  +------------------------+

  SELECT COALESCE(a, b) FROM t1;

  +------------------------+

  | COALESCE(a, b) |

  +------------------------+

  | 2001:db8::ff00:42:8329 |

  +------------------------+

• a character string in text (short or long) address representation. The result data type is INET6. The character string counterpart is automatically converted to INET6. If the string format is not understood, it's converted with a warning to either NULL or to '::', depending on the NULL-ability of the result.

  CREATE OR REPLACE TABLE t1 (a INET6, b VARCHAR(64));

  INSERT INTO t1 VALUES (NULL,'2001:db8::ff00:42:8328');

  INSERT INTO t1 VALUES (NULL,'2001:db8::ff00:42:832a garbage');

  SELECT COALESCE(a,b) FROM t1;

  SHOW WARNINGS;

  +------------------------+

  | COALESCE(a,b) |

  +------------------------+

  | 2001:db8::ff00:42:8328 |

  | NULL |

  +------------------------+

  2 rows in set, 1 warning (0.001 sec)

  +---------+------+---------------------------------------------------------+

  | Level | Code | Message |

  +---------+------+---------------------------------------------------------+

  | Warning | 1292 | Incorrect inet6 value: '2001:db8::ff00:42:832a garbage' |

  +---------+------+---------------------------------------------------------+

• a 16-byte binary string. The result data type is INET6. The binary string counterpart is automatically converted to INET6. If the length of the binary string is not equal to 16, it's converted with a warning to NULL or to '::' depending on the NULL-ability of the result.

  CREATE OR REPLACE TABLE t1 (a INET6, b VARBINARY(16));

  INSERT INTO t1 VALUES (NULL,CONCAT(0xFFFF,REPEAT(0x0000,6),0xFFFF));

  INSERT INTO t1 VALUES (NULL,0x00/*garbage*/);

  SELECT COALESCE(a,b) FROM t1;

  SHOW WARNINGS;

  +---------------+

  | COALESCE(a,b) |

  +---------------+

  | ffff::ffff |

  | NULL |

  +---------------+

  2 rows in set, 1 warning (0.001 sec)

  +---------+------+-------------------------------+

  | Level | Code | Message |

  +---------+------+-------------------------------+

  | Warning | 1292 | Incorrect inet6 value: '\x00' |

  +---------+------+-------------------------------+

Attempts to mix INET6 for result with other data types return errors:

SELECT CAST('ffff::ffff' AS INET6) UNION SELECT 1;

ERROR 4078 (HY000): Illegal parameter data types inet6 and int for operation 'UNION'

Mixing INET6 values for LEAST() and GREATEST()

Mixing INET6 with other data types for LEAST() and GREATEST(), when mixing for comparison and mixing for result are involved at the same time, uses the same rules with mixing for result, described in the previous paragraph.

Functions and operators

• HEX() with an INET6 argument returns hexadecimal representation of the underlying 16-byte binary string:

  SELECT HEX(CAST('2001:db8::ff00:42:8329' AS INET6));

  +----------------------------------------------+

  | HEX(CAST('2001:db8::ff00:42:8329' AS INET6)) |

  +----------------------------------------------+

  | 20010DB8000000000000FF0000428329 |

  +----------------------------------------------+

• Arithmetic operators (+,-,*,/,MOD,DIV) are not supported for INET6. This may change in the future.
• Function INET6_ATON() now understands INET6 values as an argument:

  CREATE OR REPLACE TABLE t1 (a INET6);

  INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');

  SELECT a, HEX(INET6_ATON(a)) FROM t1;

  +------------------------+----------------------------------+

  | a | HEX(INET6_ATON(a)) |

  +------------------------+----------------------------------+

  | 2001:db8::ff00:42:8329 | 20010DB8000000000000FF0000428329 |

  +------------------------+----------------------------------+

• 10.5.0 changed prototypes of these SQL functions IS_IPV4_COMPAT() and IS_IPV4_MAPPED() from

  IS_IPV4_COMPAT(a BINARY(16))

  IS_IPV4_MAPPED(a BINARY(16))

  to

  IS_IPV4_COMPAT(a INET6)

  IS_IPV4_MAPPED(a INET6)

  Example:

  CREATE OR REPLACE TABLE t1 (a INET6);

  INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');

  INSERT INTO t1 VALUES ('::ffff:192.168.0.1');

  INSERT INTO t1 VALUES ('::192.168.0.1');

  SELECT a, IS_IPV4_MAPPED(a), IS_IPV4_COMPAT(a) FROM t1;

  +------------------------+-------------------+-------------------+

  | a | IS_IPV4_MAPPED(a) | IS_IPV4_COMPAT(a) |

  +------------------------+-------------------+-------------------+

  | 2001:db8::ff00:42:8329 | 0 | 0 |

  | ::ffff:192.168.0.1 | 1 | 0 |

  | ::192.168.0.1 | 0 | 1 |

  +------------------------+-------------------+-------------------+

  When the argument is not INET6, automatic implicit CAST to INET6 is applied. As a consequence, IS_IPV4_COMPAT() and IS_IPV4_MAPPED() now understand arguments in both text representation and binary(16) representation. In versions before 10.5.0, these functions understood only binary(16) representation.

  CREATE OR REPLACE TABLE t1 (

  a INET6,

  b VARCHAR(39) DEFAULT a

  );

  INSERT INTO t1 (a) VALUES ('ffff::ffff'),('::ffff:192.168.0.1');

  SELECT a, IS_IPV4_MAPPED(a), b, IS_IPV4_MAPPED(b) FROM t1;

  +--------------------+-------------------+--------------------+-------------------+

  | a | IS_IPV4_MAPPED(a) | b | IS_IPV4_MAPPED(b) |

  +--------------------+-------------------+--------------------+-------------------+

  | ffff::ffff | 0 | ffff::ffff | 0 |

  | ::ffff:192.168.0.1 | 1 | ::ffff:192.168.0.1 | 1 |

  +--------------------+-------------------+--------------------+-------------------+

  CREATE OR REPLACE TABLE t1 (

  a INET6,

  b BINARY(16) DEFAULT UNHEX(HEX(a))

  );

  INSERT INTO t1 (a) VALUES ('ffff::ffff'),('::ffff:192.168.0.1');

  SELECT a, IS_IPV4_MAPPED(a), HEX(b), IS_IPV4_MAPPED(b) FROM t1;

  +--------------------+-------------------+----------------------------------+-------------------+

  | a | IS_IPV4_MAPPED(a) | HEX(b) | IS_IPV4_MAPPED(b) |

  +--------------------+-------------------+----------------------------------+-------------------+

  | ffff::ffff | 0 | FFFF000000000000000000000000FFFF | 0 |

  | ::ffff:192.168.0.1 | 1 | 00000000000000000000FFFFC0A80001 | 1 |

  +--------------------+-------------------+----------------------------------+-------------------+

INET6 and prepared statement parameters

INET6 understands both text and binary(16) address representation in prepared statement parameters (PREPARE..EXECUTE and EXECUTE IMMEDIATE statements).

CREATE OR REPLACE TABLE t1 (a INET6);

EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING 'ffff::fffe';

EXECUTE IMMEDIATE 'INSERT INTO t1 VALUES (?)' USING X'FFFF000000000000000000000000FFFF';

SELECT * FROM t1;

+------------+

| a          |

+------------+

| ffff::fffe |

| ffff::ffff |

+------------+

EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING 'ffff::fffe';

+------------+

| a          |

+------------+

| ffff::fffe |

+------------+

EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a=?' USING X'FFFF000000000000000000000000FFFF';

+------------+

| a          |

+------------+

| ffff::ffff |

+------------+

Migration from BINARY(16) to INET6

Suppose, prior to 10.5.0, you have used BINARY(16) as a storage for IPv6 internet addresses, in combination with INET6_ATON() and INET6_NTOA() to respectively insert and retrieve data:

CREATE OR REPLACE TABLE t1 (a BINARY(16));

INSERT INTO t1 VALUES (INET6_ATON('ffff::ffff'));

SELECT INET6_NTOA(a) FROM t1;

+---------------+

| INET6_NTOA(a) |

+---------------+

| ffff::ffff    |

+---------------+

In 10.5, you can alter BINARY(16) columns storing IPv6 addresses to INET6. After such ALTER there is no a need to use INET6_ATON() and INET6_NTOA(). Addresses can be inserted and retrieved directly:

ALTER TABLE t1 MODIFY a INET6;

INSERT INTO t1 VALUES ('ffff::fffe');

SELECT * FROM t1;

+------------+

| a          |

+------------+

| ffff::ffff |

| ffff::fffe |

+------------+

Migration from INET6 to BINARY(16)

It is possible to convert INET6 columns to BINARY(16) and continue using the data in combination with INET6_NTOA() and INET6_ATON().

CREATE OR REPLACE TABLE t1 (a INET6);

INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');

INSERT INTO t1 VALUES ('::ffff:192.168.0.1');

INSERT INTO t1 VALUES ('::192.168.0.1');

ALTER TABLE t1 MODIFY a BINARY(16);

SELECT INET6_NTOA(a) FROM t1;

+------------------------+

| INET6_NTOA(a)          |

+------------------------+

| 2001:db8::ff00:42:8329 |

| ::ffff:192.168.0.1     |

| ::192.168.0.1          |

+------------------------+