when Query does the "nested select" thing, it copies the ORDER BY to be placed on the "outer" select, so that eager loaders modify only the outer one and not the inner one

3 files changed, 20 insertions, 0 deletions

diff --git a/CHANGES b/CHANGES
index f08ae12c8..d5bdb82b3 100644
--- a/CHANGES
+++ b/CHANGES
@@ -9,6 +9,7 @@ the given object was formerly attached to was garbage collected;
 otherwise still requires you explicitly remove the instance from 
 the previous Session.
 - fixes to mapper compilation, checking for more error conditions
+- small fix to eager loading combined with ordering/limit/offset
 
 0.2.3
 - overhaul to mapper compilation to be deferred.  this allows mappers
diff --git a/lib/sqlalchemy/orm/query.py b/lib/sqlalchemy/orm/query.py
index f7d554900..9111b78ec 100644
--- a/lib/sqlalchemy/orm/query.py
+++ b/lib/sqlalchemy/orm/query.py
@@ -324,6 +324,10 @@ class Query(object):
             statement = sql.select([], sql.and_(*crit), from_obj=[self.table], use_labels=True)
  #           raise "OK statement", str(statement)
             if order_by:
+                # copy the order_by, since eager loaders will modify it, and we want the
+                # "inner" order_by to remain untouched
+                # see test/orm/mapper.py EagerTest.testmorelimit
+                order_by = [o.copy_container() for o in util.to_list(order_by)]
                 statement.order_by(*util.to_list(order_by))
         else:
             from_obj.append(self.table)
diff --git a/test/orm/mapper.py b/test/orm/mapper.py
index b73f20b15..13e79bc72 100644
--- a/test/orm/mapper.py
+++ b/test/orm/mapper.py
@@ -795,7 +795,22 @@ class EagerTest(MapperSuperTest):
         l = q.select((Item.c.item_name=='item 2') | (Item.c.item_name=='item 5') | (Item.c.item_name=='item 3'), order_by=[Item.c.item_id], limit=2)        
         self.assert_result(l, Item, *[item_keyword_result[1], item_keyword_result[2]])
         
+    def testmorelimit(self):
+        """tests that the ORDER BY doesnt get clobbered with a nested eager load, when the ORDER BY
+        is an expression.  requires the copying of the order by clause in query.compile()"""
+        ordermapper = mapper(Order, orders, properties = dict(
+                items = relation(mapper(Item, orderitems), lazy = False)
+            ))
+
+        m = mapper(User, users, properties = dict(
+            addresses = relation(mapper(Address, addresses), lazy = False),
+            orders = relation(ordermapper, primaryjoin = users.c.user_id==orders.c.user_id, lazy = False),
+        ))
+        sess = create_session()
+        q = sess.query(m)
         
+        l = q.select(q.join_to('orders'), order_by=desc(orders.c.user_id), limit=2, offset=1)
+        self.assert_result(l, User, *(user_all_result[2], user_all_result[0]))
         
     def testonetoone(self):
         m = mapper(User, users, properties = dict(