built-in function: Support `unhex()` function

[jingchen2222]

Is your feature request related to a problem? Please describe.

unhex(expr)

Converts hexadecimal expr to binary.

Examples:

> SELECT decode(unhex('537061726B2053514C'), 'UTF-8');
 Spark SQL

reference:

https://spark.apache.org/docs/2.3.0/api/sql/#unhex

Describe the solution you'd like

We do provide a built-in function development guide for you. Please check OpenMLDB Build-In Function Develop Guide or the Chinese version for help.

Please make sure you:

• [ ] Add built-in C++ function in src/udf/udf.h and src/udf/udf.cc
• [ ] Register function to default library from function void DefaultUdfLibrary::IniStringUdf() in src/udf/default_udf_library.cc.
• [ ] Add related unit test in src/codegen/udf_ir_builder_test.cc
• [ ] Documenting function

If you have any questions or concerns, please let us know. We appreciate every piece of code, comment. Describe alternatives you've considered A clear and concise description of any alternative solutions or features you've considered.

Additional context Add any other context or screenshots about the feature request here.

[aucker]

Please assign this task to me

[lumianph]

@aucker i have assigned it to you, thanks~

[aucker]

Hi, I have a question. I just downloaded and tried the Spark SQL on the commmand line, when I type

spark-sql> select unhex('537061726B2053514C');
Spark SQL
Time taken: 0.049 seconds, Fetched 1 row(s)

I got a utf-8 string, not the binary 010100110111000001100001011100100110101100100000010100110101000101001100. Then I tried

spark-sql> select decode(unhex('537061726B2053514C'), 'UTF-8');
Spark SQL
Time taken: 0.047 seconds, Fetched 1 row(s)

same result, looks like the unhex function just unconvert the hex functions.

I also tried:

spark-sql> select hex(11);
B
Time taken: 0.076 seconds, Fetched 1 row(s)

But when I unhex it, I got:

spark-sql> select unhex('B');


Time taken: 0.038 seconds, Fetched 1 row(s)

So I think in spark sql, the unhex function is just undo what the hex function do when hex got a valid string rather an integer, it is not convert hex to binary, am I right?

Please give me some instructions, thanks~

[aceforeverd]

Hi, I have a question. I just downloaded and tried the Spark SQL on the commmand line, when I type

spark-sql> select unhex('537061726B2053514C');
Spark SQL
Time taken: 0.049 seconds, Fetched 1 row(s)

I got a utf-8 string, not the binary 010100110111000001100001011100100110101100100000010100110101000101001100. Then I tried

spark-sql> select decode(unhex('537061726B2053514C'), 'UTF-8');
Spark SQL
Time taken: 0.047 seconds, Fetched 1 row(s)

same result, looks like the unhex function just unconvert the hex functions.

I also tried:

spark-sql> select hex(11);
B
Time taken: 0.076 seconds, Fetched 1 row(s)

hex(11) is not same as hex('11')

But when I unhex it, I got:

spark-sql> select unhex('B');


Time taken: 0.038 seconds, Fetched 1 row(s)

So I think in spark sql, the unhex function is just undo what the hex function do when hex got a valid string rather an integer, it is not convert hex to binary, am I right?

@aucker Performs the inverse operation of HEX(str). That is, it interprets each pair of hexadecimal digits in the argument as a number and converts it to the character represented by the number. The resulting characters are returned as a binary string. Mariadb's description is more detailed. unhex what hexed and return binary string, not 01010 binary.