You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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:
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.
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.
devanshu=# SELECT 'abc'::text || '\x00'::bytea AS result, pg_typeof('abc'::text || '\x00'::bytea) AS concat_result;
result | concat_result
---------+---------------
abc\x00 | text
(1 row)
I validated the rules in Postgres 18. It coerces it to Text type regardless of whether the binary string is valid or not.
bytea is coerced to text via its textual representation.
I don't think we can follow that behaviour for DataFusion, because our version of casting binary to string will try to interpret the actual bytes as UTF8, whereas it seems postgres will convert it to the hex representation?
I think we can proceed with assumption of preferring binary over string, and maybe with a PR up there can be discussion from others on how it looks and if it's preferred to instead cast to string in cases of binary || string 🤔
It's safer to make a proper binary concatenation for a binary/string mix. Spark does it more safely this way, while the majority (Postgres, DuckDB, and Snowflake) actually prefer casting to text for historical reasons.
I can draft a PR with an experimental implementation for both binary/string and binary/binary to verify that it fits our expectations.
What do you think of having a dialect-dependent implementation via customising ExprPlanner? For Spark/Databricks we plan a binary | binary -> text, while for others, we use binary. So we can have expected behaviour for every platform.
What do you think of having a dialect-dependent implementation via customising ExprPlanner? For Spark/Databricks we plan a binary | binary -> text, while for others, we use binary. So we can have expected behaviour for every platform.
Do we have existing precedent for such customization at that level?
What do you think of having a dialect-dependent implementation via customising ExprPlanner? For Spark/Databricks we plan a binary | binary -> text, while for others, we use binary. So we can have expected behaviour for every platform.
Do we have existing precedent for such customization at that level?
Not quiet. The idea is to make a plan using two UDFs in ExprPlanner::plan_binary_op - one ConcatBinary with text-to-binary and the classical Concat. The planner chooses between them based on a dialect. A good example is NestedFunctionPlanner::plan_binary_op
Since it had not been done before, I stumbled upon maintaining an actual config with a dialect in the expression planner, so we can react to set dialect dynamically. A session state cannot be passed to SessionStateBuilder::with_default_features, where default planners are instantiated early, while the state is rebuilt often via new_from_existing. I believe it could be done with some redesign, but it's worth confirming if it's worth it.
Maybe it's best to keep this simple and follow what Postgres does for now; we do have precedent for having Spark specific functions so if we want to follow Spark we could have that behaviour in the datafusion-spark crate 🤔
@Jefffrey I implemented a Postgres-like behaviour in this PR. If it looks good, I can also follow-up with a opt-in Spark binary-to-binary implementation
@theirix Thank you for the fix, but Binary || Binary still evaluates to Utf8, which does not work if they are not valid UTF-8 strings and does not match PostgreSQL's behavior:
$ datafusion-cli
> selectarrow_typeof(x'31'|| x'32');+--------------------------------------------+| arrow_typeof(Binary("49") || Binary("50")) |+--------------------------------------------+| Utf8 |+--------------------------------------------+1 row(s) fetched.Elapsed 0.003 seconds.
> selectx'ff'|| x'af';Optimizer rule 'simplify_expressions' failedcaused byArrow error: Invalid argument error: Encountered non UTF-8 data: invalid utf-8 sequence of 1 bytes from index 0
I suggest reopening this issue to discuss semantics again.
The issue is that the logic was introduced for the N-ary concat UDF, but not to the binary pipe operator (||) - they return text but have entirely different code. While concat allows invalid UTF-8 in binary inputs by deferring validation to the entire concatenated binary, the pipe operator doesn't do so. The optimiser tries to optimise this plan
Projection: CAST(Binary("255") AS Utf8) || CAST(Binary("175") AS Utf8)
EmptyRelation: rows=1
While I was summarising what other engines do, I discovered that Postgres is actually an outlier, and it handles binaries by emitting a hex representation, as pointed out above. This Postgres implementation for both UDF and operator emits binary as decoded text, not as hexified string SELECT x'c3a9' || 'hello'::text -> éhello instead of 1100001110101001hello with Postgres.
Engine
str+str
str+bin
bin+bin
PostgreSQL
text
Mixed text and hexified bytes
Binary (if both args are bytea) or hexified str
ClickHouse
String of bytes
String of bytes
String of bytes
Spark/Databricks
String
Type error (must cast)
Binary
DuckDB
String
Type error (must cast)
Binary
Snowflake
String
Type error (must cast)
Binary
MySQL
String
Binary string
Binary string
Now I'm wondering if we should provide bin+bin -> bin logic by changing concat UDF and providing a totally new BinaryConcat operator instead of StringConcat when both operands are binaries. It depends on the choice between Postgres and various AnalyticalDBs.
I suggest reopening this issue to discuss semantics again.
The issue is that the logic was introduced for the N-ary concat UDF, but not to the binary pipe operator (||) - they return text but have entirely different code. While concat allows invalid UTF-8 in binary inputs by deferring validation to the entire concatenated binary, the pipe operator doesn't do so. The optimiser tries to optimise this plan
Projection: CAST(Binary("255") AS Utf8) || CAST(Binary("175") AS Utf8)
EmptyRelation: rows=1
While I was summarising what other engines do, I discovered that Postgres is actually an outlier, and it handles binaries by emitting a hex representation, as pointed out above. This Postgres implementation for both UDF and operator emits binary as decoded text, not as hexified string SELECT x'c3a9' || 'hello'::text -> éhello instead of 1100001110101001hello with Postgres.
Engine str+str str+bin bin+bin
PostgreSQL text Mixed text and hexified bytes Binary (if both args are bytea) or hexified str
ClickHouse String of bytes String of bytes String of bytes
Spark/Databricks String Type error (must cast) Binary
DuckDB String Type error (must cast) Binary
Snowflake String Type error (must cast) Binary
MySQL String Binary string Binary string
Now I'm wondering if we should provide bin+bin -> bin logic by changing concat UDF and providing a totally new BinaryConcat operator instead of StringConcat when both operands are binaries. It depends on the choice between Postgres and various AnalyticalDBs.
@Jefffrey@alamb@jayzhan211 I would like to ask for your opinion on these findings and on which path we should follow.
Maybe it's better to only support str+str=str and bin+bin=bin for concat & ||, and any mixed ones (str+bin, bin+str) should be a type error like spark/duckdb/snowflake. That seems like the least surprising behaviour to me.
This feature is finally done. All concat UDFs (generic concat, concat_ws and Spark concat) and the pipe operator are fully consistent:
we allow binary vs binary concatenation, producing a binary type.
if the arguments are strings, it works as before, producing a string.
if the arguments are mixed types (strings vs binary), then binaries are coerced to strings.
It matches the behaviour of other analytical databases and Postgres - all of them actually cast binaries to text and produce a text output for mixed cases (correcting previous findings in the table above #12709 (comment))
@kawadakk, regarding your latest comment, I believe the behaviour is expected now
Activity
alamb commentedon Feb 26, 2025
I agree this still seems not to be supported
jayzhan211 commentedon Feb 27, 2025
ArrayConcat use signature
variadic_anywhich should be changed to something in ArraySignatureJefffrey commentedon Jan 31, 2026
Some pointers:
Look here for the existing
StringConcatoperator:datafusion/datafusion/expr-common/src/operator.rs
Lines 83 to 84 in a02e683
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
StringConcatunless we rename it) or potentially introduce a new operatorBinaryConcat. 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 || stringconcat (i.e. which common type we coerce to); we can't assume all binary is a valid string, but strings are always valid binary.devanshu0987 commentedon Feb 1, 2026
I validated the rules in Postgres 18. It coerces it to Text type regardless of whether the binary string is valid or not.
bytea is coerced to text via its textual representation.
Can we follow the same for Data Fusion?
Jefffrey commentedon Feb 1, 2026
I don't think we can follow that behaviour for DataFusion, because our version of casting binary to string will try to interpret the actual bytes as UTF8, whereas it seems postgres will convert it to the hex representation?
devanshu0987 commentedon Feb 2, 2026
Yes. I used Cursor to find the implementation. Seems it does a hex_encode.
src/backend/utils/adt/bytea.cJefffrey commentedon Feb 3, 2026
I think we can proceed with assumption of preferring binary over string, and maybe with a PR up there can be discussion from others on how it looks and if it's preferred to instead cast to string in cases of
binary || string🤔theirix commentedon Feb 25, 2026
It's safer to make a proper binary concatenation for a binary/string mix. Spark does it more safely this way, while the majority (Postgres, DuckDB, and Snowflake) actually prefer casting to text for historical reasons.
I can draft a PR with an experimental implementation for both binary/string and binary/binary to verify that it fits our expectations.
alamb commentedon Feb 27, 2026
In my opinion we should follow what the other systems do rather than inventing our own semantics
theirix commentedon Mar 2, 2026
What do you think of having a dialect-dependent implementation via customising
ExprPlanner? For Spark/Databricks we plan a binary | binary -> text, while for others, we use binary. So we can have expected behaviour for every platform.Jefffrey commentedon Mar 3, 2026
Do we have existing precedent for such customization at that level?
theirix commentedon Mar 4, 2026
Not quiet. The idea is to make a plan using two UDFs in
ExprPlanner::plan_binary_op- oneConcatBinarywith text-to-binary and the classicalConcat. The planner chooses between them based on a dialect. A good example isNestedFunctionPlanner::plan_binary_opSince it had not been done before, I stumbled upon maintaining an actual config with a dialect in the expression planner, so we can react to
set dialectdynamically. A session state cannot be passed toSessionStateBuilder::with_default_features, where default planners are instantiated early, while the state is rebuilt often vianew_from_existing. I believe it could be done with some redesign, but it's worth confirming if it's worth it.Jefffrey commentedon Mar 5, 2026
Maybe it's best to keep this simple and follow what Postgres does for now; we do have precedent for having Spark specific functions so if we want to follow Spark we could have that behaviour in the datafusion-spark crate 🤔
theirix commentedon Mar 10, 2026
@Jefffrey I implemented a Postgres-like behaviour in this PR. If it looks good, I can also follow-up with a opt-in Spark binary-to-binary implementation
1 remaining item
fix: binary string concat (#20787)
theirix commentedon Apr 4, 2026
Resolution: went with casting binaries to text - the most common behaviour except Spark
kawadakk commentedon Apr 6, 2026
@theirix Thank you for the fix, but
Binary || Binarystill evaluates toUtf8, which does not work if they are not valid UTF-8 strings and does not match PostgreSQL's behavior:theirix commentedon Apr 6, 2026
I suggest reopening this issue to discuss semantics again.
The issue is that the logic was introduced for the N-ary
concatUDF, but not to the binary pipe operator (||) - they return text but have entirely different code. Whileconcatallows invalid UTF-8 in binary inputs by deferring validation to the entire concatenated binary, the pipe operator doesn't do so. The optimiser tries to optimise this planand fails trying to evaluate literals. The same error with
concat. It doesn't happen when arguments are valid UTF-8 binaries as per added SLTs - main...theirix:datafusion:binary-concat-fixes . Of course, if we follow Postgres' behaviour with hexifying binary output, it won't happen.While I was summarising what other engines do, I discovered that Postgres is actually an outlier, and it handles binaries by emitting a hex representation, as pointed out above. This Postgres implementation for both UDF and operator emits binary as decoded text, not as hexified string
SELECT x'c3a9' || 'hello'::text->éhelloinstead of1100001110101001hellowith Postgres.Now I'm wondering if we should provide bin+bin -> bin logic by changing
concatUDF and providing a totally newBinaryConcatoperator instead ofStringConcatwhen both operands are binaries. It depends on the choice between Postgres and various AnalyticalDBs.theirix commentedon Apr 19, 2026
@Jefffrey @alamb @jayzhan211 I would like to ask for your opinion on these findings and on which path we should follow.
fix: binary string concat (apache#20787)
Jefffrey commentedon Apr 21, 2026
Maybe it's better to only support str+str=str and bin+bin=bin for concat &
||, and any mixed ones (str+bin, bin+str) should be a type error like spark/duckdb/snowflake. That seems like the least surprising behaviour to me.feat: support binary arguments for StringConcat operator (apache#21883)
theirix commentedon May 6, 2026
Binary operator support has been merged, and I am working on a final part to synchronise the operator and UDF behaviour.
feat: support mixed binary and string types for concat UDFs (apache#2…
theirix commentedon Jun 21, 2026
This feature is finally done. All concat UDFs (generic concat, concat_ws and Spark concat) and the pipe operator are fully consistent:
It matches the behaviour of other analytical databases and Postgres - all of them actually cast binaries to text and produce a text output for mixed cases (correcting previous findings in the table above #12709 (comment))
@kawadakk, regarding your latest comment, I believe the behaviour is expected now