feat(join): support `_.col.upper()` and `lambda left, right: ...` as predicates

[NickCrews]

Is your feature request related to a problem?

I have these two tables, which I want to join regardless of capitalization. I want to be able to do this:

import ibis
tl = ibis.memtable({"f": ["a", "b", "c"]})
tr = ibis.memtable({"f": ["A", "B", "Z"]})
tl.join(tr, ibis._.f.upper())
# expect {"f": ["a", "b"], "f_right": ["A", "B"]}
# Currently get `SignatureValidationError: JoinLink(...)`

Currently I have to actually pass tl.f.upper() == tr.f.upper().

Another problem I have is reusing my predicates. I want to be able to do all of these:

def equalish(col: str):
    def pred(left: ibis.Table, right: ibis.Table):
        return (left[col] == right[col]) | (left[col].isnull() & right[col].isnull())
   return pred

preds = [
    "a",
    _.x.upper(),
    lambda t: t.y.abs(),
    lambda l, r: l.foo == r.bar,
    (_.baz, _.qux.upper()),
    equalish("x"),
    equalish("y"),
]
a.join(b, preds)
c.join(d, preds)

Currently, I have to reference the tables directly in the predicates, so I have to actually do the binding at right-before-join time, which prevents me from being able to reuse them.

def equalish(left, right, col):
    return (left[col] == right[col]) | (left[col].isnull() & right[col].isnull())

a.join(b, [equalish(a, b, "x"), equalish(a, b, "y")])
c.join(d, [equalish(c, d, "x"), equalish(c, d, "y")])

Describe the solution you'd like

See below

Code of Conduct

• I agree to follow this project's Code of Conduct

[NickCrews]

After @kszucs's comment, I think this needs more general discussion of the spec before I dive into an implementation.

After thinking about it and experimenting with current behavior, I have a more generalized proposal for how to adjust join conditions.

"Single" Values

Currently, if given something that is "singular", it should be resolved against each table individually, and then combined with ==. This is good behavior, I want to keep this, and just add the ability to pass _.col.

lt.join(rt, "f")

Currently works as expected. My proposals below are just generalizations of this.

lt.join(rt, lambda t: t.f)

Currently, works as expected, the same as lt.join(rt, "f")

After, it works the same.

lt.join(rt, _.f)

Currently, it fails (resolves against lt, giving lt.f, which isn't a BooleanColumn)

After, it resolves to lt.f == rt.f, same semantics of lt.join(rt, "f") More generally, we should support lt.join(rt, _.anything_that_join_supports)

If we change this behavior, then this would break SOME current usages. It would NOT break when you reference the same expression in both sides, eg lt.join(rt, _.f == rt.f) or lt.join(rt, _.f.abs().notnull() == rt.f.abs().notnull()).
Currently, this works by binding any Deferreds to the left table, resulting in (lt.f == rt.f).
After the change, after resolving against each table individually and then combining with ==, we would get a condition of
(lt.f == rt.f) == (rt.f == rt.f) which is the same as (lt.f == rt.f) == True which is the same as (lt.f == rt.f), which is the same as the current behavior! Double check my algebra/logic here, I might be wrong. We could add a test before the change and ensure that it doesn't start failing.

The change WOULD break usages where the you reference different expressions in both sides such as lt.join(rt, _.left_id == rt.right_id). This is currently tested in one test, but I don't think it is publicly shown in any examples or docs, so I hope it should be fairly uncommon.

However, a "better" way of doing this, that I think we should actually document, is lt.join(rt, lambda l, r: l.f == r.f), see below. Even though lt.join(rt, _.f == rt.f) "works", I don't like it: It feels arbitrary how it binds to the left table, not the right. Also, I don't see how it is that useful, since you still need the direct reference to rt, which makes it so you can't reuse it in other join conditions. Since you are already hardcoding in the rt in there, it's not that much more of a pain to also hardcode in the lt.

"Pair" Values

We already support this with 2-tuples eg lt.join(rt, [("f", _.f + 1), (_.f.abs(), _.f + 1)]).
I think we should extend this to lambdas.

lt.join(rt, lambda l, r: l.f == r.f)

Currently, it fails with TypeError: <lambda>() missing 1 required positional argument: 'r'
After, it works. This is the preferred new spelling of lt.join(rt, _.f == rt.f)

In all of these, we should probably support recursion. eg lt.join(rt, lambda l, r: (lambda y: _.f)) should work.
It looks like currently lt.join(rt, lambda x: (lambda y: y.f)) works as expected, so maybe this wouldn't actually be a huge lift.

[kszucs]

Don't want to further complicate it, but deferreds actually support multiple placeholders, not just _, like:

l, r = var("l"), var("r")

left_table.join(right_table, l.field_a == r.field_a && r.field_b == l.field_b)

[NickCrews]

Oh wow, I didn't know about that. That's not a part of the public API though, right? Are you saying there is a way to use this API to support one of the use cases I describe above?

Am I correct here: It requires the relation name to match the var name, eg t.name("left").join(t2.name("right"), var("x").field_g == var("y").field_h) would fail? If it does require a match, then this would not allow for reusing predicates between diffferent joins.

[NickCrews]

Ah, re-reading this now, I think @kszucs is proposing that as a user I could do

from ibis import l, r
left_table.join(right_table, l.field_a == r.field_a && r.field_b == l.field_b)

I think I prefer the lambda l, r: ... syntax that I proposed above. It is less magical, and requires one less import. I would vote for just supporting the lambda syntax, and NOT supporting the from ibis import l, r syntax.

@cpcloud do you have any thoughts on the suggestions in this issue? Should I bother continuing with the linked PR? I still find this feature addition appealing. Any suggestions or dealbreakers I would need to think about?