select( TA.join(TB).select().columns) produces duplicate names x-as-y

[Former user]

#sqlite does not have problem with this, but postgres has. (and i guess any #other more strict DB). #This functionality is being used in polymorphic_union()

from sqlalchemy import * db = create_engine( 'sqlite:///:memory:') meta = BoundMetaData( db)

table_A = Table( 'A', meta, Column( 'name', type= String, ), Column( 'id', primary_key= True, type= Integer ), Column( 'atype', type= String, ), )

table_B = Table( 'B', meta, Column( 'bdata', type= String, ), Column( 'id', Integer, ForeignKey( 'A.id', ), primary_key= True ), )

selB = table_A.join( table_B).select( table_A.c.atype == 'B' ) print '---', selB

selselB = select( selB.columns) print '===', selselB

:::::::::: results in:

--- SELECT "A".name, "A".id, "A".atype, "B".bdata, "B".id FROM "A" JOIN "B" ON "A".id = "B".id WHERE "A".atype = ?

=== SELECT name, id, atype, bdata FROM (SELECT "A".name AS name, "A".id AS id, "A".atype AS atype, "B".bdata AS bdata, "B".id AS id FROM "A" JOIN "B" ON "A".id = "B".id WHERE "A".atype = ?)

======= note both "A".id AS id , "B".id AS id

[Mike Bayer]

this is what use_labels is for:

   selB = table_A.join( table_B).select( table_A.c.atype == 'B' , use_labels=True)

   ...

--- SELECT "A".name AS A_name, "A".id AS A_id, "A".atype AS A_atype, "B".bdata AS B_bdata, "B".id AS B_id 
FROM "A" JOIN "B" ON "A".id = "B".id 
WHERE "A".atype = ?
=== SELECT A_name, A_id, A_atype, B_bdata, B_id 
FROM (SELECT "A".name AS A_name, "A".id AS A_id, "A".atype AS A_atype, "B".bdata AS B_bdata, "B".id AS B_id 
FROM "A" JOIN "B" ON "A".id = "B".id 
WHERE "A".atype = ?)

as described in:

http://www.sqlalchemy.org/docs/sqlconstruction.myt#sql_select_labels

the overlap of the typical shared "id" column is not a problem with polymorphic_union since they are a foreign key to each other and are synonymous within a select. additionally any other columns which have the same name in two joined tables will not work with joined table inheritance in any case unless labels are used (which can be done on a per-column basis via label()). typically joined table inheritance (such as the built-in kind in postgres) requires that the two tables have distinct column names other than columns which are synonomous (and therefore dont need to be stated separately in the join).

[Mike Bayer]

we've made changes in properties.py to account for the issues that arise from the polymorphic_union having columns of the right name but wrong parent table (using ClauseAdapter, a unit test is in test/orm/inheritance5.py RelationTest4). but that is a separate issue from this ticket.