- The subquery wrapping which occurs when joined eager loading

is used with a one-to-many query that also features LIMIT,
OFFSET, or DISTINCT has been disabled in the case of a one-to-one
relationship, that is a one-to-many with
:paramref:`.relationship.uselist` set to False.  This will produce
more efficient queries in these cases.
fixes #3249

5 files changed, 104 insertions, 13 deletions

diff --git a/doc/build/changelog/changelog_10.rst b/doc/build/changelog/changelog_10.rst
index 474cec093..8f6fd3f37 100644
--- a/doc/build/changelog/changelog_10.rst
+++ b/doc/build/changelog/changelog_10.rst
@@ -24,6 +24,22 @@
     on compatibility concerns, see :doc:`/changelog/migration_10`.
 
     .. change::
+        :tags: feature, orm
+        :tickets: 3249
+
+        The subquery wrapping which occurs when joined eager loading
+        is used with a one-to-many query that also features LIMIT,
+        OFFSET, or DISTINCT has been disabled in the case of a one-to-one
+        relationship, that is a one-to-many with
+        :paramref:`.relationship.uselist` set to False.  This will produce
+        more efficient queries in these cases.
+
+        .. seealso::
+
+            :ref:`change_3249`
+
+
+    .. change::
         :tags: bug, orm
         :tickets: 3301
 
diff --git a/doc/build/changelog/migration_10.rst b/doc/build/changelog/migration_10.rst
index 80bff1bbf..66b385cc2 100644
--- a/doc/build/changelog/migration_10.rst
+++ b/doc/build/changelog/migration_10.rst
@@ -1203,6 +1203,55 @@ join into a subquery as a join target on SQLite.
 
 :ticket:`3008`
 
+.. _change_3429:
+
+Subqueries no longer applied to uselist=False joined eager loads
+----------------------------------------------------------------
+
+Given a joined eager load like the following::
+
+    class A(Base):
+        __tablename__ = 'a'
+        id = Column(Integer, primary_key=True)
+        b = relationship("B", uselist=False)
+
+
+    class B(Base):
+        __tablename__ = 'b'
+        id = Column(Integer, primary_key=True)
+        a_id = Column(ForeignKey('a.id'))
+
+    s = Session()
+    print(s.query(A).options(joinedload(A.b)).limit(5))
+
+SQLAlchemy considers the relationship ``A.b`` to be a "one to many,
+loaded as a single value", which is essentially a "one to one"
+relationship.  However, joined eager loading has always treated the
+above as a situation where the main query needs to be inside a
+subquery, as would normally be needed for a collection of B objects
+where the main query has a LIMIT applied::
+
+    SELECT anon_1.a_id AS anon_1_a_id, b_1.id AS b_1_id, b_1.a_id AS b_1_a_id
+    FROM (SELECT a.id AS a_id
+    FROM a LIMIT :param_1) AS anon_1
+    LEFT OUTER JOIN b AS b_1 ON anon_1.a_id = b_1.a_id
+
+However, since the relationship of the inner query to the outer one is
+that at most only one row is shared in the case of ``uselist=False``
+(in the same way as a many-to-one), the "subquery" used with LIMIT +
+joined eager loading is now dropped in this case::
+
+    SELECT a.id AS a_id, b_1.id AS b_1_id, b_1.a_id AS b_1_a_id
+    FROM a LEFT OUTER JOIN b AS b_1 ON a.id = b_1.a_id
+    LIMIT :param_1
+
+In the case that the LEFT OUTER JOIN returns more than one row, the ORM
+has always emitted a warning here and ignored addtional results for
+``uselist=False``, so the results in that error situation should not change.
+
+:ticket:`3249`
+
+
 query.update() with ``synchronize_session='evaluate'`` raises on multi-table update
 -----------------------------------------------------------------------------------
 
diff --git a/lib/sqlalchemy/orm/strategies.py b/lib/sqlalchemy/orm/strategies.py
index 25a27885b..611635333 100644
--- a/lib/sqlalchemy/orm/strategies.py
+++ b/lib/sqlalchemy/orm/strategies.py
@@ -1269,7 +1269,7 @@ class JoinedLoader(AbstractRelationshipLoader):
             anonymize_labels=True)
         assert clauses.aliased_class is not None
 
-        if self.parent_property.direction != interfaces.MANYTOONE:
+        if self.parent_property.uselist:
             context.multi_row_eager_loaders = True
 
         innerjoin = (
diff --git a/test/orm/inheritance/test_relationship.py b/test/orm/inheritance/test_relationship.py
index 3c671c9c1..b1d99415d 100644
--- a/test/orm/inheritance/test_relationship.py
+++ b/test/orm/inheritance/test_relationship.py
@@ -571,20 +571,20 @@ class SelfReferentialM2MTest(fixtures.MappedTest, AssertsCompiledSQL):
         # test that the splicing of the join works here, doesn't break in
         # the middle of "parent join child1"
         q = sess.query(Child1).options(joinedload('left_child2'))
-        self.assert_compile(q.limit(1).with_labels().statement,
-            "SELECT anon_1.child1_id AS anon_1_child1_id, anon_1.parent_id "
-            "AS anon_1_parent_id, anon_1.parent_cls AS anon_1_parent_cls, "
-            "child2_1.id AS child2_1_id, parent_1.id AS "
-            "parent_1_id, parent_1.cls AS parent_1_cls FROM "
-            "(SELECT child1.id AS child1_id, parent.id AS parent_id, "
-            "parent.cls AS parent_cls "
+        self.assert_compile(
+            q.limit(1).with_labels().statement,
+            "SELECT child1.id AS child1_id, parent.id AS parent_id, "
+            "parent.cls AS parent_cls, child2_1.id AS child2_1_id, "
+            "parent_1.id AS parent_1_id, parent_1.cls AS parent_1_cls "
             "FROM parent JOIN child1 ON parent.id = child1.id "
-            "LIMIT :param_1) AS anon_1 LEFT OUTER JOIN "
-            "(secondary AS secondary_1 JOIN "
+            "LEFT OUTER JOIN (secondary AS secondary_1 JOIN "
             "(parent AS parent_1 JOIN child2 AS child2_1 "
-            "ON parent_1.id = child2_1.id) ON parent_1.id = secondary_1.left_id) "
-            "ON anon_1.parent_id = secondary_1.right_id",
-            {'param_1':1})
+            "ON parent_1.id = child2_1.id) "
+            "ON parent_1.id = secondary_1.left_id) "
+            "ON parent.id = secondary_1.right_id "
+            "LIMIT :param_1",
+            checkparams={'param_1': 1}
+        )
 
         # another way to check
         assert q.limit(1).with_labels().subquery().count().scalar() == 1
diff --git a/test/orm/test_eager_relations.py b/test/orm/test_eager_relations.py
index ea8db8fda..3688773c2 100644
--- a/test/orm/test_eager_relations.py
+++ b/test/orm/test_eager_relations.py
@@ -1073,6 +1073,32 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL):
             eq_([User(id=7, address=Address(id=1))], l)
         self.assert_sql_count(testing.db, go, 1)
 
+    def test_one_to_many_scalar_subq_wrapping(self):
+        Address, addresses, users, User = (self.classes.Address,
+                                           self.tables.addresses,
+                                           self.tables.users,
+                                           self.classes.User)
+
+        mapper(User, users, properties=dict(
+            address=relationship(mapper(Address, addresses),
+                                 lazy='joined', uselist=False)
+        ))
+        q = create_session().query(User)
+        q = q.filter(users.c.id == 7).limit(1)
+
+        self.assert_compile(
+            q,
+            "SELECT users.id AS users_id, users.name AS users_name, "
+            "addresses_1.id AS addresses_1_id, "
+            "addresses_1.user_id AS addresses_1_user_id, "
+            "addresses_1.email_address AS addresses_1_email_address "
+            "FROM users LEFT OUTER JOIN addresses AS addresses_1 "
+            "ON users.id = addresses_1.user_id "
+            "WHERE users.id = :id_1 "
+            "LIMIT :param_1",
+            checkparams={'id_1': 7, 'param_1': 1}
+        )
+
     def test_many_to_one(self):
         users, Address, addresses, User = (
             self.tables.users,