Support for hex encoding/decoding for `Binary` in sql queries

[ozgrakkurt]

Hi,

I want to query parquet files by a binary column. I have the scalar value to query by in hex form and I want to run an sql query.

I tried to_hex and postgres style encode but neither seemed to work.

SELECT block_number from log
WHERE log.block_number >= 10000000
	AND log.block_number < 10001000
	AND encode(log.address, 'hex') = '0xced4e93198734ddaff8492d525bd258d49eb388e'

or

SELECT block_number from log
WHERE log.block_number >= 10000000
	AND log.block_number < 10001000
	AND to_hex(log.address) = '0xced4e93198734ddaff8492d525bd258d49eb388e'

I get this error with to_hex:

failed to execute query:\nError during planning: Coercion from [Binary] to the signature Uniform(1, [Int64]) failed.

Would it be possible to implement this? also how can I enter a binary literal so maybe I can run this query now?

[alamb]

Hi @ozgrakkurt -- I definitely think it would be possible to implement

The current implementation simply takes String -- we would have to extend it to take Binary as well

https://github.com/apache/arrow-datafusion/blob/25725c0cfd1bc54f735c41a57c13d9b0e15c8d2b/datafusion/physical-expr/src/string_expressions.rs#L568

Here are some of the other places: https://github.com/apache/arrow-datafusion/search?q=to_hex&type=code

[ozgrakkurt]

@alamb I think implementing encode and decode functions like postgresql would be the best. I can work on it if you give me a general direction

[alamb]

@alamb I think implementing encode and decode functions like postgresql would be the best. I can work on it if you give me a general direction

Thanks @ozgrakkurt -- sounds good

I would find a similar function -- perhaps find one that looks similar in https://docs.rs/datafusion-expr/11.0.0/datafusion_expr/enum.BuiltinScalarFunction.html

The data type is DataType::Binary and DataType::LargeBinary -- and the corresponding Array type is https://docs.rs/arrow/21.0.0/arrow/array/type.BinaryArray.html

[alamb]

I believe this may be resolved in #6767

[ozgrakkurt]

isn't fully resolved by #6767 because there is still no functionality to convert the selected binary column to hex like:

SELECT encode(my_col, 'hex') from my_table;

As far as I understand

[ozgrakkurt]

I wanted to work on this but many things got in between, I'll try to get back to it

[alamb]

It looks like we need to support to_hex for Binary columns as well