break up test_query into three modules

1 files changed, 1593 insertions, 0 deletions

diff --git a/test/orm/test_froms.py b/test/orm/test_froms.py
new file mode 100644
index 000000000..6c86fa7ec
--- /dev/null
+++ b/test/orm/test_froms.py
@@ -0,0 +1,1593 @@
+from test.lib.testing import eq_, assert_raises, assert_raises_message
+import operator
+from sqlalchemy import *
+from sqlalchemy import exc as sa_exc, util
+from sqlalchemy.sql import compiler, table, column
+from sqlalchemy.engine import default
+from sqlalchemy.orm import *
+from sqlalchemy.orm import attributes
+
+from test.lib.testing import eq_
+
+import sqlalchemy as sa
+from test.lib import testing, AssertsCompiledSQL, Column, engines
+
+from test.orm import _fixtures
+from test.orm._fixtures import keywords, addresses, Base, \
+            Keyword, FixtureTest, \
+           Dingaling, item_keywords, dingalings, User, items,\
+           orders, Address, users, nodes, \
+            order_items, Item, Order, Node, \
+            composite_pk_table, CompositePk
+
+from test.orm import _base
+
+from sqlalchemy.orm.util import join, outerjoin, with_parent
+
+class QueryTest(_fixtures.FixtureTest):
+    run_setup_mappers = 'once'
+    run_inserts = 'once'
+    run_deletes = None
+
+
+    @classmethod
+    def setup_mappers(cls):
+        mapper(User, users, properties={
+            'addresses':relationship(Address, backref='user', order_by=addresses.c.id),
+            'orders':relationship(Order, backref='user', order_by=orders.c.id), # o2m, m2o
+        })
+        mapper(Address, addresses, properties={
+            'dingaling':relationship(Dingaling, uselist=False, backref="address")  #o2o
+        })
+        mapper(Dingaling, dingalings)
+        mapper(Order, orders, properties={
+            'items':relationship(Item, secondary=order_items, order_by=items.c.id),  #m2m
+            'address':relationship(Address),  # m2o
+        })
+        mapper(Item, items, properties={
+            'keywords':relationship(Keyword, secondary=item_keywords) #m2m
+        })
+        mapper(Keyword, keywords)
+
+        mapper(Node, nodes, properties={
+            'children':relationship(Node, 
+                backref=backref('parent', remote_side=[nodes.c.id])
+            )
+        })
+
+        mapper(CompositePk, composite_pk_table)
+
+        configure_mappers()
+
+
+class RawSelectTest(QueryTest, AssertsCompiledSQL):
+    """compare a bunch of select() tests with the equivalent Query using straight table/columns.
+    
+    Results should be the same as Query should act as a select() pass-thru for ClauseElement entities.
+    
+    """
+    def test_select(self):
+        sess = create_session()
+
+        self.assert_compile(sess.query(users).select_from(users.select()).with_labels().statement, 
+            "SELECT users.id AS users_id, users.name AS users_name FROM users, "
+            "(SELECT users.id AS id, users.name AS name FROM users) AS anon_1",
+            dialect=default.DefaultDialect()
+            )
+
+        self.assert_compile(sess.query(users, exists([1], from_obj=addresses)).with_labels().statement, 
+            "SELECT users.id AS users_id, users.name AS users_name, EXISTS "
+            "(SELECT 1 FROM addresses) AS anon_1 FROM users",
+            dialect=default.DefaultDialect()
+            )
+
+        # a little tedious here, adding labels to work around Query's auto-labelling.
+        # also correlate needed explicitly.  hmmm.....
+        # TODO: can we detect only one table in the "froms" and then turn off use_labels ?
+        s = sess.query(addresses.c.id.label('id'), addresses.c.email_address.label('email')).\
+            filter(addresses.c.user_id==users.c.id).correlate(users).statement.alias()
+            
+        self.assert_compile(sess.query(users, s.c.email).select_from(users.join(s, s.c.id==users.c.id)).with_labels().statement, 
+                "SELECT users.id AS users_id, users.name AS users_name, anon_1.email AS anon_1_email "
+                "FROM users JOIN (SELECT addresses.id AS id, addresses.email_address AS email FROM addresses "
+                "WHERE addresses.user_id = users.id) AS anon_1 ON anon_1.id = users.id",
+                dialect=default.DefaultDialect()
+            )
+
+        x = func.lala(users.c.id).label('foo')
+        self.assert_compile(sess.query(x).filter(x==5).statement, 
+            "SELECT lala(users.id) AS foo FROM users WHERE lala(users.id) = :param_1", dialect=default.DefaultDialect())
+
+        self.assert_compile(sess.query(func.sum(x).label('bar')).statement,  
+            "SELECT sum(lala(users.id)) AS bar FROM users", dialect=default.DefaultDialect()) 
+
+
+class FromSelfTest(QueryTest, AssertsCompiledSQL):
+    def test_filter(self):
+
+        assert [User(id=8), User(id=9)] == create_session().query(User).filter(User.id.in_([8,9])).from_self().all()
+
+        assert [User(id=8), User(id=9)] == create_session().query(User).order_by(User.id).slice(1,3).from_self().all()
+        assert [User(id=8)] == list(create_session().query(User).filter(User.id.in_([8,9])).from_self().order_by(User.id)[0:1])
+    
+    def test_join(self):
+        assert [
+            (User(id=8), Address(id=2)),
+            (User(id=8), Address(id=3)),
+            (User(id=8), Address(id=4)),
+            (User(id=9), Address(id=5))
+        ] == create_session().query(User).filter(User.id.in_([8,9])).from_self().\
+            join('addresses').add_entity(Address).order_by(User.id, Address.id).all()
+    
+    def test_group_by(self):
+        eq_(
+            create_session().query(Address.user_id, func.count(Address.id).label('count')).\
+                            group_by(Address.user_id).order_by(Address.user_id).all(),
+            [(7, 1), (8, 3), (9, 1)]
+        )
+
+        eq_(
+            create_session().query(Address.user_id, Address.id).\
+                            from_self(Address.user_id, func.count(Address.id)).\
+                            group_by(Address.user_id).order_by(Address.user_id).all(),
+            [(7, 1), (8, 3), (9, 1)]
+        )
+        
+    def test_no_joinedload(self):
+        """test that joinedloads are pushed outwards and not rendered in subqueries."""
+        
+        s = create_session()
+        
+        oracle_as = not testing.against('oracle') and "AS " or ""
+        
+        self.assert_compile(
+            s.query(User).options(joinedload(User.addresses)).from_self().statement,
+            "SELECT anon_1.users_id, anon_1.users_name, addresses_1.id, addresses_1.user_id, "\
+            "addresses_1.email_address FROM (SELECT users.id AS users_id, users.name AS users_name FROM users) %(oracle_as)sanon_1 "\
+            "LEFT OUTER JOIN addresses %(oracle_as)saddresses_1 ON anon_1.users_id = addresses_1.user_id ORDER BY addresses_1.id" % {
+                'oracle_as':oracle_as
+            }
+        )
+            
+    def test_aliases(self):
+        """test that aliased objects are accessible externally to a from_self() call."""
+        
+        s = create_session()
+        
+        ualias = aliased(User)
+        eq_(
+            s.query(User, ualias).filter(User.id > ualias.id).from_self(User.name, ualias.name).
+                    order_by(User.name, ualias.name).all(),
+            [
+                (u'chuck', u'ed'), 
+                (u'chuck', u'fred'), 
+                (u'chuck', u'jack'), 
+                (u'ed', u'jack'), 
+                (u'fred', u'ed'), 
+                (u'fred', u'jack')
+            ]
+        )
+
+        eq_(
+            s.query(User, ualias).filter(User.id > ualias.id).from_self(User.name, ualias.name).filter(ualias.name=='ed')\
+                .order_by(User.name, ualias.name).all(),
+            [(u'chuck', u'ed'), (u'fred', u'ed')]
+        )
+
+        eq_(
+            s.query(User, ualias).filter(User.id > ualias.id).from_self(ualias.name, Address.email_address).
+                    join(ualias.addresses).order_by(ualias.name, Address.email_address).all(),
+            [
+                (u'ed', u'fred@fred.com'), 
+                (u'jack', u'ed@bettyboop.com'), 
+                (u'jack', u'ed@lala.com'), 
+                (u'jack', u'ed@wood.com'), 
+                (u'jack', u'fred@fred.com')]
+        )
+        
+        
+    def test_multiple_entities(self):
+        sess = create_session()
+
+        eq_(
+            sess.query(User, Address).filter(User.id==Address.user_id).filter(Address.id.in_([2, 5])).from_self().all(),
+            [
+                (User(id=8), Address(id=2)),
+                (User(id=9), Address(id=5))
+            ]
+        )
+
+        eq_(
+            sess.query(User, Address).filter(User.id==Address.user_id).filter(Address.id.in_([2, 5])).from_self().options(joinedload('addresses')).first(),
+            
+            #    order_by(User.id, Address.id).first(),
+            (User(id=8, addresses=[Address(), Address(), Address()]), Address(id=2)),
+        )
+
+    def test_multiple_with_column_entities(self):
+        sess = create_session()
+        
+        eq_(
+            sess.query(User.id).from_self().\
+                add_column(func.count().label('foo')).\
+                group_by(User.id).\
+                order_by(User.id).\
+                from_self().all(),
+            [
+                (7,1), (8, 1), (9, 1), (10, 1)
+            ]
+            
+        )
+
+class AddEntityEquivalenceTest(_base.MappedTest, AssertsCompiledSQL):
+    run_setup_mappers = 'once'
+
+    @classmethod
+    def define_tables(cls, metadata):
+        Table('a', metadata,
+            Column('id', Integer, primary_key=True, test_needs_autoincrement=True),
+            Column('name', String(50)),
+            Column('type', String(20)),
+            Column('bid', Integer, ForeignKey('b.id'))
+        )
+
+        Table('b', metadata,
+            Column('id', Integer, primary_key=True, test_needs_autoincrement=True),
+            Column('name', String(50)),
+            Column('type', String(20))
+        )
+
+        Table('c', metadata,
+            Column('id', Integer, ForeignKey('b.id'), primary_key=True),
+            Column('age', Integer)
+        )
+
+        Table('d', metadata,
+            Column('id', Integer, ForeignKey('a.id'), primary_key=True),
+            Column('dede', Integer)
+        )
+
+    @classmethod
+    @testing.resolve_artifact_names
+    def setup_classes(cls):
+        class A(_fixtures.Base):
+            pass
+            
+        class B(_fixtures.Base):
+            pass
+        
+        class C(B):
+            pass
+        
+        class D(A):
+            pass
+            
+        mapper(A, a, 
+                    polymorphic_identity='a', 
+                    polymorphic_on=a.c.type,
+                    with_polymorphic= ('*', None),
+                    properties={
+                        'link':relation( B, uselist=False, backref='back')
+                    })
+        mapper(B, b, 
+                    polymorphic_identity='b', 
+                    polymorphic_on=b.c.type,
+                    with_polymorphic= ('*', None)
+                    )
+        mapper(C, c, inherits=B, polymorphic_identity='c')
+        mapper(D, d, inherits=A, polymorphic_identity='d')
+        
+    @classmethod
+    @testing.resolve_artifact_names
+    def insert_data(cls):
+        sess = create_session()
+        sess.add_all([
+            B(name='b1'), 
+            A(name='a1', link= C(name='c1',age=3)), 
+            C(name='c2',age=6), 
+            A(name='a2')
+            ])
+        sess.flush()
+    
+    @testing.resolve_artifact_names
+    def test_add_entity_equivalence(self):
+        sess = create_session()
+        
+        for q in [
+            sess.query( A,B).join( A.link),
+            sess.query( A).join( A.link).add_entity(B),
+        ]:
+            eq_(
+                q.all(),
+                [(
+                    A(bid=2, id=1, name=u'a1', type=u'a'), 
+                    C(age=3, id=2, name=u'c1', type=u'c')
+                )]
+            )
+
+        for q in [
+            sess.query( B,A).join( B.back),
+            sess.query( B).join( B.back).add_entity(A),
+            sess.query( B).add_entity(A).join( B.back)
+        ]:
+            eq_(
+                q.all(),
+                [(
+                    C(age=3, id=2, name=u'c1', type=u'c'), 
+                    A(bid=2, id=1, name=u'a1', type=u'a')
+                )]
+            )
+        
+        
+class InstancesTest(QueryTest, AssertsCompiledSQL):
+
+    def test_from_alias(self):
+
+        query = users.select(users.c.id==7).\
+                    union(users.select(users.c.id>7)).\
+                    alias('ulist').\
+                    outerjoin(addresses).\
+                    select(use_labels=True,
+                            order_by=['ulist.id', addresses.c.id])
+        sess =create_session()
+        q = sess.query(User)
+
+        def go():
+            l = list(q.options(contains_alias('ulist'), 
+                            contains_eager('addresses')).\
+                            instances(query.execute()))
+            assert self.static.user_address_result == l
+        self.assert_sql_count(testing.db, go, 1)
+
+        sess.expunge_all()
+
+        def go():
+            l = q.options(contains_alias('ulist'), 
+                            contains_eager('addresses')).\
+                                from_statement(query).all()
+            assert self.static.user_address_result == l
+        self.assert_sql_count(testing.db, go, 1)
+
+        # better way.  use select_from()
+        def go():
+            l = sess.query(User).select_from(query).\
+                        options(contains_eager('addresses')).all()
+            assert self.static.user_address_result == l
+        self.assert_sql_count(testing.db, go, 1)
+
+        # same thing, but alias addresses, so that the adapter 
+        # generated by select_from() is wrapped within
+        # the adapter created by contains_eager()
+        adalias = addresses.alias()
+        query = users.select(users.c.id==7).\
+                    union(users.select(users.c.id>7)).\
+                    alias('ulist').\
+                    outerjoin(adalias).\
+                    select(use_labels=True,
+                            order_by=['ulist.id', adalias.c.id])
+        def go():
+            l = sess.query(User).select_from(query).\
+                    options(contains_eager('addresses', alias=adalias)).all()
+            assert self.static.user_address_result == l
+        self.assert_sql_count(testing.db, go, 1)
+
+    def test_contains_eager(self):
+        sess = create_session()
+
+        # test that contains_eager suppresses the normal outer join rendering
+        q = sess.query(User).outerjoin(User.addresses).\
+                        options(contains_eager(User.addresses)).\
+                        order_by(User.id, addresses.c.id)
+        self.assert_compile(q.with_labels().statement,
+                            'SELECT addresses.id AS addresses_id, '
+                            'addresses.user_id AS addresses_user_id, '
+                            'addresses.email_address AS '
+                            'addresses_email_address, users.id AS '
+                            'users_id, users.name AS users_name FROM '
+                            'users LEFT OUTER JOIN addresses ON '
+                            'users.id = addresses.user_id ORDER BY '
+                            'users.id, addresses.id',
+                            dialect=default.DefaultDialect())
+                
+        def go():
+            assert self.static.user_address_result == q.all()
+        self.assert_sql_count(testing.db, go, 1)
+        sess.expunge_all()
+
+        adalias = addresses.alias()
+        q = sess.query(User).select_from(users.outerjoin(adalias)).options(contains_eager(User.addresses, alias=adalias)).order_by(User.id, adalias.c.id)
+        def go():
+            eq_(self.static.user_address_result, q.order_by(User.id).all())
+        self.assert_sql_count(testing.db, go, 1)
+        sess.expunge_all()
+
+        selectquery = users.outerjoin(addresses).select(users.c.id<10, use_labels=True, order_by=[users.c.id, addresses.c.id])
+        q = sess.query(User)
+
+        def go():
+            l = list(q.options(contains_eager('addresses')).instances(selectquery.execute()))
+            assert self.static.user_address_result[0:3] == l
+        self.assert_sql_count(testing.db, go, 1)
+
+        sess.expunge_all()
+
+        def go():
+            l = list(q.options(contains_eager(User.addresses)).instances(selectquery.execute()))
+            assert self.static.user_address_result[0:3] == l
+        self.assert_sql_count(testing.db, go, 1)
+        sess.expunge_all()
+
+        def go():
+            l = q.options(contains_eager('addresses')).from_statement(selectquery).all()
+            assert self.static.user_address_result[0:3] == l
+        self.assert_sql_count(testing.db, go, 1)
+
+    def test_contains_eager_alias(self):
+        adalias = addresses.alias('adalias')
+        selectquery = users.outerjoin(adalias).select(use_labels=True, order_by=[users.c.id, adalias.c.id])
+        sess = create_session()
+        q = sess.query(User)
+
+        # string alias name
+        def go():
+            l = list(q.options(contains_eager('addresses', alias="adalias")).instances(selectquery.execute()))
+            assert self.static.user_address_result == l
+        self.assert_sql_count(testing.db, go, 1)
+        sess.expunge_all()
+
+        # expression.Alias object
+        def go():
+            l = list(q.options(contains_eager('addresses', alias=adalias)).instances(selectquery.execute()))
+            assert self.static.user_address_result == l
+        self.assert_sql_count(testing.db, go, 1)
+
+        sess.expunge_all()
+
+        # Aliased object
+        adalias = aliased(Address)
+        def go():
+            l = q.options(contains_eager('addresses', alias=adalias)).outerjoin(adalias, User.addresses).order_by(User.id, adalias.id)
+            assert self.static.user_address_result == l.all()
+        self.assert_sql_count(testing.db, go, 1)
+        sess.expunge_all()
+
+        oalias = orders.alias('o1')
+        ialias = items.alias('i1')
+        query = users.outerjoin(oalias).outerjoin(order_items).outerjoin(ialias).select(use_labels=True).order_by(users.c.id, oalias.c.id, ialias.c.id)
+        q = create_session().query(User)
+        # test using string alias with more than one level deep
+        def go():
+            l = list(q.options(contains_eager('orders', alias='o1'), contains_eager('orders.items', alias='i1')).instances(query.execute()))
+            assert self.static.user_order_result == l
+        self.assert_sql_count(testing.db, go, 1)
+
+        sess.expunge_all()
+
+        # test using Alias with more than one level deep
+        def go():
+            l = list(q.options(contains_eager('orders', alias=oalias), contains_eager('orders.items', alias=ialias)).instances(query.execute()))
+            assert self.static.user_order_result == l
+        self.assert_sql_count(testing.db, go, 1)
+        sess.expunge_all()
+
+        # test using Aliased with more than one level deep
+        oalias = aliased(Order)
+        ialias = aliased(Item)
+        def go():
+            l = q.options(contains_eager(User.orders, alias=oalias), 
+                            contains_eager(User.orders, Order.items, alias=ialias)).\
+                outerjoin(oalias, User.orders).\
+                outerjoin(ialias, oalias.items).order_by(User.id, oalias.id, ialias.id)
+            assert self.static.user_order_result == l.all()
+        self.assert_sql_count(testing.db, go, 1)
+        sess.expunge_all()
+
+    def test_mixed_eager_contains_with_limit(self):
+        sess = create_session()
+    
+        q = sess.query(User)
+        def go():
+            # outerjoin to User.orders, offset 1/limit 2 so we get user
+            # 7 + second two orders. then joinedload the addresses.
+            # User + Order columns go into the subquery, address left
+            # outer joins to the subquery, joinedloader for User.orders
+            # applies context.adapter to result rows.  This was
+            # [ticket:1180].
+
+            l = \
+                q.outerjoin(User.orders).options(joinedload(User.addresses),
+                    contains_eager(User.orders)).order_by(User.id,
+                    Order.id).offset(1).limit(2).all()
+            eq_(l, [User(id=7,
+                addresses=[Address(email_address=u'jack@bean.com',
+                user_id=7, id=1)], name=u'jack',
+                orders=[Order(address_id=1, user_id=7,
+                description=u'order 3', isopen=1, id=3),
+                Order(address_id=None, user_id=7, description=u'order 5'
+                , isopen=0, id=5)])])
+
+        self.assert_sql_count(testing.db, go, 1)
+        sess.expunge_all()
+
+        def go():
+
+            # same as above, except Order is aliased, so two adapters
+            # are applied by the eager loader
+
+            oalias = aliased(Order)
+            l = q.outerjoin(oalias, User.orders).\
+                            options(joinedload(User.addresses),
+                                contains_eager(User.orders, alias=oalias)).\
+                                order_by(User.id, oalias.id).\
+                                offset(1).limit(2).all()
+            eq_(l, [User(id=7,
+                addresses=[Address(email_address=u'jack@bean.com',
+                user_id=7, id=1)], name=u'jack',
+                orders=[Order(address_id=1, user_id=7,
+                description=u'order 3', isopen=1, id=3),
+                Order(address_id=None, user_id=7, description=u'order 5'
+                , isopen=0, id=5)])])
+
+        self.assert_sql_count(testing.db, go, 1)
+    
+    
+class MixedEntitiesTest(QueryTest, AssertsCompiledSQL):
+
+    def test_values(self):
+        sess = create_session()
+
+        assert list(sess.query(User).values()) == list()
+
+        sel = users.select(User.id.in_([7, 8])).alias()
+        q = sess.query(User)
+        q2 = q.select_from(sel).values(User.name)
+        eq_(list(q2), [(u'jack',), (u'ed',)])
+    
+        q = sess.query(User)
+        q2 = q.order_by(User.id).\
+                values(User.name, User.name + " " + cast(User.id, String(50)))
+        eq_(
+            list(q2), 
+            [(u'jack', u'jack 7'), (u'ed', u'ed 8'), 
+            (u'fred', u'fred 9'), (u'chuck', u'chuck 10')]
+        )
+    
+        q2 = q.join('addresses').\
+                filter(User.name.like('%e%')).\
+                order_by(User.id, Address.id).\
+                values(User.name, Address.email_address)
+        eq_(list(q2), 
+                [(u'ed', u'ed@wood.com'), (u'ed', u'ed@bettyboop.com'), 
+                (u'ed', u'ed@lala.com'), (u'fred', u'fred@fred.com')])
+    
+        q2 = q.join('addresses').\
+                filter(User.name.like('%e%')).\
+                order_by(desc(Address.email_address)).\
+                slice(1, 3).values(User.name, Address.email_address)
+        eq_(list(q2), [(u'ed', u'ed@wood.com'), (u'ed', u'ed@lala.com')])
+    
+        adalias = aliased(Address)
+        q2 = q.join(adalias, 'addresses').\
+                filter(User.name.like('%e%')).\
+                values(User.name, adalias.email_address)
+        eq_(list(q2), [(u'ed', u'ed@wood.com'), (u'ed', u'ed@bettyboop.com'), 
+                        (u'ed', u'ed@lala.com'), (u'fred', u'fred@fred.com')])
+    
+        q2 = q.values(func.count(User.name))
+        assert q2.next() == (4,)
+
+        q2 = q.select_from(sel).filter(User.id==8).values(User.name, sel.c.name, User.name)
+        eq_(list(q2), [(u'ed', u'ed', u'ed')])
+
+        # using User.xxx is alised against "sel", so this query returns nothing
+        q2 = q.select_from(sel).\
+                filter(User.id==8).\
+                filter(User.id>sel.c.id).values(User.name, sel.c.name, User.name)
+        eq_(list(q2), [])
+
+        # whereas this uses users.c.xxx, is not aliased and creates a new join
+        q2 = q.select_from(sel).\
+                filter(users.c.id==8).\
+                filter(users.c.id>sel.c.id).values(users.c.name, sel.c.name, User.name)
+        eq_(list(q2), [(u'ed', u'jack', u'jack')])
+
+    @testing.fails_on('mssql', 'FIXME: unknown')
+    def test_values_specific_order_by(self):
+        sess = create_session()
+
+        assert list(sess.query(User).values()) == list()
+
+        sel = users.select(User.id.in_([7, 8])).alias()
+        q = sess.query(User)
+        u2 = aliased(User)
+        q2 = q.select_from(sel).\
+                    filter(u2.id>1).\
+                    order_by(User.id, sel.c.id, u2.id).\
+                    values(User.name, sel.c.name, u2.name)
+        eq_(list(q2), [(u'jack', u'jack', u'jack'), (u'jack', u'jack', u'ed'), 
+                        (u'jack', u'jack', u'fred'), (u'jack', u'jack', u'chuck'), 
+                        (u'ed', u'ed', u'jack'), (u'ed', u'ed', u'ed'), 
+                        (u'ed', u'ed', u'fred'), (u'ed', u'ed', u'chuck')])
+
+    @testing.fails_on('mssql', 'FIXME: unknown')
+    @testing.fails_on('oracle',
+                      "Oracle doesn't support boolean expressions as "
+                      "columns")
+    @testing.fails_on('postgresql+pg8000',
+                      "pg8000 parses the SQL itself before passing on "
+                      "to PG, doesn't parse this")
+    @testing.fails_on('postgresql+zxjdbc',
+                      "zxjdbc parses the SQL itself before passing on "
+                      "to PG, doesn't parse this")
+    def test_values_with_boolean_selects(self):
+        """Tests a values clause that works with select boolean
+        evaluations"""
+        sess = create_session()
+
+        q = sess.query(User)
+        q2 = q.group_by(User.name.like('%j%')).\
+                    order_by(desc(User.name.like('%j%'))).\
+                    values(User.name.like('%j%'), func.count(User.name.like('%j%')))
+        eq_(list(q2), [(True, 1), (False, 3)])
+
+        q2 = q.order_by(desc(User.name.like('%j%'))).values(User.name.like('%j%'))
+        eq_(list(q2), [(True,), (False,), (False,), (False,)])
+
+
+    def test_correlated_subquery(self):
+        """test that a subquery constructed from ORM attributes doesn't leak out 
+        those entities to the outermost query.
+    
+        """
+        sess = create_session()
+    
+        subq = select([func.count()]).\
+            where(User.id==Address.user_id).\
+            correlate(users).\
+            label('count')
+
+        # we don't want Address to be outside of the subquery here
+        eq_(
+            list(sess.query(User, subq)[0:3]),
+            [(User(id=7,name=u'jack'), 1), (User(id=8,name=u'ed'), 3), 
+            (User(id=9,name=u'fred'), 1)]
+            )
+
+        # same thing without the correlate, as it should
+        # not be needed
+        subq = select([func.count()]).\
+            where(User.id==Address.user_id).\
+            label('count')
+
+        # we don't want Address to be outside of the subquery here
+        eq_(
+            list(sess.query(User, subq)[0:3]),
+            [(User(id=7,name=u'jack'), 1), (User(id=8,name=u'ed'), 3), 
+            (User(id=9,name=u'fred'), 1)]
+            )
+
+    def test_tuple_labeling(self):
+        sess = create_session()
+        
+        # test pickle + all the protocols !
+        for pickled in False, -1, 0, 1, 2:
+            for row in sess.query(User, Address).join(User.addresses).all():
+                if pickled is not False:
+                    row = util.pickle.loads(util.pickle.dumps(row, pickled))
+                    
+                eq_(row.keys(), ['User', 'Address'])
+                eq_(row.User, row[0])
+                eq_(row.Address, row[1])
+        
+            for row in sess.query(User.name, User.id.label('foobar')):
+                if pickled is not False:
+                    row = util.pickle.loads(util.pickle.dumps(row, pickled))
+                eq_(row.keys(), ['name', 'foobar'])
+                eq_(row.name, row[0])
+                eq_(row.foobar, row[1])
+
+            for row in sess.query(User).values(User.name, User.id.label('foobar')):
+                if pickled is not False:
+                    row = util.pickle.loads(util.pickle.dumps(row, pickled))
+                eq_(row.keys(), ['name', 'foobar'])
+                eq_(row.name, row[0])
+                eq_(row.foobar, row[1])
+
+            oalias = aliased(Order)
+            for row in sess.query(User, oalias).join(User.orders).all():
+                if pickled is not False:
+                    row = util.pickle.loads(util.pickle.dumps(row, pickled))
+                eq_(row.keys(), ['User'])
+                eq_(row.User, row[0])
+
+            oalias = aliased(Order, name='orders')
+            for row in sess.query(User, oalias).join(User.orders).all():
+                if pickled is not False:
+                    row = util.pickle.loads(util.pickle.dumps(row, pickled))
+                eq_(row.keys(), ['User', 'orders'])
+                eq_(row.User, row[0])
+                eq_(row.orders, row[1])
+            
+            # test here that first col is not labeled, only
+            # one name in keys, matches correctly
+            for row in sess.query(User.name + 'hoho', User.name):
+                eq_(row.keys(), ['name'])
+                eq_(row[0], row.name + 'hoho')
+            
+            if pickled is not False:
+                ret = sess.query(User, Address).join(User.addresses).all()
+                util.pickle.loads(util.pickle.dumps(ret, pickled))
+                
+    def test_column_queries(self):
+        sess = create_session()
+
+        eq_(sess.query(User.name).all(), [(u'jack',), (u'ed',), (u'fred',), (u'chuck',)])
+    
+        sel = users.select(User.id.in_([7, 8])).alias()
+        q = sess.query(User.name)
+        q2 = q.select_from(sel).all()
+        eq_(list(q2), [(u'jack',), (u'ed',)])
+
+        eq_(sess.query(User.name, Address.email_address).filter(User.id==Address.user_id).all(), [
+            (u'jack', u'jack@bean.com'), (u'ed', u'ed@wood.com'), 
+            (u'ed', u'ed@bettyboop.com'), (u'ed', u'ed@lala.com'), 
+            (u'fred', u'fred@fred.com')
+        ])
+    
+        eq_(sess.query(User.name, func.count(Address.email_address)).\
+                    outerjoin(User.addresses).group_by(User.id, User.name).\
+                    order_by(User.id).all(), 
+            [(u'jack', 1), (u'ed', 3), (u'fred', 1), (u'chuck', 0)]
+        )
+
+        eq_(sess.query(User, func.count(Address.email_address)).\
+                    outerjoin(User.addresses).group_by(User).\
+                    order_by(User.id).all(), 
+            [(User(name='jack',id=7), 1), (User(name='ed',id=8), 3), 
+            (User(name='fred',id=9), 1), (User(name='chuck',id=10), 0)]
+        )
+
+        eq_(sess.query(func.count(Address.email_address), User).\
+                outerjoin(User.addresses).group_by(User).\
+                order_by(User.id).all(), 
+            [(1, User(name='jack',id=7)), (3, User(name='ed',id=8)), 
+            (1, User(name='fred',id=9)), (0, User(name='chuck',id=10))]
+        )
+    
+        adalias = aliased(Address)
+        eq_(sess.query(User, func.count(adalias.email_address)).\
+                outerjoin(adalias, 'addresses').group_by(User).\
+                order_by(User.id).all(), 
+            [(User(name='jack',id=7), 1), (User(name='ed',id=8), 3), 
+            (User(name='fred',id=9), 1), (User(name='chuck',id=10), 0)]
+        )
+
+        eq_(sess.query(func.count(adalias.email_address), User).\
+                outerjoin(adalias, User.addresses).group_by(User).\
+                order_by(User.id).all(),
+            [(1, User(name=u'jack',id=7)), (3, User(name=u'ed',id=8)), 
+                (1, User(name=u'fred',id=9)), (0, User(name=u'chuck',id=10))]
+        )
+
+        # select from aliasing + explicit aliasing
+        eq_(
+            sess.query(User, adalias.email_address, adalias.id).\
+                    outerjoin(adalias, User.addresses).\
+                    from_self(User, adalias.email_address).\
+                    order_by(User.id, adalias.id).all(),
+            [
+                (User(name=u'jack',id=7), u'jack@bean.com'), 
+                (User(name=u'ed',id=8), u'ed@wood.com'), 
+                (User(name=u'ed',id=8), u'ed@bettyboop.com'),
+                (User(name=u'ed',id=8), u'ed@lala.com'), 
+                (User(name=u'fred',id=9), u'fred@fred.com'), 
+                (User(name=u'chuck',id=10), None)
+            ]
+        )
+    
+        # anon + select from aliasing
+        eq_(
+            sess.query(User).join(User.addresses, aliased=True).\
+                    filter(Address.email_address.like('%ed%')).\
+                    from_self().all(),
+            [
+                User(name=u'ed',id=8), 
+                User(name=u'fred',id=9), 
+            ]
+        )
+
+        # test eager aliasing, with/without select_from aliasing
+        for q in [
+            sess.query(User, adalias.email_address).\
+                    outerjoin(adalias, User.addresses).\
+                    options(joinedload(User.addresses)).\
+                    order_by(User.id, adalias.id).limit(10),
+            sess.query(User, adalias.email_address, adalias.id).\
+                    outerjoin(adalias, User.addresses).\
+                    from_self(User, adalias.email_address).\
+                    options(joinedload(User.addresses)).\
+                    order_by(User.id, adalias.id).limit(10),
+        ]:
+            eq_(
+
+                q.all(),
+                [(User(addresses=[
+                            Address(user_id=7,email_address=u'jack@bean.com',id=1)],
+                            name=u'jack',id=7), u'jack@bean.com'), 
+                (User(addresses=[
+                                    Address(user_id=8,email_address=u'ed@wood.com',id=2), 
+                                    Address(user_id=8,email_address=u'ed@bettyboop.com',id=3), 
+                                    Address(user_id=8,email_address=u'ed@lala.com',id=4)],
+                                        name=u'ed',id=8), u'ed@wood.com'), 
+                (User(addresses=[
+                            Address(user_id=8,email_address=u'ed@wood.com',id=2), 
+                            Address(user_id=8,email_address=u'ed@bettyboop.com',id=3), 
+                            Address(user_id=8,email_address=u'ed@lala.com',id=4)],name=u'ed',id=8), 
+                                        u'ed@bettyboop.com'), 
+                (User(addresses=[
+                            Address(user_id=8,email_address=u'ed@wood.com',id=2), 
+                            Address(user_id=8,email_address=u'ed@bettyboop.com',id=3), 
+                            Address(user_id=8,email_address=u'ed@lala.com',id=4)],name=u'ed',id=8), 
+                                        u'ed@lala.com'), 
+                (User(addresses=[Address(user_id=9,email_address=u'fred@fred.com',id=5)],name=u'fred',id=9), 
+                                        u'fred@fred.com'), 
+
+                (User(addresses=[],name=u'chuck',id=10), None)]
+        )
+
+    def test_column_from_limited_joinedload(self):
+        sess = create_session()
+    
+        def go():
+            results = sess.query(User).limit(1).\
+                        options(joinedload('addresses')).\
+                        add_column(User.name).all()
+            eq_(results, [(User(name='jack'), 'jack')])
+        self.assert_sql_count(testing.db, go, 1)
+    
+    @testing.fails_on('postgresql+pg8000', "'type oid 705 not mapped to py type' (due to literal)")
+    def test_self_referential(self):
+    
+        sess = create_session()
+        oalias = aliased(Order)
+
+        for q in [
+            sess.query(Order, oalias).\
+                    filter(Order.user_id==oalias.user_id).filter(Order.user_id==7).\
+                    filter(Order.id>oalias.id).order_by(Order.id, oalias.id),
+            sess.query(Order, oalias).from_self().filter(Order.user_id==oalias.user_id).\
+                    filter(Order.user_id==7).filter(Order.id>oalias.id).\
+                    order_by(Order.id, oalias.id),
+        
+            # same thing, but reversed.  
+            sess.query(oalias, Order).from_self().filter(oalias.user_id==Order.user_id).\
+                            filter(oalias.user_id==7).filter(Order.id<oalias.id).\
+                            order_by(oalias.id, Order.id),
+        
+            # here we go....two layers of aliasing
+            sess.query(Order, oalias).filter(Order.user_id==oalias.user_id).\
+                            filter(Order.user_id==7).filter(Order.id>oalias.id).\
+                            from_self().order_by(Order.id, oalias.id).\
+                            limit(10).options(joinedload(Order.items)),
+
+            # gratuitous four layers
+            sess.query(Order, oalias).filter(Order.user_id==oalias.user_id).\
+                            filter(Order.user_id==7).filter(Order.id>oalias.id).from_self().\
+                            from_self().from_self().order_by(Order.id, oalias.id).\
+                            limit(10).options(joinedload(Order.items)),
+
+        ]:
+    
+            eq_(
+            q.all(),
+            [
+                (Order(address_id=1,description=u'order 3',isopen=1,user_id=7,id=3), 
+                        Order(address_id=1,description=u'order 1',isopen=0,user_id=7,id=1)), 
+                (Order(address_id=None,description=u'order 5',isopen=0,user_id=7,id=5), 
+                        Order(address_id=1,description=u'order 1',isopen=0,user_id=7,id=1)), 
+                (Order(address_id=None,description=u'order 5',isopen=0,user_id=7,id=5), 
+                        Order(address_id=1,description=u'order 3',isopen=1,user_id=7,id=3))                
+            ]
+        )
+        
+        
+        # ensure column expressions are taken from inside the subquery, not restated at the top
+        q = sess.query(Order.id, Order.description, literal_column("'q'").label('foo')).\
+            filter(Order.description == u'order 3').from_self()
+        self.assert_compile(q,
+                            "SELECT anon_1.orders_id AS "
+                            "anon_1_orders_id, anon_1.orders_descriptio"
+                            "n AS anon_1_orders_description, "
+                            "anon_1.foo AS anon_1_foo FROM (SELECT "
+                            "orders.id AS orders_id, "
+                            "orders.description AS orders_description, "
+                            "'q' AS foo FROM orders WHERE "
+                            "orders.description = :description_1) AS "
+                            "anon_1", use_default_dialect=True)
+        eq_(
+            q.all(),
+            [(3, u'order 3', 'q')]
+        )
+        
+    
+    def test_multi_mappers(self):
+
+        test_session = create_session()
+
+        (user7, user8, user9, user10) = test_session.query(User).all()
+        (address1, address2, address3, address4, address5) = \
+                        test_session.query(Address).all()
+
+        expected = [(user7, address1),
+            (user8, address2),
+            (user8, address3),
+            (user8, address4),
+            (user9, address5),
+            (user10, None)]
+
+        sess = create_session()
+
+        selectquery = users.outerjoin(addresses).select(use_labels=True, order_by=[users.c.id, addresses.c.id])
+        eq_(list(sess.query(User, Address).instances(selectquery.execute())), expected)
+        sess.expunge_all()
+
+        for address_entity in (Address, aliased(Address)):
+            q = sess.query(User).add_entity(address_entity).\
+                            outerjoin(address_entity, 'addresses').\
+                            order_by(User.id, address_entity.id)
+            eq_(q.all(), expected)
+            sess.expunge_all()
+
+            q = sess.query(User).add_entity(address_entity)
+            q = q.join(address_entity, 'addresses')
+            q = q.filter_by(email_address='ed@bettyboop.com')
+            eq_(q.all(), [(user8, address3)])
+            sess.expunge_all()
+
+            q = sess.query(User, address_entity).join(address_entity, 'addresses').\
+                                    filter_by(email_address='ed@bettyboop.com')
+            eq_(q.all(), [(user8, address3)])
+            sess.expunge_all()
+
+            q = sess.query(User, address_entity).join(address_entity, 'addresses').\
+                                    options(joinedload('addresses')).\
+                                    filter_by(email_address='ed@bettyboop.com')
+            eq_(list(util.OrderedSet(q.all())), [(user8, address3)])
+            sess.expunge_all()
+
+    def test_aliased_multi_mappers(self):
+        sess = create_session()
+
+        (user7, user8, user9, user10) = sess.query(User).all()
+        (address1, address2, address3, address4, address5) = sess.query(Address).all()
+
+        expected = [(user7, address1),
+            (user8, address2),
+            (user8, address3),
+            (user8, address4),
+            (user9, address5),
+            (user10, None)]
+
+        q = sess.query(User)
+        adalias = addresses.alias('adalias')
+        q = q.add_entity(Address, alias=adalias).select_from(users.outerjoin(adalias))
+        l = q.order_by(User.id, adalias.c.id).all()
+        assert l == expected
+
+        sess.expunge_all()
+
+        q = sess.query(User).add_entity(Address, alias=adalias)
+        l = q.select_from(users.outerjoin(adalias)).filter(adalias.c.email_address=='ed@bettyboop.com').all()
+        assert l == [(user8, address3)]
+
+    def test_with_entities(self):
+        sess = create_session()
+        
+        q = sess.query(User).filter(User.id==7).order_by(User.name)
+        
+        self.assert_compile(
+            q.with_entities(User.id,Address).\
+                    filter(Address.user_id == User.id),
+                    'SELECT users.id AS users_id, addresses.id '
+                    'AS addresses_id, addresses.user_id AS '
+                    'addresses_user_id, addresses.email_address'
+                    ' AS addresses_email_address FROM users, '
+                    'addresses WHERE users.id = :id_1 AND '
+                    'addresses.user_id = users.id ORDER BY '
+                    'users.name', 
+                use_default_dialect=True)
+        
+        
+    def test_multi_columns(self):
+        sess = create_session()
+
+        expected = [(u, u.name) for u in sess.query(User).all()]
+
+        for add_col in (User.name, users.c.name):
+            assert sess.query(User).add_column(add_col).all() == expected
+            sess.expunge_all()
+
+        assert_raises(sa_exc.InvalidRequestError, sess.query(User).add_column, object())
+
+    def test_add_multi_columns(self):
+        """test that add_column accepts a FROM clause."""
+    
+        sess = create_session()
+    
+        eq_(
+            sess.query(User.id).add_column(users).all(),
+            [(7, 7, u'jack'), (8, 8, u'ed'), (9, 9, u'fred'), (10, 10, u'chuck')]
+        )
+    
+    def test_multi_columns_2(self):
+        """test aliased/nonalised joins with the usage of add_column()"""
+        sess = create_session()
+
+        (user7, user8, user9, user10) = sess.query(User).all()
+        expected = [(user7, 1),
+            (user8, 3),
+            (user9, 1),
+            (user10, 0)
+            ]
+
+        q = sess.query(User)
+        q = q.group_by(users).order_by(User.id).outerjoin('addresses').\
+                                add_column(func.count(Address.id).label('count'))
+        eq_(q.all(), expected)
+        sess.expunge_all()
+    
+        adalias = aliased(Address)
+        q = sess.query(User)
+        q = q.group_by(users).order_by(User.id).outerjoin(adalias, 'addresses').\
+                                add_column(func.count(adalias.id).label('count'))
+        eq_(q.all(), expected)
+        sess.expunge_all()
+
+        # TODO: figure out why group_by(users) doesn't work here
+        s = select([users, func.count(addresses.c.id).label('count')]).\
+                        select_from(users.outerjoin(addresses)).\
+                        group_by(*[c for c in users.c]).order_by(User.id)
+        q = sess.query(User)
+        l = q.add_column("count").from_statement(s).all()
+        assert l == expected
+
+
+    def test_raw_columns(self):
+        sess = create_session()
+        (user7, user8, user9, user10) = sess.query(User).all()
+        expected = [
+            (user7, 1, "Name:jack"),
+            (user8, 3, "Name:ed"),
+            (user9, 1, "Name:fred"),
+            (user10, 0, "Name:chuck")]
+
+        adalias = addresses.alias()
+        q = create_session().query(User).add_column(func.count(adalias.c.id))\
+            .add_column(("Name:" + users.c.name)).outerjoin(adalias, 'addresses')\
+            .group_by(users).order_by(users.c.id)
+
+        assert q.all() == expected
+
+        # test with a straight statement
+        s = select([users, func.count(addresses.c.id).label('count'), 
+                            ("Name:" + users.c.name).label('concat')], 
+                            from_obj=[users.outerjoin(addresses)], 
+                            group_by=[c for c in users.c], order_by=[users.c.id])
+        q = create_session().query(User)
+        l = q.add_column("count").add_column("concat").from_statement(s).all()
+        assert l == expected
+
+        sess.expunge_all()
+
+        # test with select_from()
+        q = create_session().query(User).add_column(func.count(addresses.c.id))\
+            .add_column(("Name:" + users.c.name)).select_from(users.outerjoin(addresses))\
+            .group_by(users).order_by(users.c.id)
+
+        assert q.all() == expected
+        sess.expunge_all()
+
+        q = create_session().query(User).add_column(func.count(addresses.c.id))\
+            .add_column(("Name:" + users.c.name)).outerjoin('addresses')\
+            .group_by(users).order_by(users.c.id)
+
+        assert q.all() == expected
+        sess.expunge_all()
+
+        q = create_session().query(User).add_column(func.count(adalias.c.id))\
+            .add_column(("Name:" + users.c.name)).outerjoin(adalias, 'addresses')\
+            .group_by(users).order_by(users.c.id)
+
+        assert q.all() == expected
+        sess.expunge_all()
+
+class SelectFromTest(QueryTest, AssertsCompiledSQL):
+    run_setup_mappers = None
+
+    def test_replace_with_select(self):
+        mapper(User, users, properties = {
+            'addresses':relationship(Address)
+        })
+        mapper(Address, addresses)
+
+        sel = users.select(users.c.id.in_([7, 8])).alias()
+        sess = create_session()
+
+        eq_(sess.query(User).select_from(sel).all(), [User(id=7), User(id=8)])
+
+        eq_(sess.query(User).select_from(sel).filter(User.id==8).all(), [User(id=8)])
+
+        eq_(sess.query(User).select_from(sel).order_by(desc(User.name)).all(), [
+            User(name='jack',id=7), User(name='ed',id=8)
+        ])
+
+        eq_(sess.query(User).select_from(sel).order_by(asc(User.name)).all(), [
+            User(name='ed',id=8), User(name='jack',id=7)
+        ])
+
+        eq_(sess.query(User).select_from(sel).options(joinedload('addresses')).first(),
+            User(name='jack', addresses=[Address(id=1)])
+        )
+
+    def test_join_mapper_order_by(self):
+        """test that mapper-level order_by is adapted to a selectable."""
+    
+        mapper(User, users, order_by=users.c.id)
+
+        sel = users.select(users.c.id.in_([7, 8]))
+        sess = create_session()
+
+        eq_(sess.query(User).select_from(sel).all(),
+            [
+                User(name='jack',id=7), User(name='ed',id=8)
+            ]
+        )
+
+    def test_differentiate_self_external(self):
+        """test some different combinations of joining a table to a subquery of itself."""
+        
+        mapper(User, users)
+        
+        sess = create_session()
+
+        sel = sess.query(User).filter(User.id.in_([7, 8])).subquery()
+        ualias = aliased(User)
+        
+        self.assert_compile(
+            sess.query(User).join(sel, User.id>sel.c.id),
+            "SELECT users.id AS users_id, users.name AS users_name FROM "
+            "users JOIN (SELECT users.id AS id, users.name AS name FROM "
+            "users WHERE users.id IN (:id_1, :id_2)) AS anon_1 ON users.id > anon_1.id",
+            use_default_dialect=True
+        )
+    
+        self.assert_compile(
+            sess.query(ualias).select_from(sel).filter(ualias.id>sel.c.id),
+            "SELECT users_1.id AS users_1_id, users_1.name AS users_1_name FROM "
+            "users AS users_1, (SELECT users.id AS id, users.name AS name FROM "
+            "users WHERE users.id IN (:id_1, :id_2)) AS anon_1 WHERE users_1.id > anon_1.id",
+            use_default_dialect=True
+        )
+
+        # these two are essentially saying, "join ualias to ualias", so an 
+        # error is raised.  join() deals with entities, not what's in
+        # select_from().
+        assert_raises(sa_exc.InvalidRequestError,
+            sess.query(ualias).select_from(sel).join, ualias, ualias.id>sel.c.id
+        )
+
+        assert_raises(sa_exc.InvalidRequestError,
+            sess.query(ualias).select_from(sel).join, ualias, ualias.id>User.id
+        )
+
+        salias = aliased(User, sel)
+        self.assert_compile(
+            sess.query(salias).join(ualias, ualias.id>salias.id),
+            "SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name FROM "
+            "(SELECT users.id AS id, users.name AS name FROM users WHERE users.id "
+            "IN (:id_1, :id_2)) AS anon_1 JOIN users AS users_1 ON users_1.id > anon_1.id",
+            use_default_dialect=True
+        )
+        
+        
+        # this one uses an explicit join(left, right, onclause) so works
+        self.assert_compile(
+            sess.query(ualias).select_from(join(sel, ualias, ualias.id>sel.c.id)),
+            "SELECT users_1.id AS users_1_id, users_1.name AS users_1_name FROM "
+            "(SELECT users.id AS id, users.name AS name FROM users WHERE users.id "
+            "IN (:id_1, :id_2)) AS anon_1 JOIN users AS users_1 ON users_1.id > anon_1.id",
+            use_default_dialect=True
+        )
+        
+        
+        
+    def test_join_no_order_by(self):
+        mapper(User, users)
+
+        sel = users.select(users.c.id.in_([7, 8]))
+        sess = create_session()
+
+        eq_(sess.query(User).select_from(sel).all(),
+            [
+                User(name='jack',id=7), User(name='ed',id=8)
+            ]
+        )
+
+    def test_join(self):
+        mapper(User, users, properties = {
+            'addresses':relationship(Address)
+        })
+        mapper(Address, addresses)
+
+        sel = users.select(users.c.id.in_([7, 8]))
+        sess = create_session()
+
+        eq_(sess.query(User).select_from(sel).join('addresses').
+                    add_entity(Address).order_by(User.id).order_by(Address.id).all(),
+            [
+                (User(name='jack',id=7), Address(user_id=7,email_address='jack@bean.com',id=1)),
+                (User(name='ed',id=8), Address(user_id=8,email_address='ed@wood.com',id=2)),
+                (User(name='ed',id=8), Address(user_id=8,email_address='ed@bettyboop.com',id=3)),
+                (User(name='ed',id=8), Address(user_id=8,email_address='ed@lala.com',id=4))
+            ]
+        )
+
+        adalias = aliased(Address)
+        eq_(sess.query(User).select_from(sel).join(adalias, 'addresses').
+                    add_entity(adalias).order_by(User.id).order_by(adalias.id).all(),
+            [
+                (User(name='jack',id=7), Address(user_id=7,email_address='jack@bean.com',id=1)),
+                (User(name='ed',id=8), Address(user_id=8,email_address='ed@wood.com',id=2)),
+                (User(name='ed',id=8), Address(user_id=8,email_address='ed@bettyboop.com',id=3)),
+                (User(name='ed',id=8), Address(user_id=8,email_address='ed@lala.com',id=4))
+            ]
+        )
+    
+
+    def test_more_joins(self):
+        mapper(User, users, properties={
+            'orders':relationship(Order, backref='user'), # o2m, m2o
+        })
+        mapper(Order, orders, properties={
+            'items':relationship(Item, secondary=order_items,
+                                        order_by=items.c.id),  #m2m
+        })
+        mapper(Item, items, properties={
+            'keywords':relationship(Keyword, secondary=item_keywords,
+                                        order_by=keywords.c.id) #m2m
+        })
+        mapper(Keyword, keywords)
+
+        sess = create_session()
+        sel = users.select(users.c.id.in_([7, 8]))
+
+        eq_(sess.query(User).select_from(sel).\
+                join('orders', 'items', 'keywords').\
+                filter(Keyword.name.in_(['red', 'big', 'round'])).\
+                all(), 
+        [
+            User(name=u'jack',id=7)
+        ])
+
+        eq_(sess.query(User).select_from(sel).\
+                    join('orders', 'items', 'keywords', aliased=True).\
+                    filter(Keyword.name.in_(['red', 'big', 'round'])).\
+                    all(), 
+        [
+            User(name=u'jack',id=7)
+        ])
+
+        def go():
+            eq_(
+                sess.query(User).select_from(sel).
+                        options(joinedload_all('orders.items.keywords')).
+                        join('orders', 'items', 'keywords', aliased=True).
+                        filter(Keyword.name.in_(['red', 'big', 'round'])).\
+                        all(), 
+                [
+                User(name=u'jack',orders=[
+                    Order(description=u'order 1',items=[
+                        Item(description=u'item 1',
+                            keywords=[
+                                Keyword(name=u'red'), 
+                                Keyword(name=u'big'),
+                                Keyword(name=u'round')
+                            ]),
+                        Item(description=u'item 2',
+                            keywords=[
+                                Keyword(name=u'red',id=2),
+                                Keyword(name=u'small',id=5),
+                                Keyword(name=u'square')
+                            ]),
+                        Item(description=u'item 3',
+                                keywords=[
+                                    Keyword(name=u'green',id=3),
+                                    Keyword(name=u'big',id=4),
+                                    Keyword(name=u'round',id=6)])
+                    ]),
+                    Order(description=u'order 3',items=[
+                        Item(description=u'item 3',
+                                keywords=[
+                                    Keyword(name=u'green',id=3),
+                                    Keyword(name=u'big',id=4),
+                                    Keyword(name=u'round',id=6)
+                                ]),
+                        Item(description=u'item 4',keywords=[],id=4),
+                        Item(description=u'item 5',keywords=[],id=5)
+                        ]),
+                    Order(description=u'order 5',
+                        items=[
+                            Item(description=u'item 5',keywords=[])])
+                        ])
+                ])
+        self.assert_sql_count(testing.db, go, 1)
+        
+        sess.expunge_all()
+        sel2 = orders.select(orders.c.id.in_([1,2,3]))
+        eq_(sess.query(Order).select_from(sel2).\
+                    join('items', 'keywords').\
+                    filter(Keyword.name == 'red').\
+                    order_by(Order.id).all(), [
+            Order(description=u'order 1',id=1),
+            Order(description=u'order 2',id=2),
+        ])
+        eq_(sess.query(Order).select_from(sel2).\
+                    join('items', 'keywords', aliased=True).\
+                    filter(Keyword.name == 'red').\
+                    order_by(Order.id).all(), [
+            Order(description=u'order 1',id=1),
+            Order(description=u'order 2',id=2),
+        ])
+
+
+    def test_replace_with_eager(self):
+        mapper(User, users, properties = {
+            'addresses':relationship(Address, order_by=addresses.c.id)
+        })
+        mapper(Address, addresses)
+
+        sel = users.select(users.c.id.in_([7, 8]))
+        sess = create_session()
+
+        def go():
+            eq_(sess.query(User).options(joinedload('addresses')).select_from(sel).order_by(User.id).all(),
+                [
+                    User(id=7, addresses=[Address(id=1)]),
+                    User(id=8, addresses=[Address(id=2), Address(id=3), Address(id=4)])
+                ]
+            )
+        self.assert_sql_count(testing.db, go, 1)
+        sess.expunge_all()
+
+        def go():
+            eq_(sess.query(User).options(joinedload('addresses')).select_from(sel).filter(User.id==8).order_by(User.id).all(),
+                [User(id=8, addresses=[Address(id=2), Address(id=3), Address(id=4)])]
+            )
+        self.assert_sql_count(testing.db, go, 1)
+        sess.expunge_all()
+
+        def go():
+            eq_(sess.query(User).options(joinedload('addresses')).select_from(sel).order_by(User.id)[1], User(id=8, addresses=[Address(id=2), Address(id=3), Address(id=4)]))
+        self.assert_sql_count(testing.db, go, 1)
+
+class CustomJoinTest(QueryTest):
+    run_setup_mappers = None
+
+    def test_double_same_mappers(self):
+        """test aliasing of joins with a custom join condition"""
+        mapper(Address, addresses)
+        mapper(Order, orders, properties={
+            'items':relationship(Item, secondary=order_items, lazy='select', order_by=items.c.id),
+        })
+        mapper(Item, items)
+        mapper(User, users, properties = dict(
+            addresses = relationship(Address, lazy='select'),
+            open_orders = relationship(Order, primaryjoin = and_(orders.c.isopen == 1, users.c.id==orders.c.user_id), lazy='select'),
+            closed_orders = relationship(Order, primaryjoin = and_(orders.c.isopen == 0, users.c.id==orders.c.user_id), lazy='select')
+        ))
+        q = create_session().query(User)
+        
+        eq_(
+            q.join('open_orders', 'items', aliased=True).filter(Item.id==4).\
+                        join('closed_orders', 'items', aliased=True).filter(Item.id==3).all(),
+            [User(id=7)]
+        )
+
+class ExternalColumnsTest(QueryTest):
+    """test mappers with SQL-expressions added as column properties."""
+
+    run_setup_mappers = None
+
+    def test_external_columns_bad(self):
+
+        assert_raises_message(sa_exc.ArgumentError, "not represented in the mapper's table", mapper, User, users, properties={
+            'concat': (users.c.id * 2),
+        })
+        clear_mappers()
+
+    def test_external_columns(self):
+        """test querying mappings that reference external columns or selectables."""
+    
+        mapper(User, users, properties={
+            'concat': column_property((users.c.id * 2)),
+            'count': column_property(
+                            select([func.count(addresses.c.id)], users.c.id==addresses.c.user_id).\
+                            correlate(users).\
+                            as_scalar())
+        })
+
+        mapper(Address, addresses, properties={
+            'user':relationship(User)
+        })
+
+        sess = create_session()
+    
+        sess.query(Address).options(joinedload('user')).all()
+
+        eq_(sess.query(User).all(), 
+            [
+                User(id=7, concat=14, count=1),
+                User(id=8, concat=16, count=3),
+                User(id=9, concat=18, count=1),
+                User(id=10, concat=20, count=0),
+            ]
+        )
+
+        address_result = [
+            Address(id=1, user=User(id=7, concat=14, count=1)),
+            Address(id=2, user=User(id=8, concat=16, count=3)),
+            Address(id=3, user=User(id=8, concat=16, count=3)),
+            Address(id=4, user=User(id=8, concat=16, count=3)),
+            Address(id=5, user=User(id=9, concat=18, count=1))
+        ]
+        eq_(sess.query(Address).all(), address_result)
+
+        # run the eager version twice to test caching of aliased clauses
+        for x in range(2):
+            sess.expunge_all()
+            def go():
+               eq_(sess.query(Address).\
+                            options(joinedload('user')).\
+                            order_by(Address.id).all(), 
+                    address_result)
+            self.assert_sql_count(testing.db, go, 1)
+    
+        ualias = aliased(User)
+        eq_(
+            sess.query(Address, ualias).join(ualias, 'user').all(), 
+            [(address, address.user) for address in address_result]
+        )
+
+        eq_(
+                sess.query(Address, ualias.count).\
+                            join(ualias, 'user').\
+                            join('user', aliased=True).\
+                            order_by(Address.id).all(),
+                [
+                    (Address(id=1), 1),
+                    (Address(id=2), 3),
+                    (Address(id=3), 3),
+                    (Address(id=4), 3),
+                    (Address(id=5), 1)
+                ]
+            )
+
+        eq_(sess.query(Address, ualias.concat, ualias.count).
+                        join(ualias, 'user').
+                        join('user', aliased=True).order_by(Address.id).all(),
+            [
+                (Address(id=1), 14, 1),
+                (Address(id=2), 16, 3),
+                (Address(id=3), 16, 3),
+                (Address(id=4), 16, 3),
+                (Address(id=5), 18, 1)
+            ]
+        )
+
+        ua = aliased(User)
+        eq_(sess.query(Address, ua.concat, ua.count).
+                    select_from(join(Address, ua, 'user')).
+                    options(joinedload(Address.user)).order_by(Address.id).all(),
+            [
+                (Address(id=1, user=User(id=7, concat=14, count=1)), 14, 1),
+                (Address(id=2, user=User(id=8, concat=16, count=3)), 16, 3),
+                (Address(id=3, user=User(id=8, concat=16, count=3)), 16, 3),
+                (Address(id=4, user=User(id=8, concat=16, count=3)), 16, 3),
+                (Address(id=5, user=User(id=9, concat=18, count=1)), 18, 1)
+            ]
+        )
+
+        eq_(list(sess.query(Address).join('user').values(Address.id, User.id, User.concat, User.count)), 
+            [(1, 7, 14, 1), (2, 8, 16, 3), (3, 8, 16, 3), (4, 8, 16, 3), (5, 9, 18, 1)]
+        )
+
+        eq_(list(sess.query(Address, ua).select_from(join(Address,ua, 'user')).values(Address.id, ua.id, ua.concat, ua.count)), 
+            [(1, 7, 14, 1), (2, 8, 16, 3), (3, 8, 16, 3), (4, 8, 16, 3), (5, 9, 18, 1)]
+        )
+
+    def test_external_columns_joinedload(self):
+        # in this test, we have a subquery on User that accesses "addresses", underneath
+        # an joinedload for "addresses".  So the "addresses" alias adapter needs to *not* hit 
+        # the "addresses" table within the "user" subquery, but "user" still needs to be adapted.
+        # therefore the long standing practice of eager adapters being "chained" has been removed
+        # since its unnecessary and breaks this exact condition.
+        mapper(User, users, properties={
+            'addresses':relationship(Address, backref='user', order_by=addresses.c.id),
+            'concat': column_property((users.c.id * 2)),
+            'count': column_property(select([func.count(addresses.c.id)], users.c.id==addresses.c.user_id).correlate(users))
+        })
+        mapper(Address, addresses)
+        mapper(Order, orders, properties={
+            'address':relationship(Address),  # m2o
+        })
+
+        sess = create_session()
+        def go():
+            o1 = sess.query(Order).options(joinedload_all('address.user')).get(1)
+            eq_(o1.address.user.count, 1)
+        self.assert_sql_count(testing.db, go, 1)
+
+        sess = create_session()
+        def go():
+            o1 = sess.query(Order).options(joinedload_all('address.user')).first()
+            eq_(o1.address.user.count, 1)
+        self.assert_sql_count(testing.db, go, 1)
+
+class TestOverlyEagerEquivalentCols(_base.MappedTest):
+    @classmethod
+    def define_tables(cls, metadata):
+        global base, sub1, sub2
+        base = Table('base', metadata, 
+            Column('id', Integer, primary_key=True, test_needs_autoincrement=True),
+            Column('data', String(50))
+        )
+
+        sub1 = Table('sub1', metadata, 
+            Column('id', Integer, ForeignKey('base.id'), primary_key=True),
+            Column('data', String(50))
+        )
+
+        sub2 = Table('sub2', metadata, 
+            Column('id', Integer, ForeignKey('base.id'), ForeignKey('sub1.id'), primary_key=True),
+            Column('data', String(50))
+        )
+
+    def test_equivs(self):
+        class Base(_base.ComparableEntity):
+            pass
+        class Sub1(_base.ComparableEntity):
+            pass
+        class Sub2(_base.ComparableEntity):
+            pass
+    
+        mapper(Base, base, properties={
+            'sub1':relationship(Sub1),
+            'sub2':relationship(Sub2)
+        })
+    
+        mapper(Sub1, sub1)
+        mapper(Sub2, sub2)
+        sess = create_session()
+    
+        s11 = Sub1(data='s11')
+        s12 = Sub1(data='s12')
+        s2 = Sub2(data='s2')
+        b1 = Base(data='b1', sub1=[s11], sub2=[])
+        b2 = Base(data='b1', sub1=[s12], sub2=[])
+        sess.add(b1)
+        sess.add(b2)
+        sess.flush()
+    
+        # theres an overlapping ForeignKey here, so not much option except
+        # to artifically control the flush order
+        b2.sub2 = [s2]
+        sess.flush()
+    
+        q = sess.query(Base).outerjoin('sub2', aliased=True)
+        assert sub1.c.id not in q._filter_aliases.equivalents
+
+        eq_(
+            sess.query(Base).join('sub1').outerjoin('sub2', aliased=True).\
+                filter(Sub1.id==1).one(),
+                b1
+        )