[sqlalchemy] UNION types x and y cannot be matched

[Eric Ongerth]

For reference:

http://www.mail-archive.com/[EMAIL PROTECTED]/msg02239.html

I found the above discussion when googling a ProgrammingError i've
been getting with a polymorphic_union:

<quote>
sqlalchemy.exceptions.SQLError: (ProgrammingError) UNION types numeric
and character varying cannot be matched
'SELECT ijoin.type AS ijoin_type, ijoin.id AS ijoin_id, ijoin.size AS
ijoin_size \nFROM (SELECT items.type AS type, skiboots.id AS id,
skiboots.size AS size \nFROM items JOIN skiboots ON items.id =
skiboots.id UNION ALL SELECT anon_c93f.type AS type, anon_c93f.id AS
id, CAST(NULL AS VARCHAR(20)) AS size \nFROM (SELECT items.id AS id,
items.type AS type \nFROM items \nWHERE items.type = %(items_type)s)
AS anon_c93f UNION ALL SELECT items.type AS type, skis.id AS id,
skis.size AS size \nFROM items JOIN skis ON items.id = skis.id) AS
ijoin ORDER BY ijoin.id' {'items_type': 'item'}
</quote>

Here's a minimal test script:

from sqlalchemy import *

db = create_engine('postgres://postgres:[EMAIL PROTECTED]:5432/
testrentals',
    encoding='utf-8')
metadata = BoundMetaData(db)

items = Table('items', metadata,
    Column('id', Integer, primary_key=True, autoincrement=True),
    Column('type', String(20)))

skis = Table('skis', metadata,
    Column('id', Integer, primary_key=True),
    Column('size', String(20), nullable=False),
    ForeignKeyConstraint(['id'], ['items.id']))

skiboots = Table('skiboots', metadata,
    Column('id', Integer, primary_key=True),
    Column('size', types.Numeric(3,1)),
    ForeignKeyConstraint(['id'], ['items.id']))

item_join = polymorphic_union(
    {
        'ski':items.join(skis),
        'skiboot':items.join(skiboots),
        'item':items.select(items.c.type=='item'),
    }, None, 'ijoin')

class Item(object):pass
class Ski(Item): pass
class SkiBoot(Item): pass

item_mapper = mapper(Item, items,
    select_table=item_join,
    polymorphic_on=item_join.c.type,
    polymorphic_identity='item')

ski_mapper = mapper(Ski, skis,
    inherits=item_mapper,
    polymorphic_identity='ski')

skiboot_mapper = mapper(SkiBoot, skiboots,
    inherits=item_mapper,
    polymorphic_identity='skiboot',
    inherit_condition = items.c.id==skiboots.c.id)

if __name__ == "__main__":
    session = create_session()
    print session.query(Item).select()

##############################################


So, skis are working fine but skiboots aren't.  If I either comment
out the 'size' column in the skiboots table:
#     Column('size', types.Numeric(3,1)),
- or - comment out the 'skiboots' line in the item_join:
#         'skiboot':items.join(skiboots),

...then it runs ok.

Maybe I'm making incorrect use of the Numeric type?  Or is this a
bug?  I want to use the Numeric type because i'd like to represent
boot sizes as, e.g., 9.5, 10.0, 10.5.  For skis the size is a String
because sometimes it's a number and sometimes a nominal size like "s",
"m", "xl", etc.  No problem with the string; i'm just wondering if I
can use the Numeric type as sqla currently stands.  Do I have to
define a custom type and stash Numerics in string representations?

Thanks!


--~--~---------~--~----~------------~-------~--~----~
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 [EMAIL PROTECTED]
For more options, visit this group at 
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---