Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20783

INET6 cannot be converted to BINARY(16) (requires clarification in documentation)

Details

    Description

      The description of MDEV-274 says

      Values are stored as a 16-byte fixed length binary string <...>
      Storage engines see INET6 as BINARY(16)

      One could derive from that that INET6 can be converted into BINARY(16), but it's not the case:

      CREATE OR REPLACE TABLE t1 (a INET6);
      		 
      INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');
      		 
      ALTER TABLE t1 MODIFY a BINARY(16);
      		 
      select cast(a as binary(16)) from t1;
      		 
      show warnings;


      MariaDB [test]> ALTER TABLE t1 MODIFY a BINARY(16);
      		 
      ERROR 1406 (22001): Data too long for column 'a' at row 1
      		 
      MariaDB [test]> select cast(a as binary(16)) from t1;
      		 
      +-----------------------+
      		 
      | cast(a as binary(16)) |
      		 
      +-----------------------+
      		 
      | 2001:db8::ff00:4      |
      		 
      +-----------------------+
      		 
      1 row in set, 1 warning (0.000 sec)
      		 
       
      		 
      MariaDB [test]> show warnings;
      		 
      +---------+------+----------------------------------------------------------------+
      		 
      | Level   | Code | Message                                                        |
      		 
      +---------+------+----------------------------------------------------------------+
      		 
      | Warning | 1292 | Truncated incorrect BINARY(16) value: '2001:db8::ff00:42:8329' |
      		 
      +---------+------+----------------------------------------------------------------+
      		 
      1 row in set (0.000 sec)

      I assume it's expected, but it should probably be clarified in documentation.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-20784 Testing for MDEV-274 (INET6, data type for IPv6/IPv4 addresses)

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-20795 CAST(inet6 AS BINARY) returns wrong result

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-274 The data type for IPv6/IPv4 addresses in MariaDB

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-20781 Document INET6 (MDEV-274)

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            elenst Elena Stepanova made changes -
            Description The description of MDEV-274 says
            {quote}
            Values are stored as a 16-byte fixed length binary string <...>
            Storage engines see INET6 as BINARY(16)
            {quote}

            One could derive from that that INET6 can be converted into BINARY(16), but it's not the case:
            {code:sql}
            MariaDB [test]> CREATE OR REPLACE TABLE t1 (a INET6);
            Query OK, 0 rows affected (0.321 sec)

            MariaDB [test]> INSERT INTO t1 VALUES (INET6_ATON('2001:db8::ff00:42:8329'));
            Query OK, 1 row affected (0.034 sec)

            MariaDB [test]> ALTER TABLE t1 MODIFY a BINARY(16);
            ERROR 1406 (22001): Data too long for column 'a' at row 1
            MariaDB [test]> select cast(a as binary(16)) from t1;
            +-----------------------+
            | cast(a as binary(16)) |
            +-----------------------+
            | 2001:db8::ff00:4 |
            +-----------------------+
            1 row in set, 1 warning (0.000 sec)

            MariaDB [test]> show warnings;
            +---------+------+----------------------------------------------------------------+
            | Level | Code | Message |
            +---------+------+----------------------------------------------------------------+
            | Warning | 1292 | Truncated incorrect BINARY(16) value: '2001:db8::ff00:42:8329' |
            +---------+------+----------------------------------------------------------------+
            1 row in set (0.000 sec)
            {code}

            I assume it's expected, but it should probably be clarified in documentation.             		The description of MDEV-274 says
            {quote}
            Values are stored as a 16-byte fixed length binary string <...>
            Storage engines see INET6 as BINARY(16)
            {quote}

            One could derive from that that INET6 can be converted into BINARY(16), but it's not the case:
            {code:sql}
            MariaDB [test]> CREATE OR REPLACE TABLE t1 (a INET6);
            Query OK, 0 rows affected (0.321 sec)

            MariaDB [test]> INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');
            Query OK, 1 row affected (0.034 sec)

            MariaDB [test]> ALTER TABLE t1 MODIFY a BINARY(16);
            ERROR 1406 (22001): Data too long for column 'a' at row 1
            MariaDB [test]> select cast(a as binary(16)) from t1;
            +-----------------------+
            | cast(a as binary(16)) |
            +-----------------------+
            | 2001:db8::ff00:4 |
            +-----------------------+
            1 row in set, 1 warning (0.000 sec)

            MariaDB [test]> show warnings;
            +---------+------+----------------------------------------------------------------+
            | Level | Code | Message |
            +---------+------+----------------------------------------------------------------+
            | Warning | 1292 | Truncated incorrect BINARY(16) value: '2001:db8::ff00:42:8329' |
            +---------+------+----------------------------------------------------------------+
            1 row in set (0.000 sec)
            {code}

            I assume it's expected, but it should probably be clarified in documentation.
            elenst Elena Stepanova made changes -
            Description The description of MDEV-274 says
            {quote}
            Values are stored as a 16-byte fixed length binary string <...>
            Storage engines see INET6 as BINARY(16)
            {quote}

            One could derive from that that INET6 can be converted into BINARY(16), but it's not the case:
            {code:sql}
            MariaDB [test]> CREATE OR REPLACE TABLE t1 (a INET6);
            Query OK, 0 rows affected (0.321 sec)

            MariaDB [test]> INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');
            Query OK, 1 row affected (0.034 sec)

            MariaDB [test]> ALTER TABLE t1 MODIFY a BINARY(16);
            ERROR 1406 (22001): Data too long for column 'a' at row 1
            MariaDB [test]> select cast(a as binary(16)) from t1;
            +-----------------------+
            | cast(a as binary(16)) |
            +-----------------------+
            | 2001:db8::ff00:4 |
            +-----------------------+
            1 row in set, 1 warning (0.000 sec)

            MariaDB [test]> show warnings;
            +---------+------+----------------------------------------------------------------+
            | Level | Code | Message |
            +---------+------+----------------------------------------------------------------+
            | Warning | 1292 | Truncated incorrect BINARY(16) value: '2001:db8::ff00:42:8329' |
            +---------+------+----------------------------------------------------------------+
            1 row in set (0.000 sec)
            {code}

            I assume it's expected, but it should probably be clarified in documentation.             		The description of MDEV-274 says
            {quote}
            Values are stored as a 16-byte fixed length binary string <...>
            Storage engines see INET6 as BINARY(16)
            {quote}

            One could derive from that that INET6 can be converted into BINARY(16), but it's not the case:
            {code:sql}
            CREATE OR REPLACE TABLE t1 (a INET6);
            INSERT INTO t1 VALUES ('2001:db8::ff00:42:8329');
            ALTER TABLE t1 MODIFY a BINARY(16);
            select cast(a as binary(16)) from t1;
            show warnings;
            {code}
            {code:sql}
            MariaDB [test]> ALTER TABLE t1 MODIFY a BINARY(16);
            ERROR 1406 (22001): Data too long for column 'a' at row 1
            MariaDB [test]> select cast(a as binary(16)) from t1;
            +-----------------------+
            | cast(a as binary(16)) |
            +-----------------------+
            | 2001:db8::ff00:4 |
            +-----------------------+
            1 row in set, 1 warning (0.000 sec)

            MariaDB [test]> show warnings;
            +---------+------+----------------------------------------------------------------+
            | Level | Code | Message |
            +---------+------+----------------------------------------------------------------+
            | Warning | 1292 | Truncated incorrect BINARY(16) value: '2001:db8::ff00:42:8329' |
            +---------+------+----------------------------------------------------------------+
            1 row in set (0.000 sec)
            {code}

            I assume it's expected, but it should probably be clarified in documentation.
            bar Alexander Barkov made changes -
            Comment [ [~elenst], I think it should be fixed. Moreover, it works the other way around, i.e. conversion from BINARY(16) to INET6:

            {code:sql}
            CREATE OR REPLACE TABLE t1 (a BINARY(16));
            INSERT INTO t1 VALUES (INET6_ATON('ffff::ffff'));
            ALTER TABLE t1 MODIFY a INET6;
            SELECT * FROM t1;
            {code}
            {noformat}
            +------------+
            | a |
            +------------+
            | ffff::ffff |
            +------------+
            {noformat}


            ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            issue.field.resolutiondate 2019-10-10 05:33:26.0 2019-10-10 05:33:26.522
            bar Alexander Barkov made changes -
            Fix Version/s 10.5.0 [ 23709 ]
            Fix Version/s 10.5 [ 23123 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 100304 ] MariaDB v4 [ 156834 ]

            People

              bar Alexander Barkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.