MySQL Bugs: #40466: INET_NTOA() Returns VARBINARY instead of STRING in Connector/J

Bug #40466	INET_NTOA() Returns VARBINARY instead of STRING in Connector/J
Submitted:	31 Oct 2008 20:30	Modified:	31 Oct 2008 20:37
Reporter:	Greg Wittel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	5.0.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When querying a resultset that includes INET_NTOA(unsigned int), the result set column data type is Varbinary (-3) instead of the expected String (12) result.

The change in behavior can be seen where Connector/J 5.0.4 returns String while 5.0.6 and higher return varbinary.

How to repeat:
1.  Create a database table in Mysql like:
  CREATE TABLE `iptable` (
    `ip` int(10) unsigned default NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.  INSERT INTO iptable VALUES (inet_aton('1.2.3.4'));

3. Query via Connector J something like:

   ...
  PreparedStatement pstmt = conn.prepareStatement("select inet_ntoa(ip) FROM iptable;" );
   ResultSetMetaData metaData = pstmt.getMetaData( );
   System.out.println( metaData.getColumnType( 1 ) );

Suggested fix:
Return data type as string, or at least document the different return type.

This is because 5.0.6 and later actually use the type that's returned from the server. 

If you don't agree with the server that human-readable strings from functions should be VARBINARY, then there's a configuration parameter for that. Set "functionsNeverReturnBlobs=true" in your JDBC URL.

(I happen to think they shouldn't be either, but have yet to convince the server developers of that).