[Sqlalchemy-tickets] Issue #3797: Plain Column on Single Table Subclass Pollutes `foreign_keys` when Multi Table Ancestor Present (zzzeek/sqlalchemy)

[Daniel R. <iss...@bi...>]

New issue 3797: Plain Column on Single Table Subclass Pollutes `foreign_keys` when Multi Table Ancestor Present
https://bitbucket.org/zzzeek/sqlalchemy/issues/3797/plain-column-on-single-table-subclass

Daniel Rocco:

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:


```
#!python

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


```
#!python

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)

```