On Nov 14, 2013, at 10:18 AM, Michael Woods <mikeswo...@gmail.com> wrote:
> Hi SA community > > This issue has been bugging me for a while. Hopefully someone has some input, > since this issue is really impeding my work at this point. > > Using the following code > > query_object = select([id_column] + columns, > and_(*where_clauses), > from_parts, > use_labels=True, > correlate=False, > distinct=True) > > I am able to produce the valid query: > > SELECT DISTINCT accard."ESOPH_REGISTRY"."PTID" AS > "accard_ESOPH_REGISTRY_PTID", > accard."ESOPH_REGISTRY_VW"."GENDER" AS > "accard_ESOPH_REGISTRY_VW_GENDER", > accard."ESOPH_REGISTRY_VW"."LAST_NAME" AS > "accard_ESOPH_REGISTRY_VW_LAST_NAME", > accard."ESOPH_REGISTRY_VW"."FIRST_NAME" AS > "accard_ESOPH_REGISTRY_VW_FIRST_NAME", > accard."ESOPH_REGISTRY"."RACE" AS > "accard_ESOPH_REGISTRY_RACE", > accard."ESOPH_BIOPSY"."SPECIMEN_NO" AS > "accard_ESOPH_BIOPSY_SPECIMEN_NO", > accard."ESOPH_BIOPSY"."SITE_DISTANCE_CM" AS > "accard_ESOPH_BIOPSY_SITE_DISTANCE_CM", > accard."ESOPH_BIOPSY"."PATHOLOGY_CODE" AS > "accard_ESOPH_BIOPSY_PATHOLOGY_CODE" > FROM accard."ESOPH_REGISTRY", > accard."ESOPH_REGISTRY_VW" > LEFT OUTER JOIN accard."MASTER_REGISTRY" ON accard."ESOPH_REGISTRY_VW"."UUID" > = accard."MASTER_REGISTRY"."UUID", > accard."ESOPH_BIOPSY" > LEFT OUTER JOIN accard."ESOPH_ENDOSCOPY" ON > accard."ESOPH_BIOPSY"."FK_ENDOSCOPY_ID" = > accard."ESOPH_ENDOSCOPY"."PK_ENDOSCOPY_ID" > WHERE accard."ESOPH_ENDOSCOPY"."PTID" = accard."ESOPH_REGISTRY"."PTID" > AND accard."MASTER_REGISTRY"."UUID" = accard."ESOPH_REGISTRY"."UUID" > AND accard."ESOPH_REGISTRY"."PTID" = accard."ESOPH_REGISTRY_VW"."PTID" > AND upper(accard."ESOPH_ENDOSCOPY"."DISPLAY") = 'Y' > AND upper(accard."ESOPH_BIOPSY"."DISPLAY") = 'Y' > AND upper(accard."ESOPH_REGISTRY"."DISPLAY") = 'Y' > AND upper(accard."MASTER_REGISTRY"."DISPLAY") = 'Y' LIMIT 250 > > All is good and right with the world. > > However, once I add a new where clause, an invalid query is produced, and the > source of my frustration begins. > > For example, the following statement: > > query_object.append_whereclause(esoph_biopsy.c['site_distance_cm'] > 5.0) > > produces the query: > > SELECT DISTINCT accard."ESOPH_REGISTRY"."PTID" AS > "accard_ESOPH_REGISTRY_PTID", > accard."ESOPH_REGISTRY_VW"."GENDER" AS > "accard_ESOPH_REGISTRY_VW_GENDER", > accard."ESOPH_REGISTRY_VW"."LAST_NAME" AS > "accard_ESOPH_REGISTRY_VW_LAST_NAME", > accard."ESOPH_REGISTRY_VW"."FIRST_NAME" AS > "accard_ESOPH_REGISTRY_VW_FIRST_NAME", > accard."ESOPH_REGISTRY"."RACE" AS > "accard_ESOPH_REGISTRY_RACE", > accard."ESOPH_BIOPSY"."SPECIMEN_NO" AS > "accard_ESOPH_BIOPSY_SPECIMEN_NO", > accard."ESOPH_BIOPSY"."SITE_DISTANCE_CM" AS > "accard_ESOPH_BIOPSY_SITE_DISTANCE_CM", > accard."ESOPH_BIOPSY"."PATHOLOGY_CODE" AS > "accard_ESOPH_BIOPSY_PATHOLOGY_CODE" > FROM accard."ESOPH_REGISTRY", > accard."ESOPH_BIOPSY", > accard."ESOPH_REGISTRY_VW" > LEFT OUTER JOIN accard."MASTER_REGISTRY" ON accard."ESOPH_REGISTRY_VW"."UUID" > = accard."MASTER_REGISTRY"."UUID", > accard."ESOPH_BIOPSY" > LEFT OUTER JOIN accard."ESOPH_ENDOSCOPY" ON > accard."ESOPH_BIOPSY"."FK_ENDOSCOPY_ID" = > accard."ESOPH_ENDOSCOPY"."PK_ENDOSCOPY_ID" > WHERE accard."ESOPH_ENDOSCOPY"."PTID" = accard."ESOPH_REGISTRY"."PTID" > AND accard."MASTER_REGISTRY"."UUID" = accard."ESOPH_REGISTRY"."UUID" > AND accard."ESOPH_REGISTRY"."PTID" = accard."ESOPH_REGISTRY_VW"."PTID" > AND upper(accard."ESOPH_ENDOSCOPY"."DISPLAY") = 'Y' > AND upper(accard."ESOPH_BIOPSY"."DISPLAY") = 'Y' > AND upper(accard."ESOPH_REGISTRY"."DISPLAY") = 'Y' > AND upper(accard."MASTER_REGISTRY"."DISPLAY") = 'Y' > AND accard."ESOPH_BIOPSY"."SITE_DISTANCE_CM" > 5.0 LIMIT 250 > > When run, it produces the error <OperationalError> ambiguous column name: > accard.ESOPH_BIOPSY.SPECIMEN_NO, which is understandable, considering the > table ESOPH_BIOPSY appears twice in the FROM clause--once as a standalone > table, and once in a join: > > accard."ESOPH_BIOPSY" LEFT OUTER JOIN accard."ESOPH_ENDOSCOPY" ON > accard."ESOPH_BIOPSY"."FK_ENDOSCOPY_ID" = > accard."ESOPH_ENDOSCOPY"."PK_ENDOSCOPY_ID" > > I'm not doing anything strange in the construction of the Select object via > select(). The value of from_parts contains 5 items: the 2 standalone tables > (accard."ESOPH_REGISTRY" and accard."ESOPH_REGISTRY_VW"), and the 2 joins. > > This is confusing behavior, as it contradicts the SQLAlchemy documentation > for select(), mainly the from_obj argument: > > from_obj – A list of ClauseElement objects which will be added to the FROM > clause of the resulting statement. Note that “from” objects are automatically > located within the columns and whereclause ClauseElements. Use this parameter > to explicitly specify “from” objects which are not automatically locatable. > This could include Table objects that aren’t otherwise present, or Join > objects whose presence will supercede that of the Table objects already > located in the other clauses. > > My understanding of the above is that if I add a column to the WHERE clause > that references a table T, first all present FROM clause objects will be > checked for T first. If T appears in a join, T will not be added to the FROM > clause objects list. Likewise, if T already appears explictly in the FROM > clause it will not be added either. > > Am I missing something? you need to send along a real example that illustrates this effect, it indicates that a copy of the Table, or an alias of some kind, is doubling up on the FROM list. this has to do with how the query was constructed in the first place and without full specifics there’s no way to guess the origins of this behavior. Also it’s probably a good idea to use select().where(), producing a new select() object, rather than append_whereclause() - the latter can lead to confusion if you re-inject pieces of the existing select() into itself.
signature.asc
Description: Message signed with OpenPGP using GPGMail
