dial back the default "flatness" a bit, it will be there for joinedload and query.join(), but if

you're dealing with aliased() or with_polymorphic() you need to say "flat=True".  Just the one
flag though, "flat" implies "aliased".

3 files changed, 154 insertions, 28 deletions

diff --git a/test/orm/inheritance/test_polymorphic_rel.py b/test/orm/inheritance/test_polymorphic_rel.py
index 4330dbf41..be3731fad 100644
--- a/test/orm/inheritance/test_polymorphic_rel.py
+++ b/test/orm/inheritance/test_polymorphic_rel.py
@@ -1286,12 +1286,66 @@ class PolymorphicPolymorphicTest(_PolymorphicTestBase, _PolymorphicPolymorphic):
     __dialect__ = 'default'
 
     def test_aliased_not_polluted_by_join(self):
+        # aliased(polymorphic) will normally do the old-school
+        # "(SELECT * FROM a JOIN b ...) AS anon_1" thing.
+        # this is the safest
         sess = create_session()
         palias = aliased(Person)
         self.assert_compile(
             sess.query(palias, Company.name)
                 .join(Person, Company.employees)
                 .filter(palias.name == 'dilbert'),
+            "SELECT anon_1.people_person_id AS anon_1_people_person_id, "
+            "anon_1.people_company_id AS anon_1_people_company_id, "
+            "anon_1.people_name AS anon_1_people_name, "
+            "anon_1.people_type AS anon_1_people_type, "
+            "anon_1.engineers_person_id AS anon_1_engineers_person_id, "
+            "anon_1.engineers_status AS anon_1_engineers_status, "
+            "anon_1.engineers_engineer_name AS anon_1_engineers_engineer_name, "
+            "anon_1.engineers_primary_language AS "
+                "anon_1_engineers_primary_language, "
+            "anon_1.managers_person_id AS anon_1_managers_person_id, "
+            "anon_1.managers_status AS anon_1_managers_status, "
+            "anon_1.managers_manager_name AS anon_1_managers_manager_name, "
+            "anon_1.boss_boss_id AS anon_1_boss_boss_id, "
+            "anon_1.boss_golf_swing AS anon_1_boss_golf_swing, "
+            "companies.name AS companies_name "
+            "FROM (SELECT people.person_id AS people_person_id, "
+                "people.company_id AS people_company_id, "
+                "people.name AS people_name, people.type AS people_type, "
+                "engineers.person_id AS engineers_person_id, "
+                "engineers.status AS engineers_status, "
+                "engineers.engineer_name AS engineers_engineer_name, "
+                "engineers.primary_language AS engineers_primary_language, "
+                "managers.person_id AS managers_person_id, "
+                "managers.status AS managers_status, "
+                "managers.manager_name AS managers_manager_name, "
+                "boss.boss_id AS boss_boss_id, "
+                "boss.golf_swing AS boss_golf_swing "
+                "FROM people LEFT OUTER JOIN engineers "
+                "ON people.person_id = engineers.person_id "
+                "LEFT OUTER JOIN managers "
+                "ON people.person_id = managers.person_id LEFT OUTER JOIN boss "
+                "ON managers.person_id = boss.boss_id) AS anon_1, "
+                "companies JOIN "
+                "(people LEFT OUTER JOIN engineers "
+                    "ON people.person_id = engineers.person_id "
+                    "LEFT OUTER JOIN managers "
+                    "ON people.person_id = managers.person_id "
+                    "LEFT OUTER JOIN boss ON managers.person_id = boss.boss_id) "
+                "ON companies.company_id = people.company_id "
+                "WHERE anon_1.people_name = :people_name_1 "
+                "ORDER BY anon_1.people_person_id"
+            )
+
+    def test_flat_aliased_w_select_from(self):
+        sess = create_session()
+        palias = aliased(Person, flat=True)
+        self.assert_compile(
+            sess.query(palias, Company.name)
+                .select_from(palias)
+                .join(Person, Company.employees)
+                .filter(palias.name == 'dilbert'),
             "SELECT people_1.person_id AS people_1_person_id, "
             "people_1.company_id AS people_1_company_id, "
             "people_1.name AS people_1_name, people_1.type AS people_1_type, "
@@ -1320,16 +1374,6 @@ class PolymorphicPolymorphicTest(_PolymorphicTestBase, _PolymorphicPolymorphic):
             "WHERE people_1.name = :name_1 ORDER BY people_1.person_id"
         )
 
-    def test_mixed_entities_compiled_four(self):
-        sess = create_session()
-        palias = aliased(Person)
-        self.assert_compile(
-            sess.query(palias, Company.name, Person)
-                .join(Company.employees)
-                .filter(Company.name == 'Elbonia, Inc.')
-                .filter(palias.name == 'dilbert'),
-            ""
-        )
 
 class PolymorphicUnionsTest(_PolymorphicTestBase, _PolymorphicUnions):
     pass
diff --git a/test/orm/inheritance/test_relationship.py b/test/orm/inheritance/test_relationship.py
index 3f1eb849f..f30a37941 100644
--- a/test/orm/inheritance/test_relationship.py
+++ b/test/orm/inheritance/test_relationship.py
@@ -1234,7 +1234,7 @@ class SubClassToSubClassMultiTest(AssertsCompiledSQL, fixtures.MappedTest):
     def test_two(self):
         Parent, Base1, Base2, Sub1, Sub2, EP1, EP2 = self._classes()
 
-        s2a = aliased(Sub2)
+        s2a = aliased(Sub2, flat=True)
 
         s = Session()
         self.assert_compile(
diff --git a/test/orm/test_of_type.py b/test/orm/test_of_type.py
index d002fd50f..67baddb52 100644
--- a/test/orm/test_of_type.py
+++ b/test/orm/test_of_type.py
@@ -2,9 +2,9 @@ from sqlalchemy.orm import Session, aliased, with_polymorphic, \
     contains_eager, joinedload, subqueryload, relationship,\
     subqueryload_all, joinedload_all
 from sqlalchemy import and_
-from sqlalchemy import testing
+from sqlalchemy import testing, exc as sa_exc
 from sqlalchemy.testing import fixtures
-from sqlalchemy.testing import assert_raises, eq_
+from sqlalchemy.testing import assert_raises, assert_raises_message, eq_
 from sqlalchemy.testing.schema import Column
 from sqlalchemy.engine import default
 from sqlalchemy.testing.entities import ComparableEntity
@@ -86,11 +86,12 @@ class _PolymorphicTestBase(object):
 
     def test_with_polymorphic_join_compile_one(self):
         sess = Session()
-# MARKMARK
+
         self.assert_compile(
             sess.query(Company).join(
                     Company.employees.of_type(
-                        with_polymorphic(Person, [Engineer, Manager], aliased=True)
+                        with_polymorphic(Person, [Engineer, Manager],
+                                    aliased=True, flat=True)
                     )
                 ),
             "SELECT companies.company_id AS companies_company_id, "
@@ -104,7 +105,8 @@ class _PolymorphicTestBase(object):
     def test_with_polymorphic_join_exec_contains_eager_one(self):
         sess = Session()
         def go():
-            wp = with_polymorphic(Person, [Engineer, Manager], aliased=True)
+            wp = with_polymorphic(Person, [Engineer, Manager],
+                                    aliased=True, flat=True)
             eq_(
                 sess.query(Company).join(
                     Company.employees.of_type(wp)
@@ -166,7 +168,7 @@ class _PolymorphicTestBase(object):
     def test_subqueryload_explicit_withpoly(self):
         sess = Session()
         def go():
-            target = with_polymorphic(Person, Engineer, aliased=True)
+            target = with_polymorphic(Person, Engineer)
             eq_(
                 sess.query(Company).\
                     filter_by(company_id=1).\
@@ -179,7 +181,7 @@ class _PolymorphicTestBase(object):
     def test_joinedload_explicit_withpoly(self):
         sess = Session()
         def go():
-            target = with_polymorphic(Person, Engineer, aliased=True)
+            target = with_polymorphic(Person, Engineer, flat=True)
             eq_(
                 sess.query(Company).\
                     filter_by(company_id=1).\
@@ -238,6 +240,44 @@ class PolymorphicJoinsTest(_PolymorphicTestBase, _PolymorphicJoins):
             comp_sel.process(sel, asfrom=True).replace("\n", "") + \
             " ON companies.company_id = people_1.company_id"
 
+    def test_joinedload_explicit_with_unaliased_poly_compile(self):
+        sess = Session()
+        target = with_polymorphic(Person, Engineer)
+        q = sess.query(Company).\
+            filter_by(company_id=1).\
+            options(joinedload(Company.employees.of_type(target)))
+        assert_raises_message(
+            sa_exc.InvalidRequestError,
+            "Detected unaliased columns when generating joined load.",
+            q._compile_context
+        )
+
+
+    def test_joinedload_explicit_with_flataliased_poly_compile(self):
+        sess = Session()
+        target = with_polymorphic(Person, Engineer, flat=True)
+        q = sess.query(Company).\
+            filter_by(company_id=1).\
+            options(joinedload(Company.employees.of_type(target)))
+        self.assert_compile(q,
+            "SELECT companies.company_id AS companies_company_id, "
+            "companies.name AS companies_name, "
+            "people_1.person_id AS people_1_person_id, "
+            "people_1.company_id AS people_1_company_id, "
+            "people_1.name AS people_1_name, people_1.type AS people_1_type, "
+            "engineers_1.person_id AS engineers_1_person_id, "
+            "engineers_1.status AS engineers_1_status, "
+            "engineers_1.engineer_name AS engineers_1_engineer_name, "
+            "engineers_1.primary_language AS engineers_1_primary_language "
+            "FROM companies LEFT OUTER JOIN (people AS people_1 "
+            "LEFT OUTER JOIN engineers AS engineers_1 "
+            "ON people_1.person_id = engineers_1.person_id "
+            "LEFT OUTER JOIN managers AS managers_1 "
+            "ON people_1.person_id = managers_1.person_id) "
+            "ON companies.company_id = people_1.company_id "
+            "WHERE companies.company_id = :company_id_1 "
+            "ORDER BY people_1.person_id"
+        )
 
 class SubclassRelationshipTest(testing.AssertsCompiledSQL, fixtures.DeclarativeMappedTest):
     """There's overlap here vs. the ones above."""
@@ -447,7 +487,7 @@ class SubclassRelationshipTest(testing.AssertsCompiledSQL, fixtures.DeclarativeM
             self.classes.Job,\
             self.classes.SubJob
 
-        Job_P = with_polymorphic(Job, SubJob, aliased=True)
+        Job_P = with_polymorphic(Job, SubJob, aliased=True, flat=True)
 
         s = Session()
         q = s.query(Job).join(DataContainer.jobs).\
@@ -501,7 +541,7 @@ class SubclassRelationshipTest(testing.AssertsCompiledSQL, fixtures.DeclarativeM
             self.classes.Job,\
             self.classes.SubJob
 
-        Job_P = with_polymorphic(Job, SubJob, aliased=True)
+        Job_P = with_polymorphic(Job, SubJob)
 
         s = Session()
         q = s.query(DataContainer).join(DataContainer.jobs.of_type(Job_P))
@@ -509,9 +549,9 @@ class SubclassRelationshipTest(testing.AssertsCompiledSQL, fixtures.DeclarativeM
             "SELECT data_container.id AS data_container_id, "
             "data_container.name AS data_container_name "
             "FROM data_container JOIN "
-            "(job AS job_1 LEFT OUTER JOIN subjob AS subjob_1 "
-                "ON job_1.id = subjob_1.id) "
-            "ON data_container.id = job_1.container_id")
+            "(job LEFT OUTER JOIN subjob "
+                "ON job.id = subjob.id) "
+            "ON data_container.id = job.container_id")
 
     def test_join_wsubclass(self):
         ParentThing, DataContainer, Job, SubJob = \
@@ -541,7 +581,7 @@ class SubclassRelationshipTest(testing.AssertsCompiledSQL, fixtures.DeclarativeM
             self.classes.Job,\
             self.classes.SubJob
 
-        Job_P = with_polymorphic(Job, SubJob, aliased=True, innerjoin=True)
+        Job_P = with_polymorphic(Job, SubJob, innerjoin=True)
 
         s = Session()
         q = s.query(DataContainer).join(DataContainer.jobs.of_type(Job_P))
@@ -549,8 +589,8 @@ class SubclassRelationshipTest(testing.AssertsCompiledSQL, fixtures.DeclarativeM
             "SELECT data_container.id AS data_container_id, "
             "data_container.name AS data_container_name "
             "FROM data_container JOIN "
-            "(job AS job_1 JOIN subjob AS subjob_1 ON job_1.id = subjob_1.id) "
-            "ON data_container.id = job_1.container_id")
+            "(job JOIN subjob ON job.id = subjob.id) "
+            "ON data_container.id = job.container_id")
 
     def test_join_walias(self):
         ParentThing, DataContainer, Job, SubJob = \
@@ -569,14 +609,34 @@ class SubclassRelationshipTest(testing.AssertsCompiledSQL, fixtures.DeclarativeM
             "FROM data_container JOIN job AS job_1 "
             "ON data_container.id = job_1.container_id")
 
-    def test_join_explicit_wpoly(self):
+    def test_join_explicit_wpoly_noalias(self):
         ParentThing, DataContainer, Job, SubJob = \
             self.classes.ParentThing,\
             self.classes.DataContainer,\
             self.classes.Job,\
             self.classes.SubJob
 
-        Job_P = with_polymorphic(Job, SubJob, aliased=True)
+        Job_P = with_polymorphic(Job, SubJob)
+
+        s = Session()
+        q = s.query(DataContainer).join(Job_P, DataContainer.jobs)
+        self.assert_compile(q,
+            "SELECT data_container.id AS data_container_id, "
+            "data_container.name AS data_container_name "
+            "FROM data_container JOIN "
+            "(job LEFT OUTER JOIN subjob "
+            "ON job.id = subjob.id) "
+            "ON data_container.id = job.container_id")
+
+
+    def test_join_explicit_wpoly_flat(self):
+        ParentThing, DataContainer, Job, SubJob = \
+            self.classes.ParentThing,\
+            self.classes.DataContainer,\
+            self.classes.Job,\
+            self.classes.SubJob
+
+        Job_P = with_polymorphic(Job, SubJob, flat=True)
 
         s = Session()
         q = s.query(DataContainer).join(Job_P, DataContainer.jobs)
@@ -588,3 +648,25 @@ class SubclassRelationshipTest(testing.AssertsCompiledSQL, fixtures.DeclarativeM
             "ON job_1.id = subjob_1.id) "
             "ON data_container.id = job_1.container_id")
 
+    def test_join_explicit_wpoly_full_alias(self):
+        ParentThing, DataContainer, Job, SubJob = \
+            self.classes.ParentThing,\
+            self.classes.DataContainer,\
+            self.classes.Job,\
+            self.classes.SubJob
+
+        Job_P = with_polymorphic(Job, SubJob, aliased=True)
+
+        s = Session()
+        q = s.query(DataContainer).join(Job_P, DataContainer.jobs)
+        self.assert_compile(q,
+            "SELECT data_container.id AS data_container_id, "
+            "data_container.name AS data_container_name "
+            "FROM data_container JOIN "
+            "(SELECT job.id AS job_id, job.type AS job_type, "
+                "job.container_id AS job_container_id, "
+                "subjob.id AS subjob_id, subjob.attr AS subjob_attr "
+                "FROM job LEFT OUTER JOIN subjob ON job.id = subjob.id) "
+                "AS anon_1 ON data_container.id = anon_1.job_container_id"
+        )
+