Repository: ibis-project/ibis

Issue:
bug: SignatureValidationError from DerefMap during repeated joins
### What happened?

```python
t1 = ibis.memtable(
    np.array([[1, 3, 4, 5, 0], [1, 4, 5, 7, 1], [0, 3, 1, 5, 2], [0, 4, 2, 7, 3]]),
    columns=["i", "j", "lower", "upper", "label"],
)
t1v = t1.view()
t2 = t1.inner_join(t1v, ["i", "j"]).select("i", "j", coeff=1, var1=t1.label, var2=t1v.label)

t3 = ibis.memtable(np.array([[0, 2], [1, 3], [2, 5]]), columns=["i", "value"])
t2.inner_join(t3, ["i"])
```
is raising the following `SignatureValidationError`:
```
---------------------------------------------------------------------------
SignatureValidationError                  Traceback (most recent call last)
Cell In[85], line 9
      6 t2 = t1.inner_join(t1v, [\"i\", \"j\"]).select(\"i\", \"j\", coeff=1, var1=t1.label, var2=t1v.label)
      8 t3 = ibis.memtable(np.array([[0, 2], [1, 3], [2, 5]]), columns=[\"i\", \"value\"])
----> 9 t2.inner_join(t3, [\"i\"])

File ~/.local/conda/envs/linopy/lib/python3.12/site-packages/ibis/expr/types/relations.py:89, in _regular_join_method.<locals>.f(self, right, predicates, lname, rname)
     58 def f(  # noqa: D417
     59     self: ir.Table,
     60     right: ir.Table,
   (...)
     67     rname: str = \"{name}_right\",
     68 ) -> ir.Table:
     69     \"\"\"Perform a join between two tables.
     70 
     71     Parameters
   (...)
     87         Joined table
     88     \"\"\"
---> 89     return self.join(right, predicates, how=how, lname=lname, rname=rname)

File ~/.local/conda/envs/linopy/lib/python3.12/site-packages/ibis/expr/types/relations.py:3247, in Table.join(left, right, predicates, how, lname, rname)
   3091 \"\"\"Perform a join between two tables.
   3092 
   3093 Parameters
   (...)
   3243 └─────────┴───────────────────┴───────────────┴───────────────────┘
   3244 \"\"\"
   3245 from ibis.expr.types.joins import Join
-> 3247 return Join(left.op()).join(
   3248     right, predicates, how=how, lname=lname, rname=rname
   3249 )

File ~/.local/conda/envs/linopy/lib/python3.12/site-packages/ibis/expr/types/joins.py:254, in Join.join(self, right, predicates, how, lname, rname)
    252 # bind and dereference the predicates
    253 preds = prepare_predicates(chain, right, predicates)
--> 254 preds = flatten_predicates(preds)
    255 if not preds and how != \"cross\":
    256     # if there are no predicates, default to every row matching unless
    257     # the join is a cross join, because a cross join already has this
    258     # behavior
    259     preds.append(ops.Literal(True, dtype=\"bool\"))

File ~/.local/conda/envs/linopy/lib/python3.12/site-packages/ibis/expr/rewrites.py:191, in flatten_predicates(node)
    187     else:
    188         # halt and yield the node
    189         return False, node
--> 191 return list(traverse(predicate, node))

File ~/.local/conda/envs/linopy/lib/python3.12/site-packages/ibis/common/graph.py:630, in traverse(fn, node)
    616 def traverse(
    617     fn: Callable[[Node], tuple[bool | Iterable, Any]], node: Iterable[Node] | Node
    618 ) -> Iterator[Any]:
    619     \"\"\"Utility for generic expression tree traversal.
    620 
    621     Parameters
   (...)
    628 
    629     \"\"\"
--> 630     nodes = list(_flatten_collections(promote_list(node)))
    631     queue: deque[Node] = deque(reversed(nodes))
    632     seen: set[Node] = set()

File ~/.local/conda/envs/linopy/lib/python3.12/site-packages/ibis/util.py:110, in promote_list(val)
    108     return [val]
    109 elif is_iterable(val):
--> 110     return list(val)
    111 elif val is None:
    112     return []

File ~/.local/conda/envs/linopy/lib/python3.12/site-packages/ibis/expr/types/joins.py:162, in prepare_predicates(chain, right, predicates, comparison)
    160 reverse = {ops.Field(chain, k): v for k, v in chain.values.items()}
    161 deref_right = DerefMap.from_targets(right)
--> 162 deref_left = DerefMap.from_targets(chain.tables, extra=reverse)
    163 deref_both = DerefMap.from_targets([*chain.tables, right], extra=reverse)
    165 left, right = chain.to_expr(), right.to_expr()

File ~/.local/conda/envs/linopy/lib/python3.12/site-packages/ibis/expr/rewrites.py:102, in DerefMap.from_targets(cls, rels, extra)
     99 if extra is not None:
    100     subs.update(extra)
--> 102 return cls(rels, subs, ambigs)

File ~/.local/conda/envs/linopy/lib/python3.12/site-packages/ibis/common/bases.py:72, in AbstractMeta.__call__(cls, *args, **kwargs)
     52 def __call__(cls, *args, **kwargs):
     53     \"\"\"Create a new instance of the class.
     54 
     55     The subclass may override the `__create__` classmethod to change the
   (...)
     70 
     71     \"\"\"
---> 72     return cls.__create__(*args, **kwargs)

File ~/.local/conda/envs/linopy/lib/python3.12/site-packages/ibis/common/grounds.py:119, in Annotable.__create__(cls, *args, **kwargs)
    116 @classmethod
    117 def __create__(cls, *args: Any, **kwargs: Any) -> Self:
    118     # construct the instance by passing only validated keyword arguments
--> 119     kwargs = cls.__signature__.validate(cls, args, kwargs)
    120     return super().__create__(**kwargs)

File ~/.local/conda/envs/linopy/lib/python3.12/site-packages/ibis/common/annotations.py:497, in Signature.validate(self, func, args, kwargs)
    494         this[name] = result
    496 if errors:
--> 497     raise SignatureValidationError(
    498         \"{call} has failed due to the following errors:{errors}\
\
Expected signature: {sig}\",
    499         sig=self,
    500         func=func,
    501         args=args,
    502         kwargs=kwargs,
    503         errors=errors,
    504     )
    506 return this

SignatureValidationError: DerefMap([<ibis.expr.operations.relations.JoinReference object at 0x158ab5f70>, <ibis.expr.operations.relations.SelfReference object at 0x157f0b6b0>], {<ibis.expr.operations.relations.Field object at 0x157f2d240>: <ibis.expr.operations.relations.Field object at 0x157f2d240>, <ibis.expr.operations.relations.Field object at 0x1586f81a0>: <ibis.expr.operations.relations.Field object at 0x157f2d240>, <ibis.expr.operations.relations.Field object at 0x157f2f1c0>: <ibis.expr.operations.relations.Field object at 0x157f2f1c0>, <ibis.expr.operations.relations.Field object at 0x157f2d860>: <ibis.expr.operations.relations.Field object at 0x157f2f1c0>, <ibis.expr.operations.relations.Field object at 0x157f2de10>: <ibis.expr.operations.relations.Field object at 0x157f2de10>, <ibis.expr.operations.relations.Field object at 0x157f2e190>: <ibis.expr.operations.relations.Field object at 0x157f2de10>, <ibis.expr.operations.relations.Field object at 0x157f2e350>: <ibis.expr.operations.relations.Field object at 0x157f2e350>, <ibis.expr.operations.relations.Field object at 0x157f2cbb0>: <ibis.expr.operations.relations.Field object at 0x157f2e350>, <ibis.expr.operations.relations.Field object at 0x157f2ce50>: <ibis.expr.operations.relations.Field object at 0x157f2ce50>, <ibis.expr.operations.relations.Field object at 0x157f2c0c0>: <ibis.expr.operations.relations.Field object at 0x157f2ce50>, <ibis.expr.operations.relations.Field object at 0x157f2c2f0>: <ibis.expr.operations.relations.Field object at 0x157f2c2f0>, <ibis.expr.operations.relations.Field object at 0x157f2e270>: <ibis.expr.operations.relations.Field object at 0x157f2e270>, <ibis.expr.operations.relations.Field object at 0x157f2dbe0>: <ibis.expr.operations.relations.Field object at 0x157f2dbe0>, <ibis.expr.operations.relations.Field object at 0x157f2c4b0>: <ibis.expr.operations.relations.Field object at 0x157f2c4b0>, <ibis.expr.operations.relations.Field object at 0x157f2c360>: <ibis.expr.operations.relations.Field object at 0x157f2c360>, <ibis.expr.operations.relations.Field object at 0x157f2cb40>: <ibis.expr.operations.relations.Field object at 0x157f2e890>, <ibis.expr.operations.relations.Field object at 0x157f2c130>: <ibis.expr.operations.relations.Field object at 0x157f2ea50>, <ibis.expr.operations.relations.Field object at 0x157f2d160>: <ibis.expr.operations.generic.Literal object at 0x157eeaa50>, <ibis.expr.operations.relations.Field object at 0x157f2fa10>: <ibis.expr.operations.relations.Field object at 0x157f2e740>, <ibis.expr.operations.relations.Field object at 0x157f2f8c0>: <ibis.expr.operations.relations.Field object at 0x157f2c8a0>}, {}) has failed due to the following errors:
  `subs`: {<ibis.expr.operations.relations.Field object at 0x157f2d240>: <ibis.expr.operations.relations.Field object at 0x157f2d240>, <ibis.expr.operations.relations.Field object at 0x1586f81a0>: <ibis.expr.operations.relations.Field object at 0x157f2d240>, <ibis.expr.operations.relations.Field object at 0x157f2f1c0>: <ibis.expr.operations.relations.Field object at 0x157f2f1c0>, <ibis.expr.operations.relations.Field object at 0x157f2d860>: <ibis.expr.operations.relations.Field object at 0x157f2f1c0>, <ibis.expr.operations.relations.Field object at 0x157f2de10>: <ibis.expr.operations.relations.Field object at 0x157f2de10>, <ibis.expr.operations.relations.Field object at 0x157f2e190>: <ibis.expr.operations.relations.Field object at 0x157f2de10>, <ibis.expr.operations.relations.Field object at 0x157f2e350>: <ibis.expr.operations.relations.Field object at 0x157f2e350>, <ibis.expr.operations.relations.Field object at 0x157f2cbb0>: <ibis.expr.operations.relations.Field object at 0x157f2e350>, <ibis.expr.operations.relations.Field object at 0x157f2ce50>: <ibis.expr.operations.relations.Field object at 0x157f2ce50>, <ibis.expr.operations.relations.Field object at 0x157f2c0c0>: <ibis.expr.operations.relations.Field object at 0x157f2ce50>, <ibis.expr.operations.relations.Field object at 0x157f2c2f0>: <ibis.expr.operations.relations.Field object at 0x157f2c2f0>, <ibis.expr.operations.relations.Field object at 0x157f2e270>: <ibis.expr.operations.relations.Field object at 0x157f2e270>, <ibis.expr.operations.relations.Field object at 0x157f2dbe0>: <ibis.expr.operations.relations.Field object at 0x157f2dbe0>, <ibis.expr.operations.relations.Field object at 0x157f2c4b0>: <ibis.expr.operations.relations.Field object at 0x157f2c4b0>, <ibis.expr.operations.relations.Field object at 0x157f2c360>: <ibis.expr.operations.relations.Field object at 0x157f2c360>, <ibis.expr.operations.relations.Field object at 0x157f2cb40>: <ibis.expr.operations.relations.Field object at 0x157f2e890>, <ibis.expr.operations.relations.Field object at 0x157f2c130>: <ibis.expr.operations.relations.Field object at 0x157f2ea50>, <ibis.expr.operations.relations.Field object at 0x157f2d160>: <ibis.expr.operations.generic.Literal object at 0x157eeaa50>, <ibis.expr.operations.relations.Field object at 0x157f2fa10>: <ibis.expr.operations.relations.Field object at 0x157f2e740>, <ibis.expr.operations.relations.Field object at 0x157f2f8c0>: <ibis.expr.operations.relations.Field object at 0x157f2c8a0>} is not matching GenericMappingOf(key=CoercedTo(type=<class 'ibis.expr.operations.core.Value'>, func=<bound method Value.__coerce__ of <class 'ibis.expr.operations.core.Value'>>), value=CoercedTo(type=<class 'ibis.expr.operations.relations.Field'>, func=<bound method Value.__coerce__ of <class 'ibis.expr.operations.relations.Field'>>), type=CoercedTo(type=<class 'ibis.common.collections.FrozenDict'>, func=<class 'ibis.common.collections.FrozenDict'>))

Expected signature: DerefMap(rels: tuple[Relation, ...], subs: FrozenDict[Value, Field], ambigs: FrozenDict[Value, tuple[Value, ...]])"
```

#### Background

This arises in a prototype system that builds optimization models from algebraic equations.

`t1` represents a vector variable with the two dimensions `i` and `j`, lower and upper bounds as well as a variable identifier (the matrix column of the constraint matrix in the end). Let's call this variable `x` for the time being.

The join for `t2` is then equivalent to the quadratic expression that is built by the algreba: `x * x`. The expression is represented by a table with a `var1` and a `var2` column which hold the label identifiers and the `coeff`icient of the quadratic term.

The third join would then be the multiplication of a pandas series with only the index `i` (so it should broadcasting across the `j`'s of the expression in `t2`), which ultimately wants to scale the `coeff` values, but ibis seems to get confused when constructing this join (even though it should be relatively straightforward).

I am not sure how relevant that is to the bug report though.

### What version of ibis are you using?

9.1.0

### What backend(s) are you using, if any?

DuckDB

### Relevant log output

_No response_

### Code of Conduct

- [X] I agree to follow this project's Code of Conduct

Discussion:
github-actions[bot]: **ACTION NEEDED**

Ibis follows the [Conventional Commits specification](https://www.conventionalcommits.org/en/v1.0.0/) for release automation.

The PR title and description are used as the merge commit message.

Please update your PR title and description to match the specification.
cpcloud: @coroa I pushed up a small test.
coroa: > @coroa I pushed up a small test.

Thanks! But note that the `SignatureValidationError` was only triggered by adding another `JoinLink`.

Should i extend the test for that?
cpcloud: Ah, yes!
coroa: @cpcloud What do you think about that addition to the test?
cpcloud: LGTM, I have another case I want to add, pushing that here in a sec.
cpcloud: Thanks for the PR!
coroa: Doctest failure looks unrelated (table ordering instability).

Thanks for the quick turn-around, it's a delight.
cpcloud: Yep, failure is unrelated, and fixed in https://github.com/ibis-project/ibis/pull/9597.
coroa: FYI for debugging i found it very helpful to add some simple `__repr__` methods to
```python
class Field(Value):
    [...]
    def __repr__(self):
        return f"<Field name={self.name} rel={self.rel}>"
```
and
```python
class Relation(Node, Coercible):
    [...]
    def __repr__(self):
        return f"<{self.__class__.__name__} fields={','.join(self.schema)}>"
```

I saw that you have some formatting facilities with `pretty` and so on, but having a simple `Node` representation would make the error messages quite a lot clearer.


Patch:
diff --git a/ibis/expr/types/joins.py b/ibis/expr/types/joins.py
index 43c1a3e36..c81fbc6cf 100644
--- a/ibis/expr/types/joins.py
+++ b/ibis/expr/types/joins.py
@@ -179,7 +179,11 @@ def prepare_predicates(
         The comparison operation to construct if the input is a pair of
         expression-like objects
     """
-    reverse = {ops.Field(chain, k): v for k, v in chain.values.items()}
+    reverse = {
+        ops.Field(chain, k): v
+        for k, v in chain.values.items()
+        if isinstance(v, ops.Field)
+    }
     deref_right = DerefMap.from_targets(right)
     deref_left = DerefMap.from_targets(chain.tables, extra=reverse)
     deref_both = DerefMap.from_targets([*chain.tables, right], extra=reverse)


Test patch:
diff --git a/ibis/expr/tests/test_newrels.py b/ibis/expr/tests/test_newrels.py
index a9ba817d6..7fb6701c7 100644
--- a/ibis/expr/tests/test_newrels.py
+++ b/ibis/expr/tests/test_newrels.py
@@ -1764,3 +1764,68 @@ def test_projections_with_similar_expressions_have_different_names():
     assert b.op().name in fields
 
     assert expr.schema() == ibis.schema({a.op().name: "string", b.op().name: "string"})
+
+
+def test_expr_in_join_projection():
+    t1 = ibis.table({"a": "int64", "b": "string"}, name="t1")
+    t2 = ibis.table({"c": "int64", "b": "string"}, name="t2")
+    t3 = ibis.table({"a": "int64", "d": "int64", "e": "string"}, name="t3")
+    expr = t1.inner_join(t2, "b").select(
+        "a", lit1=1, lit2=2 * t1.a, lit3=_.c - 5, lit4=t2.b.length() / 2.0
+    )
+
+    op = expr.op()
+    assert isinstance(op, ops.JoinChain)
+    assert op.schema == ibis.schema(
+        {
+            "a": "int64",
+            "lit1": "int8",
+            "lit2": "int64",
+            "lit3": "int64",
+            "lit4": "float64",
+        }
+    )
+
+    # simple chain selection
+    expr2 = expr.inner_join(t3, "a").select("a", "d", "lit1", "lit2", "lit3", "lit4")
+    op2 = expr2.op()
+    assert isinstance(op2, ops.JoinChain)
+    assert op2.schema == ibis.schema(
+        {
+            "a": "int64",
+            "d": "int64",
+            "lit1": "int8",
+            "lit2": "int64",
+            "lit3": "int64",
+            "lit4": "float64",
+        }
+    )
+
+    # chain with expressions from all three tables in the join
+    expr = (
+        t1.inner_join(t2, "b")
+        .inner_join(t3, t2.b == t3.e)
+        .select(
+            "a",
+            lit1=1,
+            lit2=2 * t1.a,
+            lit3=_.c - 5,
+            lit4=t2.b.length() / 2.0,
+            lit5=_.e.cast("int") * 3,
+            lit6=t3.d + 1,
+        )
+    )
+
+    op = expr.op()
+    assert isinstance(op, ops.JoinChain)
+    assert op.schema == ibis.schema(
+        {
+            "a": "int64",
+            "lit1": "int8",
+            "lit2": "int64",
+            "lit3": "int64",
+            "lit4": "float64",
+            "lit5": "int64",
+            "lit6": "int64",
+        }
+    )