how does the query come out on SQLite ? the MSSQL dialect does some more manipulation on selectables particularly when there is limit, which is probably broken for that query in 0.3 (the SQL is definitely incorrect as contacts_3457 is not stated correctly). The level of nesting and column translation on that query is high due to all the eager loading and such. needless to say, one of: the LIMIT/OFFSET, the eagerloading, the ORM generation of the query (although im not sure if 0.3 has the contains_eager() option which would allow you to link hand-rolled SQL to eagerly loaded collections), or the version of SQLA in use will probably have to change.
On Jan 6, 2009, at 9:21 PM, statemachine wrote: > > (I'm sure this problem has been addresses, but I can't find anything > specific on it through normal support resources.) > > First, we're running an older version out of necessity: 0.3.11 (we > haven't had the time to upgrade our core libraries to use newer > versions yet). I'm hoping that someone has some ideas/pointers for > working with this issue in 0.3. > > I do most of my development in MySQL or SQLite, but deploy on SQL > Server on Windows using adodbapi. I've only witnessed this issue with > SQL Server 2005, assuming other engines handle it differently. > > Applying a limit() to a model's Query object results in the statement > being rendered with a subselect, which in and of itself isn't bad. > But it's raising errors, which I presume are from conflicting names. > > Here's the ambiguous error from SQL Server: > > com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft OLE > DB Provider for SQL Server', u'The multi-part identifier > "contacts_3457.id" could not be bound.', None, 0, -2147217900), None) > > > The query generated SA is below. "contacts_3457" is specified in the > outer select and the subselect, and I don't think the db server likes > it. It's referencing values outside its execute context, I presume. > > It's fine if I remove the limit, but that's not acceptable. > > Anybody have any ideas or pointers? > > *MUCH* appreciated! ( month's behind schedule and it's not getting any > easier. :) ) > > > > -- on command: "SELECT contacts_3457.id AS contacts_id, > contacts_3457.title AS contacts_title, contacts_3457.parent_id AS > contacts_parent_id, contacts_3457.effective AS contacts_effective, > contacts_3457.modified AS contacts_modified, contacts_3457.status AS > contacts_status, contacts_3457.type AS contacts_type, > contacts_3457.description AS contacts_description, > contacts_3457.party_id AS contacts_party_id, contacts_3457.created AS > contacts_created, anon_fb34.seasonal_term AS anon_fb34_seasonal_term, > anon_fb34.corp_no AS anon_fb34_corp_no, anon_fb34.loc_no AS > anon_fb34_loc_no, anon_fb34.contact_id AS anon_fb34_contact_id, > anon_fb34.volume AS anon_fb34_volume, anon_fb34.contract_term AS > anon_fb34_contract_term, anon_fb34.modified AS anon_fb34_modified, > anon_fb34.created AS anon_fb34_created, anon_6f1e.id AS anon_6f1e_id, > anon_6f1e.title AS anon_6f1e_title, anon_6f1e.parent_id AS > anon_6f1e_parent_id, anon_6f1e.effective AS anon_6f1e_effective, > anon_6f1e.modified AS anon_6f1e_modified, anon_6f1e.status AS > anon_6f1e_status, anon_6f1e.description AS anon_6f1e_description, > anon_6f1e.party_id AS anon_6f1e_party_id, anon_6f1e.type AS > anon_6f1e_type, anon_6f1e.created AS anon_6f1e_created, > anon_fb34.mer_no AS anon_fb34_mer_no, anon_fb34.id AS anon_fb34_id > FROM (SELECT TOP 26 contacts_3457.id AS contacts_id, contacts_3457.id > AS contacts_oid > FROM dbo.contacts AS contacts_3457, dbo.customer_search_cache AS > customer_search_fb97 > WHERE customer_search_fb97.contact_id = contacts_3457.id AND > (contacts_3457.title LIKE ? OR customer_search_fb97.loc_name LIKE ? OR > customer_search_fb97.corp_name LIKE ? OR > customer_search_fb97.owner_name LIKE ? OR > customer_search_fb97.owner2_name LIKE ? OR > customer_search_fb97.loc_phone LIKE ? OR customer_search_fb97.loc_city > LIKE ? OR customer_search_fb97.loc_zip LIKE ? OR > customer_search_fb97.sic_mcc LIKE ? OR customer_search_fb97.tax_id > LIKE ?) ORDER BY contacts_3457.id) AS tbl_row_count, dbo.contacts LEFT > OUTER JOIN dbo.merchant_info AS anon_fb34 ON anon_fb34.contact_id = > contacts_3457.id LEFT OUTER JOIN dbo.contacts AS anon_6f1e ON > anon_6f1e.id = anon_fb34.contact_id > WHERE contacts_3457.id = tbl_row_count.contacts_id ORDER BY > tbl_row_count.contacts_id, anon_fb34.id, anon_6f1e.id" > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
