[sqlalchemy] Strange behaviour when mapping tables without a "real" PK

[Alberto Valverde]

Hi,

I've encountered a strange behaviour when mapping a table which  
hasn't got a PrimaryKeyConstraint declared but a primary_key is  
declared when building the mapper (in order to be able to map it).

Queries through the mapper work fine until the "limit" keyword is  
used. Am I doing something wrong?

Attached is a simple script that reproduces the problem on SA 0.3.10

Thanks,

Alberto


--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---


"""The following query fails when using "limit" and no primary key is declared
at the table level but is declared on the mapper. Exception raised:

exceptions.SQLError(context.statement, context.parameters, e)
sqlalchemy.exceptions.SQLError: (OperationalError) no such column: tbl_row_count.hub_time u'SELECT hub.rel2_id AS hub_rel2_id, hub.rel1_id AS hub_rel1_id, anon_9feb.data AS anon_9feb_data, anon_9feb.id AS anon_9feb_id, anon_d6ec.data AS anon_d6ec_data, anon_d6ec.id AS anon_d6ec_id, hub.time AS hub_time, hub.data AS hub_data \nFROM hub LEFT OUTER JOIN rel2 AS anon_9feb ON anon_9feb.id = hub.rel2_id LEFT OUTER JOIN rel1 AS anon_d6ec ON anon_d6ec.id = hub.rel1_id ORDER BY tbl_row_count.hub_time DESC, anon_9feb.oid, anon_d6ec.oid'

Run script for full traceback.
"""
from sqlalchemy import *

meta = MetaData()
engine = create_engine('sqlite:///:memory:', echo=True)
meta.bind = engine

# Tables
rel1 = Table('rel1', meta,
    Column('id', Integer, primary_key=True),
    Column('data', Unicode),
    )

rel2 = Table('rel2', meta,
    Column('id', Integer, primary_key=True),
    Column('data', Unicode),
    )


hub = Table('hub', meta,
    Column('time', DateTime, nullable=False),
    Column('rel1_id', Integer, ForeignKey('rel1.id'), nullable=False),
    Column('rel2_id', Integer, ForeignKey('rel2.id'), nullable=False),

    # Mapped schema doesn't use a primary key since it's a "hub" table
    # in a fact system and "time" doesn't have enough resolution to guarantee
    # uniqueness. 
    #XXX Uncommenting the following line fixes it.
    ##PrimaryKeyConstraint('time', 'rel1_id', 'rel2_id'),

    Column('data', Unicode),
    )

# Mapped classes
class Hub(object): pass
class Rel1(object): pass
class Rel2(object): pass

# mappers
mapper(Rel1, rel1)
mapper(Rel2, rel2)
mapper(Hub, hub, 
    # A PK must be "faked" in the mapper to be able to map it (some rows are 
    # missed due to duped pk when retrieving with the mapper but it's not much
    # of a problem since the schema is mostly queried without the ORM for data
    # analysisi).
    primary_key = [hub.c.time, hub.c.rel1_id, hub.c.rel2_id],
    properties = dict(
        #XXX: Making the relations lazy fixes it too.
        rel1 = relation(Rel1, lazy=False),
        rel2 = relation(Rel2, lazy=False),
        )
    )

def run_test():
    meta.create_all()
    sess = create_session(bind_to=engine)
    # No limit, no problem
    sess.query(Hub).select()
    # Bang!
    sess.query(Hub).select(limit=100)

if __name__ == '__main__':
    run_test()