Does Acero support multi-column joins?

[severinson]

Hey. I’m trying to use Acero to join two Arrow tables on multiple columns. I’ve created a Substrait plan for doing so containing an and expression with one equals expression per pair of columns. However, I get the error ”Only equal or is_not_distinct_from are supported for join key”. Is there a way to produce a Substrait plan that would do such a join? Joining on one column works since I can then use an equals expression directly. Thanks,

[amoeba]

Hi @severinson, based on the description in #31851 and the relevant code in https://github.com/apache/arrow/blob/main/cpp/src/arrow/engine/substrait/relation_internal.cc, I don't think Acero's Substrait consumer currently supports multiple join keys. Acero itself does, however. Is Substrait a key requirement for what you're doing?

[severinson]

Hey. Thanks for the info @amoeba. Substrait is not a requirement. I’m using Acero via Java. Do you know if I can use Acero from Java without going via a Substrait plan?

[amoeba]

Do you know if I can use Acero from Java without going via a Substrait plan?

It doesn't look like it, no. I only see bindings to Acero through Substrait and no direct bindings which is what you'd need.

cc @westonpace @vibhatha for thoughts

[westonpace]

This matches my understanding as well. The API surface for Acero is large and making bindings would be a pretty daunting task. I think Substrait was chosen to avoid this.

Note: Substrait itself has no problem expressing multi-column joins (which it appears you are aware of since you mention it in your question 😄). The join relation takes a join expression. That expression could be left.a = right.a AND left.b = right.b.

Probably the lowest effort fix would be to adjust Acero's Substrait consumer to handle the multiple keys. The logic is here:

arrow/cpp/src/arrow/engine/substrait/relation_internal.cc

Line 715 in 520ae44

acero::JoinKeyCmp join_key_cmp;

It's pretty simplistic and expects the expression to be an equals expression. However, if there would multiple join keys the expression would be an AND expression. So some better parsing of the expression tree would be needed.

[severinson]

That’s what I was afraid of 😅. All right, this has been very helpful. Thank you.