- changed milestone to 1.0.xx
- changed component to declarative
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
extendsBaseUser
(multi table)- A
User
has a collection of one or moreThing
objects SubUser
extendsUser
(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 relationshipThing.owner
, which bypasses the automatic construction of the join condition -
convert
SubUser
to multi table inheritance -
redefine the
Thing.owner
relationship target toBaseUser
-
trigger class instrumentation after the definition of
User
andThing
but before the definition ofSubUser
. This allows the construction of theThing.owner
relationship to succeed by forcing it to happen beforeUser
'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)
-
repo owner -
repo owner easy enough:
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index 11ef99f..43aff7c 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -905,7 +905,7 @@ class Join(FromClause): if col is not None: if self._cols_populated: self._columns[col._label] = col - self.foreign_keys.add(col) + self.foreign_keys.update(col.foreign_keys) if col.primary_key: self.primary_key.add(col) return col
-
repo owner -
reporter Fantastic! I'm glad that it was an easy fix. :)
SQLAlchemy is a really impressive achievement. Thank you for your quick response and helpfulness, and for all of the hard work you've put in over the years to make such an excellent and valuable project! :)
-
repo owner - changed status to resolved
Repair foreign_keys population for Join._refresh_for_new_column
Fixed bug where setting up a single-table inh subclass of a joined-table subclass which included an extra column would corrupt the foreign keys collection of the mapped table, thereby interfering with the initialization of relationships.
Change-Id: I04a0cf98fd456d12d5a5b9e77a46a01246969a63 Fixes:
#3797→ <<cset 25804aeae262>>
-
repo owner Repair foreign_keys population for Join._refresh_for_new_column
Fixed bug where setting up a single-table inh subclass of a joined-table subclass which included an extra column would corrupt the foreign keys collection of the mapped table, thereby interfering with the initialization of relationships.
Change-Id: I04a0cf98fd456d12d5a5b9e77a46a01246969a63 Fixes:
#3797(cherry picked from commit 8967099c3ba8b04fa20536bc0a026f6adc8e096f)→ <<cset bc4cf9480ecb>>
-
repo owner ok we're all set thanks for reporting! the next release should be the 1.1 release but it keeps getting delayed.
- Log in to comment
thanks for the great (perfect, actually) test case. The surprise in this test is the use of the "column on single-inheriting mapper" feature against a mapper that's already joined. The processing of this column first occurs at https://bitbucket.org/zzzeek/sqlalchemy/src/c3d582d87b5bd404d380c5707f35b7a20c9828fc/lib/sqlalchemy/ext/declarative/base.py?at=master&fileviewer=file-view-default#base.py-460 and the call that is blowing it up is the _refresh_for_new_column() call that is is not correctly interpreted by a Join object (likely this is only tested against a plain Table so far).