this is very helpful because you are here running into an older feature that I think is not very applicable to modern usage, not to mention not terrifically documented, so I've added http://www.sqlalchemy.org/trac/attachment/ticket/2736/ to address changing the role of select_from() to be more what it says, and to offload a particular magic aspect of it into a new method called select_entity_from().
if you look at the docstrings added to the patch there, you may get a sense for what's been going on. I'll probably commit this soon and those docs will be up on the site. There will be an upgrade path for users in this case. On May 30, 2013, at 1:10 PM, Ladislav Lenart <lenart...@volny.cz> wrote: > 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. > > > <select_from_bug.py> -- 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.
