Repository: ibis-project/ibis

Issue:
bug: can't use deferred expression in join predicate
We don't support the use of deferred expressions in (at least) equality join predicates:

Reproducer:
```python
import ibis
from ibis import _ as C


t1 = ibis.table(
    [("c", "str"), ("d", "int64")],
    name="table0",
)
t2 = ibis.table(
    [("a", "str"), ("b", "int64")],
    name="table1",
)
t3 = ibis.table([("e", "str"), ("f", "int64")], name="table2")

t1.join(t2, t1.c == t2.a).join(t3, C.c == t3.e)
```

Traceback:
```python
---------------------------------------------------------------------------
NotImplementedError                       Traceback (most recent call last)
Cell In [15], line 1
----> 1 t1.join(t2, t1.c == t2.a).join(t3, C.c == t3.e)

File ~/github.com/ibis-project/ibis/ibis/expr/types/relations.py:1113, in Table.join(left, right, predicates, how, suffixes)
   1099 _join_classes = {
   1100     'inner': ops.InnerJoin,
   1101     'left': ops.LeftJoin,
   (...)
   1109     'cross': ops.CrossJoin,
   1110 }
   1112 klass = _join_classes[how.lower()]
-> 1113 expr = klass(left, right, predicates).to_expr()
   1115 # semi/anti join only give access to the left table's fields, so
   1116 # there's never overlap
   1117 if how in ("semi", "anti"):

File ~/github.com/ibis-project/ibis/ibis/common/grounds.py:26, in BaseMeta.__call__(cls, *args, **kwargs)
     25 def __call__(cls, *args, **kwargs) -> Base:
---> 26     return cls.__create__(*args, **kwargs)

File ~/github.com/ibis-project/ibis/ibis/common/grounds.py:106, in Annotable.__create__(cls, *args, **kwargs)
    102 @classmethod
    103 def __create__(cls, *args, **kwargs) -> Annotable:
    104     # construct the instance by passing the validated keyword arguments
    105     kwargs = cls.__signature__.validate(*args, **kwargs)
--> 106     return super().__create__(**kwargs)

File ~/github.com/ibis-project/ibis/ibis/common/grounds.py:35, in Base.__create__(cls, *args, **kwargs)
     33 @classmethod
     34 def __create__(cls, *args, **kwargs) -> Base:
---> 35     return type.__call__(cls, *args, **kwargs)

File ~/github.com/ibis-project/ibis/ibis/expr/operations/relations.py:172, in Join.__init__(self, left, right, predicates, **kwargs)
    166     new = right = ops.SelfReference(right)
    167     predicates = [
    168         L.sub_for(pred, {old: new}) if isinstance(pred, ops.Node) else pred
    169         for pred in predicates
    170     ]
--> 172 predicates = _clean_join_predicates(left, right, predicates)
    174 super().__init__(left=left, right=right, predicates=predicates, **kwargs)

File ~/github.com/ibis-project/ibis/ibis/expr/operations/relations.py:113, in _clean_join_predicates(left, right, predicates)
    111     pred = pred.to_expr()
    112 elif not isinstance(pred, ir.Expr):
--> 113     raise NotImplementedError
    115 if not isinstance(pred, ir.BooleanColumn):
    116     raise com.ExpressionError('Join predicate must be comparison')

NotImplementedError: 
```

Discussion:
github-actions[bot]: ## Test Results
         6 files           6 suites   7m 17s [:stopwatch:](https://github.com/EnricoMi/publish-unit-test-result-action/blob/v1.20/README.md#the-symbols "duration of all tests")
  3 264 tests   3 238 [:heavy_check_mark:](https://github.com/EnricoMi/publish-unit-test-result-action/blob/v1.20/README.md#the-symbols "passed tests")   26 [:zzz:](https://github.com/EnricoMi/publish-unit-test-result-action/blob/v1.20/README.md#the-symbols "skipped / disabled tests") 0 [:x:](https://github.com/EnricoMi/publish-unit-test-result-action/blob/v1.20/README.md#the-symbols "failed tests")
19 530 runs  19 374 [:heavy_check_mark:](https://github.com/EnricoMi/publish-unit-test-result-action/blob/v1.20/README.md#the-symbols "passed tests") 156 [:zzz:](https://github.com/EnricoMi/publish-unit-test-result-action/blob/v1.20/README.md#the-symbols "skipped / disabled tests") 0 [:x:](https://github.com/EnricoMi/publish-unit-test-result-action/blob/v1.20/README.md#the-symbols "failed tests")

Results for commit 6522f29b.

[test-results]:data:application/gzip;base64,H4sIAP+LZWMC/03MSw7CIBSF4a00jB3wfrgZAwgJsS2Gwsi4d2+rYCc3+b+bnBeKaQ4buk7yMqGtpTri3oqtKa+QnCkAeNX9yajkPW9b8/4wpv/2SE8wKodEm2YQPCCUkstPSlv3WWIE69lniWGKD/vOEiG7nGaPPq/6vCypQiApKI3UOC60Dc6oiKNh3MJxXhgVNBYKY6zR+wOG9OfgDgEAAA==

codecov[bot]: # [Codecov](https://codecov.io/gh/ibis-project/ibis/pull/4772?src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project) Report
> Merging [#4772](https://codecov.io/gh/ibis-project/ibis/pull/4772?src=pr&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project) (6522f29) into [master](https://codecov.io/gh/ibis-project/ibis/commit/438eb19f261b144c4c993eaf9c8ad3a475c22827?el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project) (438eb19) will **decrease** coverage by `0.34%`.
> The diff coverage is `66.66%`.

[![Impacted file tree graph](https://codecov.io/gh/ibis-project/ibis/pull/4772/graphs/tree.svg?width=650&height=150&src=pr&token=sE9aT1Le0L&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project)](https://codecov.io/gh/ibis-project/ibis/pull/4772?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project)

```diff
@@            Coverage Diff             @@
##           master    #4772      +/-   ##
==========================================
- Coverage   92.46%   92.12%   -0.35%     
==========================================
  Files         192      189       -3     
  Lines       21510    21290     -220     
  Branches     2988     2959      -29     
==========================================
- Hits        19890    19613     -277     
- Misses       1205     1274      +69     
+ Partials      415      403      -12     
```

| [Impacted Files](https://codecov.io/gh/ibis-project/ibis/pull/4772?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project) | Coverage Δ | |
|---|---|---|
| [ibis/expr/operations/relations.py](https://codecov.io/gh/ibis-project/ibis/pull/4772/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project#diff-aWJpcy9leHByL29wZXJhdGlvbnMvcmVsYXRpb25zLnB5) | `98.06% <66.66%> (+0.01%)` | :arrow_up: |
| [ibis/backends/snowflake/registry.py](https://codecov.io/gh/ibis-project/ibis/pull/4772/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project#diff-aWJpcy9iYWNrZW5kcy9zbm93Zmxha2UvcmVnaXN0cnkucHk=) | `0.00% <0.00%> (-93.34%)` | :arrow_down: |
| [ibis/backends/snowflake/datatypes.py](https://codecov.io/gh/ibis-project/ibis/pull/4772/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project#diff-aWJpcy9iYWNrZW5kcy9zbm93Zmxha2UvZGF0YXR5cGVzLnB5) | `0.00% <0.00%> (-89.40%)` | :arrow_down: |
| [ibis/backends/snowflake/\_\_init\_\_.py](https://codecov.io/gh/ibis-project/ibis/pull/4772/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project#diff-aWJpcy9iYWNrZW5kcy9zbm93Zmxha2UvX19pbml0X18ucHk=) | `4.91% <0.00%> (-83.61%)` | :arrow_down: |
| [ibis/backends/base/sql/alchemy/\_\_init\_\_.py](https://codecov.io/gh/ibis-project/ibis/pull/4772/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project#diff-aWJpcy9iYWNrZW5kcy9iYXNlL3NxbC9hbGNoZW15L19faW5pdF9fLnB5) | `90.25% <0.00%> (-1.03%)` | :arrow_down: |
| [ibis/backends/postgres/registry.py](https://codecov.io/gh/ibis-project/ibis/pull/4772/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project#diff-aWJpcy9iYWNrZW5kcy9wb3N0Z3Jlcy9yZWdpc3RyeS5weQ==) | `93.75% <0.00%> (-0.90%)` | :arrow_down: |
| [ibis/util.py](https://codecov.io/gh/ibis-project/ibis/pull/4772/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project#diff-aWJpcy91dGlsLnB5) | | |
| [ibis/compat.py](https://codecov.io/gh/ibis-project/ibis/pull/4772/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project#diff-aWJpcy9jb21wYXQucHk=) | | |
| [ibis/\_\_init\_\_.py](https://codecov.io/gh/ibis-project/ibis/pull/4772/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project#diff-aWJpcy9fX2luaXRfXy5weQ==) | | |
| [ibis/backends/impala/\_\_init\_\_.py](https://codecov.io/gh/ibis-project/ibis/pull/4772/diff?src=pr&el=tree&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project#diff-aWJpcy9iYWNrZW5kcy9pbXBhbGEvX19pbml0X18ucHk=) | `86.14% <0.00%> (+0.21%)` | :arrow_up: |
| ... and [1 more](https://codecov.io/gh/ibis-project/ibis/pull/4772/diff?src=pr&el=tree-more&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=ibis-project) | |


Patch:
diff --git a/ibis/expr/operations/relations.py b/ibis/expr/operations/relations.py
index eae216792..c96dd53a3 100644
--- a/ibis/expr/operations/relations.py
+++ b/ibis/expr/operations/relations.py
@@ -13,6 +13,7 @@ import ibis.expr.schema as sch
 import ibis.expr.types as ir
 import ibis.util as util
 from ibis.common.annotations import attribute
+from ibis.expr.deferred import Deferred
 from ibis.expr.operations.core import Named, Node, Value
 from ibis.expr.operations.generic import TableColumn
 from ibis.expr.operations.logical import Equals, ExistsSubquery, NotExistsSubquery
@@ -109,6 +110,9 @@ def _clean_join_predicates(left, right, predicates):
             pred = left.to_expr()[pred] == right.to_expr()[pred]
         elif isinstance(pred, Value):
             pred = pred.to_expr()
+        elif isinstance(pred, Deferred):
+            # resolve deferred expressions on the left table
+            pred = pred.resolve(left.to_expr())
         elif not isinstance(pred, ir.Expr):
             raise NotImplementedError
 


Test patch:
diff --git a/ibis/tests/expr/test_table.py b/ibis/tests/expr/test_table.py
index a9f1e0e76..1590b07ce 100644
--- a/ibis/tests/expr/test_table.py
+++ b/ibis/tests/expr/test_table.py
@@ -794,6 +794,14 @@ def test_join_no_predicate_list(con):
     assert_equal(joined, expected)
 
 
+def test_join_deferred(con):
+    region = con.table("tpch_region")
+    nation = con.table("tpch_nation")
+    res = region.join(nation, _.r_regionkey == nation.n_regionkey)
+    exp = region.join(nation, region.r_regionkey == nation.n_regionkey)
+    assert_equal(res, exp)
+
+
 def test_asof_join():
     left = ibis.table([('time', 'int32'), ('value', 'double')])
     right = ibis.table([('time', 'int32'), ('value2', 'double')])