[Sqlalchemy-tickets] Issue #4366: Use of .has/.any with composite secondary join (zzzeek/sqlalchemy)

[Fred L. <iss...@bi...>]

New issue 4366: Use of .has/.any with composite secondary join
https://bitbucket.org/zzzeek/sqlalchemy/issues/4366/use-of-has-any-with-composite-secondary

Fred Ludlow:

Hi, 
Many times in the past I've started writing a sqlalchemy bug report and realised it was me! - this time I think it might just be a real bug...

I've attached a minimal example which hopefully explains it. python 2.7.15, sqla 1.2.13

I'm trying to create a relationship between a child (A) and it's great-grandparent (D), using a secondary join of the intermediate tables (B and C). 

This loads data correctly, but if I try and filter using .has (.any) on the relationship, it constructs an exists clause but doesn't apply the join condition within the secondary object (i.e. the B-C join).  As a result I think it ends up effectively doing "A inner join B full outer join C inner join D".

```python
from sqlalchemy import and_, 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 D(Base):
    __tablename__ = 'd'
    id = Column(Integer, primary_key=True)


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


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


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    b_id = Column(ForeignKey(B.id))
    
    d = relationship(
        'D',
        secondary="join(B, C)",
        primaryjoin="A.b_id == B.id",
        secondaryjoin="C.d_id == D.id",
        uselist=False)

    d_fixed = relationship(
        'D',
        secondary="join(B, C)",
        primaryjoin="A.b_id == B.id",
        secondaryjoin="and_(B.c_id == C.id, C.d_id == D.id)",
        uselist=False)



Base.metadata.create_all()

```
This prints:

Explicit join, correct behaviour:
```python
print session.query(A).join(A.d).filter(D.id==1)
```
```sql
SELECT a.id AS a_id, a.b_id AS a_b_id 
FROM a JOIN (b AS b_1 JOIN c AS c_1 ON c_1.id = b_1.c_id) ON a.b_id = b_1.id JOIN d ON c_1.d_id = d.id 
WHERE d.id = ?
```

Using has, missing b-c join condition
```python
print session.query(A).filter(A.d.has(D.id==1))
```
```sql
SELECT a.id AS a_id, a.b_id AS a_b_id 
FROM a 
WHERE EXISTS (SELECT 1 
FROM b, c, d 
WHERE a.b_id = b.id AND c.d_id = d.id AND d.id = ?)
```

Putting b-c join into secondaryjoin, fixes has/any query:
```python
print session.query(A).filter(A.d_fixed.has(D.id==1))
```
```sql
SELECT a.id AS a_id, a.b_id AS a_b_id 
FROM a 
WHERE EXISTS (SELECT 1 
FROM b, c, d 
WHERE a.b_id = b.id AND b.c_id = c.id AND c.d_id = d.id AND d.id = ?)
```

But the b-c join condition now appears twice when using query(A).join(A.d)
```python

```
print session.query(A).join(A.d_fixed).filter(D.id==1)
```sql
SELECT a.id AS a_id, a.b_id AS a_b_id 
FROM a JOIN (b AS b_1 JOIN c AS c_1 ON c_1.id = b_1.c_id) ON a.b_id = b_1.id JOIN d ON b_1.c_id = c_1.id AND c_1.d_id = d.id 
WHERE d.id = ?
```

I'm guessing this is related to #4349 and #4363