Bug #99480 char* UDF returning hexadecimal string rather than string
Submitted: 7 May 2020 16:45 Modified: 8 May 2020 20:06
Reporter: Luciano Nicastro Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S1 (Critical)
Version:8.0.20 OS:Ubuntu (18.04.4)
Assigned to: CPU Architecture:x86
Tags: udf, UNHEX

[7 May 2020 16:45] Luciano Nicastro 
Description:
Any char* UDF (including those in udf_example.cc) is returning a cryptic hexadecimal string instead of the correct ASCII encoded one. The very same functions work fine on 5.7.

Not sure it is related, but the function UNHEX() (and the X'...' notation) is not working as expected too.

How to repeat:
1. Compile and install into the plugin dir. the source code distributed "udf_example.so"

2. Load a function

mysql> CREATE FUNCTION lookup RETURNS STRING SONAME 'udf_example.so';

3. Use it:

mysql> SELECT lookup('mail.google.com');
       -> 0x3231362E35382E3230362E3337   

Expected result (MySQL 5.7):

mysql> SELECT lookup('mail.google.com');
       -> 216.58.206.37

Moreover:

mysql> SELECT X'616263', HEX('abc'), UNHEX(HEX('abc'));
       -> 0x616263, 616263, 0x616263

Expected result (MySQL 5.7):

mysql> SELECT X'616263', HEX('abc'), UNHEX(HEX('abc'));
        -> 'abc', 616263, 'abc'

See https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_hex
[8 May 2020 10:22] MySQL Verification Team 
Could it be a client thing?   If you using a 5.7 client, does it return expected result.

In 8.0 client,  type "status" and see this:

Binary data as:		Hexadecimal

Then run with --column-type-info and you'll see the outputs are having BINARY collation and flagged as BINARY.

So,  in MySQL 8.0 client,  run it like:

mysql --binary-as-hex=0

and see if it solves this
[8 May 2020 11:07] Luciano Nicastro 
Thanks!
I confirm that invoking the client with "--binary-as-hex=0" fixes things. I had missed this (for me) crucial change! I should have read

https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html

where it is documented that "--binary-as-hex=1" is the default as of MySQL  8.0.19!
Still I could not have guessed easily that a "char*" function is affected by that. Would it be the case to report the info in the UDFs documentation section?

Still not sure what's the logic behind it.
Thanks again.