Hello. Sorry for the long delay. I finally had enough time to produce a minimal self-contained regression. The attached file produces the following SQL:
WITH RECURSIVE q_cte(partner_id, max_depth) AS ( SELECT partner.id AS partner_id, 1 AS max_depth FROM partner WHERE partner.sponsor_id IS NULL UNION ALL SELECT partner_alias.id AS partner_id, max_depth + 1 AS max_depth FROM partner AS partner_alias, q_cte AS q_cte_alias WHERE partner_alias.sponsor_id = q_cte_alias.partner_id ) SELECT q_cte.partner_id AS q_cte_partner_id, -- suspicious partner.sponsor_id AS partner_sponsor_id FROM q_cte JOIN partner ON q_cte.partner_id = q_cte.partner_id -- BUG I would expect this (q_cte part is correct): WITH RECURSIVE q_cte(partner_id, max_depth) AS ( ... ) SELECT partner.id AS partner_id, partner.sponsor_id AS partner_sponsor_id FROM q_cte JOIN partner ON q_cte.partner_id = partner.id The bug is somehow related to the use of select_from(). Hope this helps, Ladislav Lenart On 10.5.2013 22:04, Michael Bayer wrote: > > On May 10, 2013, at 3:03 PM, Ladislav Lenart <lenart...@volny.cz> wrote: > >> Hello. >> >> My main concern was that the query creates a cartesian product and I thought >> the >> warning might have something to do with it. It haven't. The problem is >> related >> to the use of select_from(): >> >> q = session.query(cls, PersonalContact).select_from(q_cte_union) >> q = q.join(cls, cls.id == q_cte_union.c.partner_id) >> >> This part renders the following SQL: >> >> SELECT * >> FROM >> q_cte >> JOIN partner ON q_cte.partner_id = q_cte.partner_id >> >> As you can see, the JOIN condition is wrong though I have no idea why. The >> following works, however: >> >> q = session.query(cls) >> q = q.add_entity(PersonalContact) >> q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id) >> >> Is my usage of select_from() bad for some reason? Do you know what's going >> on? I >> can provide you a fully runnable test case if you still need it (on Monday). >> Note also that I use SA 0.7.9. Is this fixed in later versions? > > I've no idea why you're getting it without more context - if everything is > generated from a query(), typically the "labels" are already applied, with > the exception of some operations such as subquery() (maybe cte? not sure, I'd > need to try it, hence an already existing example saves me lots of time > tinkering around trying to guess what you're doing). > > Things have been improved in 0.8 regarding same-named columns but that only > applies when the labels are already present. -- 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.
# coding=utf-8 from sqlalchemy import Column, ForeignKey, Integer from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.sql.expression import literal_column from sqlalchemy.orm.util import aliased from sqlalchemy.engine import create_engine from sqlalchemy.orm.session import sessionmaker from sqlalchemy.orm import relationship Base = declarative_base() session = None class Partner(Base): __tablename__ = 'partner' id = Column(Integer(), primary_key=True) sponsor_id = Column(Integer(), ForeignKey('partner.id', ondelete='SET NULL')) sponsor = relationship('Partner', primaryjoin='Partner.sponsor_id == Partner.id', remote_side='Partner.id', uselist=False) def find_subtree(self, max_depth=None): cls = self.__class__ i0 = literal_column('1').label('max_depth') q_base = session.query(cls.id.label('partner_id'), i0).filter(cls.sponsor_id == self.id) q_cte = q_base.cte(name='q_cte', recursive=True) q_cte_alias = aliased(q_cte, name='q_cte_alias') partner_alias = aliased(cls, name='partner_alias') i1 = literal_column('max_depth + 1').label('max_depth') q_rec = session.query(partner_alias.id.label('partner_id'), i1) q_rec = q_rec.filter(partner_alias.sponsor_id == q_cte_alias.c.partner_id) if max_depth is not None: q_rec = q_rec.filter(q_cte_alias.c.max_depth < max_depth) q_cte_union = q_cte.union_all(q_rec) q = session.query(cls).select_from(q_cte_union) q = q.join(cls, cls.id == q_cte_union.c.partner_id) # q = q.join(q_cte_union, cls.id == q_cte_union.c.partner_id) return q def main(): global session conn_string = 'postgresql+psycopg2://lada:heslo@localhost:5433/zfp_xxx' engine = create_engine(conn_string, echo=True) session = sessionmaker(bind=engine, autoflush=False)() Base.metadata.bind = engine Base.metadata.create_all() root = Partner() session.add(root) q = root.find_subtree() print q if __name__ == '__main__': main()