Binary string (`BYTEA`, `Binary`) concatenation

[kawadakk]

Is your feature request related to a problem or challenge?

Currently there doesn't seem to be a direct way to concatenate binary strings in SQL expressions.

• lhs || rhs does not accept Binary arguments.
• concat(lhs, rhs) coerces both sides to Utf8, which does not work if they are not valid UTF-8 strings.
• array_concat(lhs, rhs) only works for List, not Binary.
• decode(encode(lhs, 'hex') || encode(rhs, 'hex'), 'hex') works, but this is verbose and inefficient.

Describe the solution you'd like

The || operator could be extended to support Binary arguments, following the behavior of other SQL engines, such as SQLite and PostgreSQL.

Describe alternatives you've considered

No response

Additional context

No response

[alamb]

I agree this still seems not to be supported

> select arrow_typeof(x'1234');
+-------------------------------+
| arrow_typeof(Binary("18,52")) |
+-------------------------------+
| Binary                        |
+-------------------------------+
1 row(s) fetched.
Elapsed 0.003 seconds.

> select x'1234' || x'5678';
Error during planning: Cannot infer common string type for string concat operation Binary || Binary

[jayzhan211]

ArrayConcat use signature variadic_any which should be changed to something in ArraySignature

[Jefffrey]

Some pointers:

Look here for the existing StringConcat operator:

datafusion/datafusion/expr-common/src/operator.rs

Lines 83 to 84 in a02e683

	/// String concat
	StringConcat,

We should look at places it is used and either try to extend it to include binary (although it might be weird since the name is StringConcat unless we rename it) or potentially introduce a new operator BinaryConcat. We should also consider the UDF:

https://github.com/apache/datafusion/blob/a02e6836e7d449008eef592b177ea282595e027a/datafusion/functions/src/string/concat.rs

One thing to keep in mind is how we handle binary || string concat (i.e. which common type we coerce to); we can't assume all binary is a valid string, but strings are always valid binary.