- 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

3 files changed, 144 insertions, 2 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index 69b5b29c1..60dea0564 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -22,6 +22,20 @@
     on compatibility concerns, see :doc:`/changelog/migration_10`.
 
     .. change::
+        :tags: bug, orm
+        :tickets: 3222
+
+        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.
+
+        This is sort of in-between feature and bug.
+
+    .. change::
         :tags: feature, sql
         :tickets: 3184
         :pullreq: bitbucket:30
diff --git a/lib/sqlalchemy/orm/util.py b/lib/sqlalchemy/orm/util.py
index 734f9d5e6..8d40ae21c 100644
--- a/lib/sqlalchemy/orm/util.py
+++ b/lib/sqlalchemy/orm/util.py
@@ -804,6 +804,16 @@ class _ORMJoin(expression.Join):
 
         expression.Join.__init__(self, left, right, onclause, isouter)
 
+        if not prop and getattr(right_info, 'mapper', None) \
+                and right_info.mapper.single:
+            # if single inheritance target and we are using a manual
+            # or implicit 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)
+            self.onclause = self.onclause & single_crit
+
     def join(self, right, onclause=None, isouter=False, join_to_left=None):
         return _ORMJoin(self, right, onclause, isouter)
 
diff --git a/test/orm/inheritance/test_single.py b/test/orm/inheritance/test_single.py
index be42cce52..6112929b6 100644
--- a/test/orm/inheritance/test_single.py
+++ b/test/orm/inheritance/test_single.py
@@ -386,7 +386,7 @@ class RelationshipToSingleTest(testing.AssertsCompiledSQL, fixtures.MappedTest):
             ]
         )
 
-    def test_outer_join(self):
+    def test_outer_join_prop(self):
         Company, Employee, Engineer = self.classes.Company,\
                                 self.classes.Employee,\
                                 self.classes.Engineer
@@ -407,7 +407,7 @@ class RelationshipToSingleTest(testing.AssertsCompiledSQL, fixtures.MappedTest):
             "= employees.company_id AND employees.type IN (:type_1)"
         )
 
-    def test_outer_join_alias(self):
+    def test_outer_join_prop_alias(self):
         Company, Employee, Engineer = self.classes.Company,\
                                 self.classes.Employee,\
                                 self.classes.Engineer
@@ -431,6 +431,124 @@ class RelationshipToSingleTest(testing.AssertsCompiledSQL, fixtures.MappedTest):
         )
 
 
+    def test_outer_join_literal_onclause(self):
+        Company, Employee, Engineer = self.classes.Company,\
+                                self.classes.Employee,\
+                                self.classes.Engineer
+        companies, employees = self.tables.companies, self.tables.employees
+
+        mapper(Company, companies, properties={
+            'engineers':relationship(Engineer)
+        })
+        mapper(Employee, employees, polymorphic_on=employees.c.type)
+        mapper(Engineer, inherits=Employee, polymorphic_identity='engineer')
+
+        sess = create_session()
+        self.assert_compile(
+            sess.query(Company, Engineer).outerjoin(
+                Engineer, Company.company_id == Engineer.company_id),
+            "SELECT companies.company_id AS companies_company_id, "
+            "companies.name AS companies_name, "
+            "employees.employee_id AS employees_employee_id, "
+            "employees.name AS employees_name, "
+            "employees.manager_data AS employees_manager_data, "
+            "employees.engineer_info AS employees_engineer_info, "
+            "employees.type AS employees_type, "
+            "employees.company_id AS employees_company_id FROM companies "
+            "LEFT OUTER JOIN employees ON "
+            "companies.company_id = employees.company_id "
+            "AND employees.type IN (:type_1)"
+        )
+
+    def test_outer_join_literal_onclause_alias(self):
+        Company, Employee, Engineer = self.classes.Company,\
+                                self.classes.Employee,\
+                                self.classes.Engineer
+        companies, employees = self.tables.companies, self.tables.employees
+
+        mapper(Company, companies, properties={
+            'engineers':relationship(Engineer)
+        })
+        mapper(Employee, employees, polymorphic_on=employees.c.type)
+        mapper(Engineer, inherits=Employee, polymorphic_identity='engineer')
+
+        eng_alias = aliased(Engineer)
+        sess = create_session()
+        self.assert_compile(
+            sess.query(Company, eng_alias).outerjoin(
+                eng_alias, Company.company_id == eng_alias.company_id),
+            "SELECT companies.company_id AS companies_company_id, "
+            "companies.name AS companies_name, "
+            "employees_1.employee_id AS employees_1_employee_id, "
+            "employees_1.name AS employees_1_name, "
+            "employees_1.manager_data AS employees_1_manager_data, "
+            "employees_1.engineer_info AS employees_1_engineer_info, "
+            "employees_1.type AS employees_1_type, "
+            "employees_1.company_id AS employees_1_company_id "
+            "FROM companies LEFT OUTER JOIN employees AS employees_1 ON "
+            "companies.company_id = employees_1.company_id "
+            "AND employees_1.type IN (:type_1)"
+        )
+
+    def test_outer_join_no_onclause(self):
+        Company, Employee, Engineer = self.classes.Company,\
+                                self.classes.Employee,\
+                                self.classes.Engineer
+        companies, employees = self.tables.companies, self.tables.employees
+
+        mapper(Company, companies, properties={
+            'engineers':relationship(Engineer)
+        })
+        mapper(Employee, employees, polymorphic_on=employees.c.type)
+        mapper(Engineer, inherits=Employee, polymorphic_identity='engineer')
+
+        sess = create_session()
+        self.assert_compile(
+            sess.query(Company, Engineer).outerjoin(
+                Engineer),
+            "SELECT companies.company_id AS companies_company_id, "
+            "companies.name AS companies_name, "
+            "employees.employee_id AS employees_employee_id, "
+            "employees.name AS employees_name, "
+            "employees.manager_data AS employees_manager_data, "
+            "employees.engineer_info AS employees_engineer_info, "
+            "employees.type AS employees_type, "
+            "employees.company_id AS employees_company_id "
+            "FROM companies LEFT OUTER JOIN employees ON "
+            "companies.company_id = employees.company_id "
+            "AND employees.type IN (:type_1)"
+        )
+
+    def test_outer_join_no_onclause_alias(self):
+        Company, Employee, Engineer = self.classes.Company,\
+                                self.classes.Employee,\
+                                self.classes.Engineer
+        companies, employees = self.tables.companies, self.tables.employees
+
+        mapper(Company, companies, properties={
+            'engineers':relationship(Engineer)
+        })
+        mapper(Employee, employees, polymorphic_on=employees.c.type)
+        mapper(Engineer, inherits=Employee, polymorphic_identity='engineer')
+
+        eng_alias = aliased(Engineer)
+        sess = create_session()
+        self.assert_compile(
+            sess.query(Company, eng_alias).outerjoin(
+                eng_alias),
+            "SELECT companies.company_id AS companies_company_id, "
+            "companies.name AS companies_name, "
+            "employees_1.employee_id AS employees_1_employee_id, "
+            "employees_1.name AS employees_1_name, "
+            "employees_1.manager_data AS employees_1_manager_data, "
+            "employees_1.engineer_info AS employees_1_engineer_info, "
+            "employees_1.type AS employees_1_type, "
+            "employees_1.company_id AS employees_1_company_id "
+            "FROM companies LEFT OUTER JOIN employees AS employees_1 ON "
+            "companies.company_id = employees_1.company_id "
+            "AND employees_1.type IN (:type_1)"
+        )
+
     def test_relationship_to_subclass(self):
         JuniorEngineer, Company, companies, Manager, \
                     Employee, employees, Engineer = (self.classes.JuniorEngineer,