[Sqlalchemy-tickets] Issue #3364: Non-simple relationship between two joined table subclasses is queried incorrectly (zzzeek/sqlalchemy)

[Scott T. <iss...@bi...>]

New issue 3364: Non-simple relationship between two joined table subclasses is queried incorrectly
https://bitbucket.org/zzzeek/sqlalchemy/issue/3364/non-simple-relationship-between-two-joined

Scott Torborg:

The test case below explains it better, but basically, creating a relationship between two joined table subclasses which includes a parent class column in the join conditions causes the relationship to be queried incorrectly.

Admittedly, this case *does* raise a warning, so it may be unintended usage:

```
/.../sqlalchemy/lib/sqlalchemy/orm/relationships.py:2368: SAWarning: Non-simple
column elements in primary join condition for property Cheese.published_pizzas
- consider using remote() annotations to mark the remote side.
```

However, it used to work, and I'm not sure this particular example can actually be fixed with annotations--so I think this may indicate a regression which could cause other problems as well.

Specifically, it works up until 0611baa889198421afa932f2af1524bd8826ed7d,
at which point the mapper initialization starts breaking (with ``TypeError:
'dict_values' object does not support indexing``). Then, 610e0594e249cd0bb28cb2bd4a7624f63f4510bb "fixes" that exception, after which the script below runs, but the assertion at the end fails.

Here's the test:

```python
from sqlalchemy import MetaData, Column, ForeignKey, types
from sqlalchemy.orm import create_session, relationship
from sqlalchemy.ext.declarative import declarative_base


metadata = MetaData('sqlite://')
metadata.bind.echo = True
Base = declarative_base(metadata=metadata)


class Food(Base):
    __tablename__ = 'foods'
    id = Column(types.Integer, primary_key=True)
    name = Column(types.Unicode(255), nullable=False)
    published = Column(types.Boolean, nullable=False, default=False)


class Pizza(Food):
    __tablename__ = 'pizzas'
    food_id = Column(None, ForeignKey('foods.id'), primary_key=True)
    cheese_id = Column(None, ForeignKey('cheeses.food_id'), nullable=False)

    cheese = relationship('Cheese',
                          primaryjoin='Pizza.cheese_id == Cheese.food_id',
                          backref='pizzas')


class Cheese(Food):
    __tablename__ = 'cheeses'
    food_id = Column(None, ForeignKey('foods.id'), primary_key=True)

    published_pizzas = relationship(
        'Pizza',
        primaryjoin=('and_(Pizza.cheese_id == Cheese.food_id,'
                     'Pizza.published == True)'))


metadata.create_all()
sess = create_session()

cheese = Cheese(name='Mozzarella', published=True)

pizza1 = Pizza(name='Plain', published=True, cheese=cheese)
pizza2 = Pizza(name='Pepperoni', published=False, cheese=cheese)
pizza3 = Pizza(name='Vegetarian', published=True, cheese=cheese)

sess.add_all([cheese, pizza1, pizza2, pizza3])
sess.flush()
sess.expunge_all()

mozzarella = sess.query(Cheese).first()

names = set(pizza.name for pizza in mozzarella.published_pizzas)
# Pepperoni isn't published, it shouldn't be returned
assert 'Pepperoni' not in names
```