[Sqlalchemy-tickets] Issue #4349: "Dynamic" relationship seems to ignore (at least) a composite secondary (zzzeek/sqlalchemy)

[Ilja E. <iss...@bi...>]

New issue 4349: "Dynamic" relationship seems to ignore (at least) a composite secondary
https://bitbucket.org/zzzeek/sqlalchemy/issues/4349/dynamic-relationship-seems-to-ignore-at

Ilja Everilä:

It would seem that a relationship configured with `lazy="dynamic"` ignores a [composite "secondary"](https://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#composite-secondary-joins), resulting in an incorrect query that performs a CROSS JOIN instead of the explicitly defined join. Given this artificial setup:


```
#!python

from sqlalchemy import create_engine, Integer, Column, ForeignKey
from sqlalchemy.orm import sessionmaker, relationship
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine("sqlite:///", echo=True)
Base = declarative_base()
Base.metadata.bind = engine
Session = sessionmaker()
session = Session()

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    ds_dyn = relationship(
        'D',
        secondary="join(B, C)",
        primaryjoin="A.id == B.a_id",
        secondaryjoin="C.d_id == D.id",
        lazy="dynamic")
    ds_sel = relationship(
        'D',
        secondary="join(B, C)",
        primaryjoin="A.id == B.a_id",
        secondaryjoin="C.d_id == D.id")
    ds_join = relationship(
        'D',
        secondary="join(B, C)",
        primaryjoin="A.id == B.a_id",
        secondaryjoin="C.d_id == D.id",
        lazy="joined")

class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    a_id = Column(ForeignKey(A.id))

class D(Base):
    __tablename__ = 'd'
    id = Column(Integer, primary_key=True)

class C(Base):
    __tablename__ = 'c'
    id = Column(Integer, primary_key=True)
    b_id = Column(ForeignKey(B.id))
    d_id = Column(ForeignKey(D.id))

Base.metadata.create_all()

```

and a dummy instance of `A`:


```
#!python

a = A()
session.add(a)
session.commit()

```

accessing the 3 relationships produce following queries:


```
#!python

In [2]: a.ds_sel
2018-10-17 12:34:20,820 INFO sqlalchemy.engine.base.Engine SELECT d.id AS d_id 
FROM d, b JOIN c ON b.id = c.b_id 
WHERE ? = b.a_id AND c.d_id = d.id
2018-10-17 12:34:20,820 INFO sqlalchemy.engine.base.Engine (1,)
Out[2]: []

```


```
#!python

In [3]: a.ds_join
2018-10-17 12:34:44,376 INFO sqlalchemy.engine.base.Engine SELECT d.id AS d_id 
FROM d, b JOIN c ON b.id = c.b_id 
WHERE ? = b.a_id AND c.d_id = d.id
2018-10-17 12:34:44,376 INFO sqlalchemy.engine.base.Engine (1,)
Out[3]: []

```


```
#!python

In [4]: a.ds_dyn.all()
2018-10-17 12:35:14,804 INFO sqlalchemy.engine.base.Engine SELECT d.id AS d_id 
FROM d, b, c 
WHERE ? = b.a_id AND c.d_id = d.id
2018-10-17 12:35:14,804 INFO sqlalchemy.engine.base.Engine (1,)
Out[4]: []

```

I've tried going through documentation [1](https://docs.sqlalchemy.org/en/latest/orm/relationship_api.html#sqlalchemy.orm.relationship.params.lazy), [2](https://docs.sqlalchemy.org/en/latest/orm/collections.html#dynamic-relationship) looking for if this is a known limitation or such, but without success.

On the other hand "dynamic" is omitted in the footnote about [composite "secondary"](https://docs.sqlalchemy.org/en/latest/orm/join_conditions.html#composite-secondary-joins):

> New in version 0.9.2: Support is improved for allowing a `join()` construct to be used directly as the target of the `secondary` argument, including support for joins, eager joins and lazy loading, as well as support within declarative to specify complex conditions such as joins involving class names as targets.

Does this mean that dynamic loading does not support this?