join to single-inh subclass without using a property-based join fails to apply single inh criterion

[Mike Bayer]

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

Base = declarative_base()


class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    type = Column(String)
    cid = Column(ForeignKey('c.id'))
    c = relationship("C", backref="a")
    __mapper_args__ = {'polymorphic_on': type}


class B(A):
    __mapper_args__ = {'polymorphic_identity': 'b'}


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

s = Session()

print s.query(C, B).filter(C.id == B.cid)

print s.query(C, B).join(B, C.id == B.cid)

print s.query(C, B).join(B, C.a)

first query is OK:

SELECT c.id AS c_id, a.id AS a_id, a.type AS a_type, a.cid AS a_cid 
FROM c, a 
WHERE c.id = a.cid AND a.type IN (:type_1)

second query lacks the discriminator:

SELECT c.id AS c_id, a.id AS a_id, a.type AS a_type, a.cid AS a_cid 
FROM c JOIN a ON c.id = a.cid

third one is OK:

SELECT c.id AS c_id, a.id AS a_id, a.type AS a_type, a.cid AS a_cid 
FROM c JOIN a ON c.id = a.cid AND a.type IN (:type_1)

the issue is the check inside of _join_entities assumes the ON clause was rendered orm.join() which also adds the criteria in. So this patch resolves:

diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 7b2ea79..0c3bc26 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -1979,7 +1979,7 @@ class Query(object):
             info.selectable, \
             getattr(info, 'is_aliased_class', False)

-        if right_mapper:
+        if right_mapper and prop is not None:
             self._join_entities += (info, )

         if right_mapper and prop and \

[Mike Bayer]

unfortunately this patch fails to do the right thing with an outer join, so we can't put this in as is and we might want to WONTFIX it:

   print s.query(C, B).outerjoin(B, C.id == B.cid)

result:

SELECT c.id AS c_id, a.id AS a_id, a.type AS a_type, a.cid AS a_cid 
FROM c LEFT OUTER JOIN a ON c.id = a.cid 
WHERE a.type IN (:type_1)

so unfortunately, if the user is hand-rolling the ON criterion, they have to also include the "single inheritance crit" - it has to be in the ON clause.

[Mike Bayer]

OK let's do this instead. Since we are OK adding WHERE to queries, we can augment the ON clause here, but this is now a 1.0 thing:

diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index 7b2ea79..8de4946 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -1980,6 +1980,11 @@ class Query(object):
             getattr(info, 'is_aliased_class', False)

         if right_mapper:
+            if not prop and right_mapper.single:
+                single_crit = right_mapper._single_table_criterion
+                if right_is_aliased:
+                    single_crit = info._adapter.traverse(single_crit)
+                onclause = sql.and_(onclause, single_crit)
             self._join_entities += (info, )

         if right_mapper and prop and \

[Mike Bayer]

Okey doke, more complicated, needs to not screw up when we do query(A).join(B), no ON clause at all. Here's that:

diff --git a/lib/sqlalchemy/orm/util.py b/lib/sqlalchemy/orm/util.py
index 734f9d5..916e032 100644
--- a/lib/sqlalchemy/orm/util.py
+++ b/lib/sqlalchemy/orm/util.py
@@ -775,6 +775,7 @@ class _ORMJoin(expression.Join):
         else:
             prop = None

+        single_crit = None
         if prop:
             if sql_util.clause_is_present(
                     on_selectable, left_info.selectable):
@@ -801,9 +802,19 @@ class _ORMJoin(expression.Join):
             else:
                 onclause = pj
             self._target_adapter = target_adapter
+        elif right_info and right_info.mapper.single:
+            # if single inheritance target and we are using a manual
+            # ON clause, augment it the same way we'd augment the
+            # WHERE.
+            single_crit = right_info.mapper._single_table_criterion
+            if right_info.is_aliased_class:
+                single_crit = right_info._adapter.traverse(single_crit)

         expression.Join.__init__(self, left, right, onclause, isouter)

+        if single_crit is not None:
+            self.onclause = self.onclause & single_crit
+
     def join(self, right, onclause=None, isouter=False, join_to_left=None):
         return _ORMJoin(self, right, onclause, isouter)

[Mike Bayer]

• The ON clause rendered when using :meth:.Query.join, :meth:.Query.outerjoin, or the standalone :func:.orm.join / :func:.orm.outerjoin functions to a single-inheritance subclass will now include the "single table criteria" in the ON clause even if the ON clause is otherwise hand-rolled; it is now added to the criteria using AND, the same way as if joining to a single-table target using relationship or similar. fixes #3222

→ <<cset dd6389f17173>>

[Mike Bayer]

• add a migration note for ref #3222

→ <<cset be8d361be286>>