Bug #68454 Using inet6_ntoa and int6_aton to convert IP causes errors
Submitted: 21 Feb 2013 9:52 Modified: 4 Mar 2013 15:30
Reporter: fengfu zhao Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S1 (Critical)
Version:5.6.11 OS:Any
Assigned to: Tor Didriksen CPU Architecture:Any
Tags: inet6_ntoa inet6_aton error

[21 Feb 2013 9:52] fengfu zhao
Description:
I store ip as bytes(using inet6_aton to convert) in mysql. when my program run  the query "select inet6_ntoa(ip) as IP from ipconvert", I want mysql to return right normal IP. But i find that the result is unexpected。in return dataset, if ip is null in mysql , inet6_ntoa(ip) is null too. this result is right; but the following inet6_ntoa(ip) s  are null too, even if  ip corresponding to IP is not null.
such as:
 select inet6_ntoa(ip) as IP from ipconvert;
 
return dataset:
     IP(return dataset)                       ip(stored in mysql)                   

     123.183.5.59                              {?;    right
     null                                      null   right
     null                                      {?;    IP should be 123.183.5.59
     null                                      {?;    IP should be 123.183.5.59

if my program run the query 'select inet6_ntoa(ip) as IP from ipconvert where ip is not null', the result is right;
return dataset:
     IP(return dataset)                       ip(stored in mysql)                   

     123.183.5.59                              {?;    right
     123.183.5.59                              {?;    right
     123.183.5.59                              {?;    right
After testing, i find if ip is null , the following IP is null too, even if ip corresponding to IP is not null in one query operation.

and when my program run the query 'load data infile 'filename' into table ipconvert set sAddr=inet6_aton(ip)' using inet6_aton also attain such problems

How to repeat:
test inet6_ntoa
1. create a table: 
schema:
CREATE TABLE `ipconvert` (
  `id` bigint(20) NOT NULL,
  `ip` varbinary(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2. insert some records:
    insert into ipconvert values(1,inet6_aton('123.183.5.59')),(2,NULL),(3,inet6_aton('123.183.5.59')),(4,inet6_aton('123.183.5.59'))

3. run these queries: 'select inet6_ntoa(ip) as IP from ipconvert' and 'select inet6_ntoa(ip) as IP from ipconvert where ip is not null'. and compare results

test inet6_aton
1. create a table:

schema:
CREATE TABLE `ipconvert` (
  `id` bigint(20) NOT NULL,
  `ip` varbinary(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2 insert some records: use load data style
   load data infile 'filename' into table ipconvert set sAddr=inet6_aton(ip)
   file content:
   1	123.183.5.59
   2	\N
   3	123.183.5.59
   4	123.183.5.59  

3 After loading, check the data in mysql
[21 Feb 2013 10:26] MySQL Verification Team
It does look suspicious indeed:

mysql> select id, inet6_ntoa(ip),ip,hex(ip) from ipconvert;
+----+----------------+------+----------+
| id | inet6_ntoa(ip) | ip   | hex(ip)  |
+----+----------------+------+----------+
|  1 | 123.183.5.59   | {╖♣; | 7BB7053B |
|  2 | NULL           | NULL | NULL     |
|  3 | NULL           | {╖♣; | 7BB7053B |
|  4 | NULL           | {╖♣; | 7BB7053B |
+----+----------------+------+----------+
4 rows in set (0.00 sec)

mysql> select id, inet6_ntoa(ip),ip,hex(ip) from ipconvert where ip is not null;
+----+----------------+------+----------+
| id | inet6_ntoa(ip) | ip   | hex(ip)  |
+----+----------------+------+----------+
|  1 | 123.183.5.59   | {╖♣; | 7BB7053B |
|  3 | 123.183.5.59   | {╖♣; | 7BB7053B |
|  4 | 123.183.5.59   | {╖♣; | 7BB7053B |
+----+----------------+------+----------+
3 rows in set (0.00 sec)

mysql>
mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.6.11-debug |
+--------------+
1 row in set (0.00 sec)
[4 Mar 2013 15:30] Paul DuBois
Noted in 5.6.11, 5.7.1 changelogs.

If INET6_NTOA() or INET6_ATON() returned NULL for a row in a result
set, following rows also returned NULL.