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()