Here's the query from SQLite. It's structured the same, but there's a subtle difference in that the 'contacts' table name isn't aliased, which is where the problem seems to be for mssql. As you noted, the outer/parent select is referencing the table aliased table name from the subselect (it's joining the table as 'anon_6f1e' but referencing the subselect's 'contacts_3457').
I'm glad you mentioned the eager loads, Michael. I thought I had turned them all lazy, but apparently missed one. Without that eager load the subselect isn't necessary and it works as it should. I'm sure there other queries throughout my application that will suffer from the same issue, but it's progress. Has anybody else run into the alias/subselect and mssql issue with 0.3? FYI: Here's the query from SQLite: SELECT contacts.id AS contacts_id, contacts.title AS contacts_title, contacts.parent_id AS contacts_parent_id, contacts.effective AS contacts_effective, contacts.modified AS contacts_modified, contacts.status AS contacts_status, contacts.type AS contacts_type, contacts.description AS contacts_description, contacts.party_id AS contacts_party_id, contacts.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_fb34.mer_no AS anon_fb34_mer_no, anon_fb34.id AS anon_fb34_id FROM (SELECT contacts.id AS contacts_id, contacts.oid AS contacts_oid FROM contacts, customer_search_cache WHERE customer_search_cache.contact_id = contacts.id AND (contacts.title LIKE ? OR customer_search_cache.loc_name LIKE ? OR customer_search_cache.corp_name LIKE ? OR customer_search_cache.owner_name LIKE ? OR customer_search_cache.owner2_name LIKE ? OR customer_search_cache.loc_phone LIKE ? OR customer_search_cache.loc_city LIKE ? OR customer_search_cache.loc_zip LIKE ? OR customer_search_cache.sic_mcc LIKE ? OR customer_search_cache.tax_id LIKE ?) ORDER BY contacts.oid LIMIT 26 OFFSET 0) AS tbl_row_count, contacts LEFT OUTER JOIN merchant_info AS anon_fb34 ON anon_fb34.contact_id = contacts.id WHERE contacts.id = tbl_row_count.contacts_id ORDER BY tbl_row_count.oid, anon_fb34.oid On Jan 6, 7:39 pm, Michael Bayer <mike...@zzzcomputing.com> wrote: > 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 -~----------~----~----~----~------~----~------~--~---
