All of that has been fixed in 0.8. With 0.8 both versions work fine.
On Feb 28, 2013, at 5:29 AM, Ladislav Lenart <lenart...@volny.cz> wrote: > Hello again. > > I have successfully installed SA 0.7.10 and the query as-is works. However > jonedload_all / subqueryload_all options on the query do NOT work. I have > slightly extended your cte_example which now demonstrates the issues. > joinedload_all does not crash but there is undesired sql activity after the > main > query which renders the joinedload useless. subqueryload_all crashes. > > I would be glad for any words of advice or idea(s) for possible workaround(s), > > Ladislav Lenart > > > On 28.2.2013 10:26, Ladislav Lenart wrote: >> Thank you very much for your valuable time, Michael! >> >> Your example code seems correct. The only differences I found are: >> * Missing FK on ImportedPartnerShare.partner_id: >> class ImportedPartnerShare(Base): >> deal_id = Column(Integer, ForeignKey('deal.id')) >> partner_id = Column(Integer, ForeignKey('partner.id')) # ForeignKey() is >> missing in yout example script >> * All FKs in the example should have nullable=False and ondelete='CASCADE'. >> >> But I suppose none of this makes any difference. As you wrote and confirmed, >> this issue (and many others) was resolved in SA 0.7.9. I have just verified >> that >> I am using SA 0.7.8 at the moment (version from debian distro). I apologize >> for >> the err subject. I did not check it when I wrote that line, I just assumed. >> >> >> Thank you again, >> >> Ladislav Lenart >> >> >> On 28.2.2013 04:12, Michael Bayer wrote: >>> OK, I've reconstructed mappings which correspond directly to your Query as >>> given, and it produces the identical SQL. I've inserted a bunch of rows >>> into all the tables so that a polymorphic result comes back, so that we can >>> in fact verify that the ORM reads the "client_id" column correctly. >>> >>> Your issue exists from SQLAlchemy 0.7.8 and backwards, and was fixed as of >>> 0.7.9, (we're up to 0.7.10 as well as 0.8 betas). 0.7.9 had many bug fixes >>> for the CTE feature as it had only been introduced in 0.7.6. >>> >>> Script is attached. >>> >>> >>> On Feb 27, 2013, at 4:11 PM, Michael Bayer <mike...@zzzcomputing.com> wrote: >>> >>>> >>>> On Feb 27, 2013, at 3:12 PM, Ladislav Lenart <lenart...@volny.cz> wrote: >>>> >>>>> Hello. >>>>> >>>>> Thank you for your prompt answer. I will try to create a working example >>>>> that >>>>> demonstrates the issue. Though it will take me a couple of days, maybe >>>>> weeks (my >>>>> regular work followed by a vacation). >>>>> >>>>> I have another problem. I rephrased the SQL, because postgres's planner >>>>> had >>>>> issues with EXISTS and thought it is a great idea to perform full scans of >>>>> several huge tables in order to return several hundreds result rows. >>>>> Enter CTEs... >>>>> >>>>> Short-short intro: >>>>> * Client is the joined table inheritance root. >>>>> * PersonalClient and CorporateClient are its subclasses. >>>>> * Partner is a salesman. >>>>> * Deal is a contract signed between salesman(s) and client(s). >>>>> * ImportedClientShare and ClientShare are M:N relationships between >>>>> clients and >>>>> deals. >>>>> * ImportedPartnerShare and PartnerShare are M:N relationships between >>>>> partners >>>>> and deals. >>>>> * We import deals from an external DB. Those are called imported. >>>>> Imported deal >>>>> has external_id and Imported*Share apply. >>>>> * However, a user of our system (a partner) can create a new deal >>>>> locally. Such >>>>> a deal does not have an external id (yet) and local *Share apply to it. >>>>> >>>>> The following code should return all clients of a given partner via >>>>> ImportedClientShare or via ClientShare: >>>>> >>>>> q1 = session.query(ImportedClientShare.client_id.label('client_id')) >>>>> q1 = q1.join(ImportedPartnerShare, ImportedClientShare.deal_id == >>>>> ImportedPartnerShare.deal_id) >>>>> q1 = q1.join(Deal, ImportedClientShare.deal_id == Deal.id) >>>>> q1 = q1.filter( >>>>> ImportedPartnerShare.partner_id == partner_id, # input argument >>>>> Deal.external_id != None, >>>>> ) >>>>> q2 = session.query(ClientShare.client_id.label('client_id')) >>>>> q2 = q2.join(PartnerShare, ClientShare.deal_id == >>>>> PartnerShare.deal_id) >>>>> q2 = q2.join(Deal, ClientShare.deal_id == Deal.id) >>>>> q2 = q2.filter( >>>>> PartnerShare.partner_id == partner_id, # input argument >>>>> Deal.external_id == None, >>>>> ) >>>>> client_ids = q1.union(q2).cte('client_ids') >>>>> q = session.query(Client).with_polymorphic([PersonalClient, >>>>> CorporateClient]) >>>>> q = q.join(client_ids, Client.id == client_ids.c.client_id) >>>>> >>>>> NoSuchColumnError: "Could not locate column in row for column 'client.id'" >>>>> >>>>> I also tried it without with_polymorphic() and the result is the same. >>>>> >>>>> Do you have any idea what is going on / what am I doing wrong and how I >>>>> can fix >>>>> this? >>>> >>>> Again, this is a very complex query, even more complex than the previous >>>> one, and it boils down to limits in what the ORM can currently handle. >>>> It's probably a bug, though there may be workarounds that allow it to >>>> work, however it's the kind of issue that typically takes me many hours to >>>> diagnose and fix or at least work around, given code that I can run and >>>> pdb in order to debug. This is not something you'd have much luck >>>> resolving on your own unless you wanted to become deeply familiar with >>>> SQLAlchemy internals. >>>> >>>> I would recommend again making sure all these issues remain in the latest >>>> 0.8 version and attempting to produce a rudimentary series of test classes >>>> which I can run in order to reproduce your results. I can reconstitute >>>> these models looking just at your queries, though sometimes after all that >>>> effort the issue isn't reproduced, due to some quirk in the mappings >>>> that's also required. >>>> >>>> -- >>>> 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. > > -- > 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. > > > <cte_example.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.