get(..)-query returns inconsistent results with single/joined inheritance

The problem occurs when you use inheritance (single-table or joined) and a get-query (by primary key). Example: the get() queries for an Engineer with id 1, which does not exist (since the row with id 1 represents a Manager). The query results are inconsistent: Sometimes you get None; and sometimes (when the manager is already present in the session) you get the manager instance.

mailing list thread: http://groups.google.com/group/sqlalchemy/browse_thread/thread/7ff3d7c995fb6395

test case:

from sqlalchemy import *
from sqlalchemy.orm import mapper, sessionmaker

engine = create_engine('sqlite:///:memory:', echo=True)
metadata = MetaData()

employees_table = Table('employees', metadata,
    Column('employee_id', Integer, primary_key=True),
    Column('name', String(50)),
    Column('manager_data', String(50)),
    Column('engineer_info', String(50)),
    Column('type', String(20), nullable=False)
)

class Employee(object):
    def __init__(self, name):
        self.name = name

class Manager(Employee):
    def __init__(self, name, manager_data):
        self.name = name
        self.manager_data = manager_data

class Engineer(Employee):
    def __init__(self, name, engineer_info):
        self.name = name
        self.engineer_info = engineer_info

employee_mapper = mapper(Employee, employees_table, \
    polymorphic_on=employees_table.c.type, polymorphic_identity='employee')
manager_mapper = mapper(Manager, inherits=employee_mapper, polymorphic_identity='manager')
engineer_mapper = mapper(Engineer, inherits=employee_mapper, polymorphic_identity='engineer')


metadata.drop_all(engine)
metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

session.add(Manager("Steve Jobs", "Apple CEO"))
session.commit()

session.add(Engineer("Steve Wozniak", "Apple engineer"))
session.commit()

# Need a new session in order to demonstrate
session = Session()
assert session.query(Engineer).get(1) is None # Works
assert isinstance(session.query(Manager).get(1), Manager)
assert session.query(Engineer).get(1) is None # Still works
manager = session.query(Manager).get(1)
assert session.query(Engineer).get(1) is None # fails!... is a Manager-instance

Comments (3)