[sqlalchemy] Unexpected SQL output for query with contains_eager and innerjoin

[Etienne Rouxel]

Hello

I am wondering why the outputs q1 and q2 below are not the same. Is it a 
bug?
 

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

_relation_a_table = Table('relation_a', Base.metadata,
        Column('id', Integer, primary_key=True),
        Column('refno', Integer)
    )

_relation_b_table = Table('relation_b', Base.metadata,
        Column('id', Integer, primary_key=True),
        ForeignKeyConstraint(['id'], ['relation_a.id'])
    )

_relation_c_table = Table('relation_c', Base.metadata,
        Column('id', Integer, primary_key=True),
        ForeignKeyConstraint(['id'], ['relation_b.id'])
    )

class RelationA(Base):
    __table__ = _relation_a_table

class RelationB(Base):
    __table__ = _relation_b_table

    relation_a = relationship('RelationA',
                innerjoin=True,
                backref=backref('relation_b'))

class RelationC(Base):
    __table__ = _relation_c_table

    relation_b = relationship('RelationB',
                innerjoin=True,
                backref=backref('relation_c',
                    innerjoin=True,
                    lazy='joined'))

if __name__ == '__main__':

    # Initialize database models
    engine = create_engine('postgresql://xxx@localhost:5432/xxx')
    Session = sessionmaker(bind=engine)
    session = Session()

    q1 = session.query(RelationA).\
        options(joinedload(RelationA.relation_b))

    q2 = session.query(RelationA).\
        outerjoin(RelationA.relation_b).\
        options(contains_eager(RelationA.relation_b))

    print q1
    print q2

#    Actual and expected SQL output for q1:

#    SELECT
#        relation_a.id AS relation_a_id,
#        relation_a.refno AS relation_a_refno,
#        relation_b_1.id AS relation_b_1_id,
#        relation_c_1.id AS relation_c_1_id
#    FROM relation_a
#    LEFT OUTER JOIN relation_b AS relation_b_1 ON relation_a.id = 
relation_b_1.id
#    LEFT OUTER JOIN relation_c AS relation_c_1 ON relation_b_1.id = 
relation_c_1.id

#    Actual SQL output for q2 (with JOIN):

#    SELECT
#        relation_a.id AS relation_a_id,
#        relation_a.refno AS relation_a_refno,
#        relation_b.id AS relation_b_id,
#        relation_c_1.id AS relation_c_1_id
#    FROM relation_a
#    LEFT OUTER JOIN relation_b ON relation_a.id = relation_b.id
#    JOIN relation_c AS relation_c_1 ON relation_b.id = relation_c_1.id

#    Expected SQL output for q2 (with LEFT OUTER JOIN):

#    SELECT
#        relation_a.id AS relation_a_id,
#        relation_a.refno AS relation_a_refno,
#        relation_b.id AS relation_b_id,
#        relation_c_1.id AS relation_c_1_id
#    FROM relation_a
#    LEFT OUTER JOIN relation_b ON relation_a.id = relation_b.id
#    LEFT OUTER JOIN relation_c AS relation_c_1 ON relation_b.id = 
relation_c_1.id


-- 
You received this message because you are subscribed to the Google Groups 
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.