aliasing_bug.py

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


Base = declarative_base()


class User(Base):
    __tablename__ = "user"
    id = Column(Integer, primary_key=True)
    name = Column(String)



class OrderMetadata(Base):
    __tablename__ = "order"

    id = Column(Integer, primary_key=True)

    buyer_id = Column(ForeignKey('user.id'))
    seller_id = Column(ForeignKey('user.id'))

    buyer = relationship(User, foreign_keys=[buyer_id])
    seller = relationship(User, foreign_keys=[seller_id])

    data = Column(String)

    @declared_attr
    def order(cls):
        return relationship("OrderBase")



class OrderBase(AbstractConcreteBase, Base):
    @declared_attr
    def id(cls):
        return Column(
            Integer,
            ForeignKey(
                'order.id',
                deferrable=True,
                initially="DEFERRED",
            ),
            primary_key=True
        )

    @declared_attr
    def order_metadata(cls):
        return relationship(OrderMetadata)

    @declared_attr
    def obj_type(cls):
        return Column(String, nullable=False)

    @declared_attr
    def data(cls):
        return Column(String)


class WeirdThings(OrderBase):
    __tablename__ = "weird_things"
    __mapper_args__ = {
        'polymorphic_identity': 'weird_things',
        'concrete': True
    }



class NormalThings(OrderBase):
    __tablename__ = "normal_things"
    __mapper_args__ = {
        'polymorphic_identity': 'normal_things',
        'concrete': True
    }



e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)


u1 = User(id=1, name="r")
u2 = User(id=2, name="k")


weird_thing = WeirdThings(
    order_metadata = OrderMetadata(
        buyer=u1,
        seller=u2,
        data="stuff about order"
    ),
    obj_type="weird_things",
    data="other stuff"
)


normal_thing = NormalThings(
    order_metadata=OrderMetadata(
        buyer=u2,
        seller=u1,
        data="stuff about order"
    ),
    obj_type="normal thing",
    data="other stuff about the trade"
)

s.add_all([u1, u2, weird_thing, normal_thing])
s.commit()


Buyer = aliased(User, name="buying_user")
Seller = aliased(User, name="selling_user")


OrderMetadataAlias = aliased(OrderMetadata, name="metadata")

# This subquery now contains all of the data we need for contains eager
subquery = s.query(
    OrderBase,
    OrderMetadataAlias,
    # These are both user objects
    # XXX What is really really strange is that
    # the ordering here changes what gets selected
    # from out of the subquery by contains_eager.
    Seller,
    Buyer,
).join(
    OrderBase.order_metadata,
    OrderMetadataAlias
).join(
    Seller,
    OrderMetadataAlias.buyer
).join(
    Buyer,
    OrderMetadataAlias.buyer
).subquery('subq')




BuyerSubquery = aliased(User, subquery, name="buyer_s")
SellerSubquery = aliased(User, subquery, name="seller_s")

OrderBaseFromSubquery = aliased(OrderBase, subquery)
OrderMetadataAliasFromSubquery = aliased(
    OrderMetadataAlias, subquery
)

s.expunge_all()


res = s.query(
    OrderBaseFromSubquery
).options(
    contains_eager(OrderBaseFromSubquery.order_metadata.of_type(OrderMetadataAliasFromSubquery)).options(
        # This is where it goes badly wrong.  This for some reason pulls out the seller id, and name rather
        # than the buyer
        contains_eager(OrderMetadataAliasFromSubquery.buyer.of_type(BuyerSubquery)),
    )
)

# It's quite hard to demonstrate what happens without printing the statement.

"""
SELECT
       /* This should be id_3 and name_1 */
       subq.id_1      AS subq_id_1,
       subq.name      AS subq_name,

       subq.id_2      AS subq_id_2,
       subq.buyer_id  AS subq_buyer_id,
       subq.seller_id AS subq_seller_id,
       subq.data_1    AS subq_data_1,
       subq.id        AS subq_id,
       subq.obj_type  AS subq_obj_type,
       subq.data      AS subq_data,
       subq.type      AS subq_type
FROM (SELECT pjoin.id           AS id,
             pjoin.obj_type     AS obj_type,
             pjoin.data         AS data,
             pjoin.type         AS type,
             metadata.id        AS id_2,
             metadata.buyer_id  AS buyer_id,
             metadata.seller_id AS seller_id,
             metadata.data      AS data_1,

             /* These are eronousnly taken */
             selling_user.id    AS id_1,
             selling_user.name  AS name,

             /* XXX NOTE This columns are ignored */
             buying_user.id     AS id_3,
             buying_user.name   AS name_1

      FROM (SELECT normal_things.id       AS id,
                   normal_things.obj_type AS obj_type,
                   normal_things.data     AS data,
                   'normal_things'        AS type
            FROM normal_things
            UNION ALL
            SELECT weird_things.id       AS id,
                   weird_things.obj_type AS obj_type,
                   weird_things.data     AS data,
                   'weird_things'        AS type
            FROM weird_things) AS pjoin
               JOIN "order" ON "order".id = pjoin.id
               JOIN "order" AS metadata ON metadata.id = pjoin.id
               JOIN user AS selling_user ON selling_user.id = metadata.buyer_id
               JOIN user AS buying_user ON buying_user.id = metadata.buyer_id) AS subq
"""


# If I swap line 128 and line 129 I get the opposite, equally erroneous effect