Plain Column on Single Table Subclass Pollutes `foreign_keys` when Multi Table Ancestor Present

Hi all. Thanks so much for SQLAlchemy, which is a joy to use and a solid foundation for our software.

Using declarative in a mixed single and multi table inheritance scenario can lead to the following error:

Traceback (most recent call last):
  File "sqla_declarative_mixed_multi_single_simpler.py", line 126, in <module>
    print session.query(BaseUser).count()
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1260, in query
    return self._query_cls(entities, self, **kwargs)
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 110, in __init__
    self._set_entities(entities)
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 120, in _set_entities
    self._set_entity_selectables(self._entities)
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 150, in _set_entity_selectables
    ent.setup_entity(*d[entity])
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3446, in setup_entity
    self._with_polymorphic = ext_info.with_polymorphic_mappers
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 754, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1891, in _with_polymorphic_mappers
    configure_mappers()
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 2768, in configure_mappers
    mapper._post_configure_properties()
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/mapper.py", line 1708, in _post_configure_properties
    prop.init()
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/interfaces.py", line 183, in init
    self.do_init()
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1629, in do_init
    self._setup_join_conditions()
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1704, in _setup_join_conditions
    can_be_synced_fn=self._columns_are_mapped
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 1972, in __init__
    self._determine_joins()
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/orm/relationships.py", line 2055, in _determine_joins
    consider_as_foreign_keys=consider_as_foreign_keys
  File "<string>", line 2, in join_condition
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 849, in _join_condition
    a, a_subset, b, consider_as_foreign_keys)
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 882, in _joincond_scan_left_right
    key=lambda fk: fk.parent._creation_order):
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 882, in <lambda>
    key=lambda fk: fk.parent._creation_order):
  File "/home/drocco/.envs/tmp-40e9352362c2c222/local/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 738, in __getattr__
    key)
AttributeError: Neither 'Column' object nor 'Comparator' object has an attribute 'parent'

The example hierarchy exhibiting this behavior consists of 4 model classes:

User extends BaseUser (multi table)
A User has a collection of one or more Thing objects
SubUser extends User (single table), adding a single column

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


engine = create_engine('sqlite:///:memory:', echo=True)
session = sessionmaker(bind=engine)()

Base = declarative_base()


class BaseUser(Base):
    __tablename__ = 'root'

    id = Column(Integer, primary_key=True)
    row_type = Column(String)

    __mapper_args__ = {
        'polymorphic_on': row_type,
        'polymorphic_identity': 'baseuser'
    }


class User(BaseUser):
    __tablename__ = 'user'

    __mapper_args__ = {
        'polymorphic_identity': 'user'
    }

    baseuser_id = Column(Integer, ForeignKey('root.id'), primary_key=True)


class Thing(Base):
    __tablename__ = 'thing'

    id = Column(Integer, primary_key=True)

    owner_id = Column(Integer, ForeignKey('user.baseuser_id'))
    owner = relationship('User', backref='things')


class SubUser(User):
    __mapper_args__ = {
        'polymorphic_identity': 'subuser'
    }

    sub_user_custom_thing = Column(Integer)


Base.metadata.create_all(engine)

print session.query(BaseUser).count()

From the traceback we see that the failure originates with this loop expression (selectable.py line 880):

for fk in sorted(
        b.foreign_keys,
        key=lambda fk: fk.parent._creation_order):

Digging further, b turns out to be the child_selectable used when setting up the join condition for Thing.owner (relationships.py line 1690); b.foreign_keys contains:

set([ForeignKey('root.id'), Column('sub_user_custom_thing', Integer(), table=<user>)])

So the plain integer column sub_user_custom_thing has somehow ended up in the User table's set of foreign keys, which leads to the symptom above.

There are several ways to work around this issue:

specify a primaryjoin on the relationship Thing.owner, which bypasses the automatic construction of the join condition
convert SubUser to multi table inheritance
redefine the Thing.owner relationship target to BaseUser
trigger class instrumentation after the definition of User and Thing but before the definition of SubUser. This allows the construction of the Thing.owner relationship to succeed by forcing it to happen before User's list of foreign keys gets corrupted.

Tested with SQLAlchemy 1.0.15, 1.1.0b3, and a master checkout on 2016-09-14.

#!

$ lsb_release -a ; echo ; python --version ; echo ; pip list
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 16.04.1 LTS
Release:        16.04
Codename:       xenial

Python 2.7.8

pip (8.1.2)
setuptools (27.2.0)
SQLAlchemy (1.0.15)
wheel (0.30.0a0)

Comments (7)