Problem/bug with column_property on eagerloaded polymophic table

[Adrian Tejn Kern]

I have a column_property on a polymorphic base class. When I joinedload/subqueryload a derived class the colum_property makes the query fail.

class A(Base):

    __tablename__ = "a"

    id      = Column(Integer, primary_key=True)

    type    = Column(String(40), nullable=False)

    __mapper_args__ = {'polymorphic_on': type}

A.anything = orm.column_property(A.id + 1000)

class B(A):

    __tablename__ = "b"

    account_id      = Column(Integer, ForeignKey('a.id'), primary_key=True)

    x_id            = Column(Integer, ForeignKey('x.id'), nullable=False)

    __mapper_args__ = {'polymorphic_identity': 'named'}

class X(Base):

    __tablename__ = "x"

    id = Column(Integer, primary_key=True)

    b = orm.relationship("B"

Calling:

    print Session.query(X).options(joinedload("b"))

produces:

SELECT x.id AS x_id,
       anon_1.a_id AS anon_1_a_id,
       anon_1.a_type AS anon_1_a_type,
       a.id + %(id_1)s AS anon_2,
                     anon_1.b_account_id AS anon_1_b_account_id,
                     anon_1.b_x_id AS anon_1_b_x_id
FROM a,
     x
LEFT OUTER JOIN
  (SELECT a.id AS a_id,
          a.TYPE AS a_type,
            b.account_id AS b_account_id,
            b.x_id AS b_x_id
   FROM a
   JOIN b ON a.id = b.account_id) AS anon_1 ON x.id = anon_1.b_x_id

It seems that the "a.id + %(id_1)" should changed to "anon_1.a_id" and "a" removed from "FROM" or better "a.id + %(id_1)s" should be moved into the sub select named anon_1. This is probably what you want if the column_property was actually a subselect itself (which is want I'm actually trying to do).

Am I correct in thinking that this corner case simply isn't supported yet? Or is it a bug? Or am I doing something wrong?

Actually the above query doesn't fail outright. Although it does create a unsuspecting join. But if the column_property instead was something like

class subA(Base):

    __tablename__ = "subA"

    id = Column(Integer, primary_key=True)

    a_id = Column(Integer, ForeignKey('a.id'), nullable=False)

    value = Column(Integer, nullable=False)

A.anything = orm.column_property(

    select([func.sum(subA.value)], subA.a_id==A.id))

Then the sql would be:

SELECT x.id AS x_id,
       anon_1.a_id AS anon_1_a_id,
       anon_1.a_type AS anon_1_a_type,

  (SELECT sum("subA".value) AS sum_1
   FROM "subA"
   WHERE "subA".a_id = a.id) AS anon_2,
       anon_1.b_account_id AS anon_1_b_account_id,
       anon_1.b_x_id AS anon_1_b_x_id
FROM x
LEFT OUTER JOIN
  (SELECT a.id AS a_id,
          a.TYPE AS a_type,
            b.account_id AS b_account_id,
            b.x_id AS b_x_id
   FROM a
   JOIN b ON a.id = b.account_id) AS anon_1 ON x.id = anon_1.b_x_id

    

Which naturally doesn't work at all, since "a.id" inside the first subselect doesn't refer to anything.

PS: I have no idea how this email is going to get formatted, please let me know if it is impossible to read.

[Michael Bayer]

It's a bug but a small one... I'd be ready to jump off a bridge if this kind of thing wasn't working in general at this point.   Trying your test case, the column_property() for the moment has to be against the actual Column, not the mapped property (there's a difference):

class A(Base):

    __tablename__ = "a"

    id      = Column(Integer, primary_key=True)

    type    = Column(String(40), nullable=False)

    __mapper_args__ = {'polymorphic_on': type}

    anything = column_property(id + 1000)

or:

A.anything = column_property(A.__table__.c.id + 1000)

When you access A.id, you get an InstrumentedAttribute, which produces a SQL expression equivalent to A.__table__.c.id except for an "annotation" that tells the ORM to treat it differently, I couldn't say exactly why it goes wrong in the way it does since it typically adapts it more aggressively, not less so...the problem here is "a.id" isn't getting lumped into the "adaptation" of the "a join b" as a subquery off of "x".   Nice test case, thanks for making it easy.

Anyway, that's the workaround for now and ticket 2316 http://www.sqlalchemy.org/trac/ticket/2316 is added.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/1CSullHjqPMJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[Adrian Tejn Kern]

Thank you, very much. 

I actually did try to use the actually Column, but I could figure out how to resolve my interdependencies since my column_property is actually a subselect, and apparently I didn't test it on my test case.