Fixes to the ``sqlalchemy.ext.serializer`` extension, including

that the "id" passed from the pickler is turned into a string
to prevent against bytes being parsed on Py3K, as well as that
``relationship()`` and ``orm.join()`` constructs are now properly
serialized. [ticket:2698] and some other observed issues.

4 files changed, 70 insertions, 32 deletions

diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst
index 9d392e778..621fd2a61 100644
--- a/doc/build/changelog/changelog_08.rst
+++ b/doc/build/changelog/changelog_08.rst
@@ -8,6 +8,16 @@
 
     .. change::
       :tags: bug, orm
+      :tickets: 2698
+
+      Fixes to the ``sqlalchemy.ext.serializer`` extension, including
+      that the "id" passed from the pickler is turned into a string
+      to prevent against bytes being parsed on Py3K, as well as that
+      ``relationship()`` and ``orm.join()`` constructs are now properly
+      serialized.
+
+    .. change::
+      :tags: bug, orm
       :tickets: 2714
 
       A significant improvement to the inner workings of query.join(),
diff --git a/lib/sqlalchemy/ext/serializer.py b/lib/sqlalchemy/ext/serializer.py
index 990483d03..b4c67538a 100644
--- a/lib/sqlalchemy/ext/serializer.py
+++ b/lib/sqlalchemy/ext/serializer.py
@@ -109,7 +109,8 @@ def Serializer(*args, **kw):
     pickler.persistent_id = persistent_id
     return pickler
 
-our_ids = re.compile(r'(mapper|table|column|session|attribute|engine):(.*)')
+our_ids = re.compile(
+            r'(mapperprop|mapper|table|column|session|attribute|engine):(.*)')
 
 
 def Deserializer(file, metadata=None, scoped_session=None, engine=None):
@@ -126,7 +127,7 @@ def Deserializer(file, metadata=None, scoped_session=None, engine=None):
             return None
 
     def persistent_load(id):
-        m = our_ids.match(id)
+        m = our_ids.match(str(id))
         if not m:
             return None
         else:
diff --git a/lib/sqlalchemy/orm/relationships.py b/lib/sqlalchemy/orm/relationships.py
index 9e44e01f7..95fa28613 100644
--- a/lib/sqlalchemy/orm/relationships.py
+++ b/lib/sqlalchemy/orm/relationships.py
@@ -835,12 +835,12 @@ class JoinCondition(object):
                     secondary_aliasizer.traverse(secondaryjoin)
             else:
                 primary_aliasizer = ClauseAdapter(dest_selectable,
-                        exclude_fn=lambda c: "local" in c._annotations,
+                        exclude_fn=_ColInAnnotations("local"),
                         equivalents=self.child_equivalents)
                 if source_selectable is not None:
                     primary_aliasizer.chain(
                         ClauseAdapter(source_selectable,
-                            exclude_fn=lambda c: "remote" in c._annotations,
+                            exclude_fn=_ColInAnnotations("remote"),
                             equivalents=self.parent_equivalents))
                 secondary_aliasizer = None
 
@@ -895,3 +895,14 @@ class JoinCondition(object):
         bind_to_col = dict((binds[col].key, col) for col in binds)
 
         return lazywhere, bind_to_col, equated_columns
+
+class _ColInAnnotations(object):
+    """Seralizable equivalent to:
+
+        lambda c: "name" in c._annotations
+    """
+    def __init__(self, name):
+        self.name = name
+
+    def __call__(self, c):
+        return self.name in c._annotations
\ No newline at end of file
diff --git a/test/ext/test_serializer.py b/test/ext/test_serializer.py
index 34d7d45e0..8d4394e2d 100644
--- a/test/ext/test_serializer.py
+++ b/test/ext/test_serializer.py
@@ -1,9 +1,7 @@
 
 from sqlalchemy.ext import serializer
-from sqlalchemy import exc
-import sqlalchemy as sa
 from sqlalchemy import testing
-from sqlalchemy import MetaData, Integer, String, ForeignKey, select, \
+from sqlalchemy import Integer, String, ForeignKey, select, \
     desc, func, util
 from sqlalchemy.testing.schema import Table
 from sqlalchemy.testing.schema import Column
@@ -19,6 +17,7 @@ class User(fixtures.ComparableEntity):
 class Address(fixtures.ComparableEntity):
     pass
 
+users = addresses = Session = None
 
 class SerializeTest(fixtures.MappedTest):
 
@@ -89,24 +88,21 @@ class SerializeTest(fixtures.MappedTest):
         eq_(re_expr.execute().fetchall(), [(7, u'jack'), (8, u'ed'),
             (8, u'ed'), (8, u'ed'), (9, u'fred')])
 
-    @testing.requires.python26  # namedtuple workaround not serializable in 2.5
-    @testing.skip_if(lambda: util.pypy, "pickle sometimes has "
-                        "problems here, sometimes not")
-    @testing.skip_if("postgresql", "Having intermittent problems on jenkins "
-                    "with this test, it's really not that important")
-    def test_query(self):
-        q = Session.query(User).filter(User.name == 'ed'
-                ).options(joinedload(User.addresses))
-        eq_(q.all(), [User(name='ed', addresses=[Address(id=2),
-            Address(id=3), Address(id=4)])])
-        q2 = serializer.loads(serializer.dumps(q, -1), users.metadata,
-                              Session)
+    def test_query_one(self):
+        q = Session.query(User).\
+                filter(User.name == 'ed').\
+                    options(joinedload(User.addresses))
 
+        q2 = serializer.loads(
+                    serializer.dumps(q, -1),
+                            users.metadata, Session)
         def go():
-            eq_(q2.all(), [User(name='ed', addresses=[Address(id=2),
-                Address(id=3), Address(id=4)])])
+            eq_(q2.all(), [
+                    User(name='ed', addresses=[Address(id=2),
+                    Address(id=3), Address(id=4)])])
 
         self.assert_sql_count(testing.db, go, 1)
+
         eq_(q2.join(User.addresses).filter(Address.email
             == 'ed@bettyboop.com').value(func.count('*')), 1)
         u1 = Session.query(User).get(8)
@@ -118,17 +114,37 @@ class SerializeTest(fixtures.MappedTest):
             Address(email='ed@lala.com'),
             Address(email='ed@bettyboop.com')])
 
-        # unfortunately pickle just doesn't have the horsepower
-        # to pickle annotated joins, both cpickle and pickle
-        # get confused likely since identity-unequal/hash equal
-        # objects with cycles being used
-        #q = \
-        #    Session.query(User).join(User.addresses).\
-        #       filter(Address.email.like('%fred%'))
-        #q2 = serializer.loads(serializer.dumps(q, -1), users.metadata,
-        #                      Session)
-        #eq_(q2.all(), [User(name='fred')])
-        #eq_(list(q2.values(User.id, User.name)), [(9, u'fred')])
+    def test_query_two(self):
+        q = \
+            Session.query(User).join(User.addresses).\
+               filter(Address.email.like('%fred%'))
+        q2 = serializer.loads(serializer.dumps(q, -1), users.metadata,
+                              Session)
+        eq_(q2.all(), [User(name='fred')])
+        eq_(list(q2.values(User.id, User.name)), [(9, u'fred')])
+
+    def test_query_three(self):
+        ua = aliased(User)
+        q = \
+            Session.query(ua).join(ua.addresses).\
+               filter(Address.email.like('%fred%'))
+        q2 = serializer.loads(serializer.dumps(q, -1), users.metadata,
+                              Session)
+        eq_(q2.all(), [User(name='fred')])
+
+        # try to pull out the aliased entity here...
+        ua_2 = q2._entities[0].entity_zero.entity
+        eq_(list(q2.values(ua_2.id, ua_2.name)), [(9, u'fred')])
+
+    def test_orm_join(self):
+        from sqlalchemy.orm.util import join
+
+        j = join(User, Address, User.addresses)
+
+        j2 = serializer.loads(serializer.dumps(j, -1), users.metadata)
+        assert j2.left is j.left
+        assert j2.right is j.right
+        assert j2._target_adapter._next
 
     @testing.requires.python26 # namedtuple workaround not serializable in 2.5
     @testing.exclude('sqlite', '<=', (3, 5, 9),