Joined tables inheritance and Many To One lead to incorrect SQL

Version '0.4.2p3'

Python Program that should be copy/pastable to reproduce the bug

import sqlalchemy from sqlalchemy import create_engine, Table, Column, Integer, String, MetaData, ForeignKey from sqlalchemy.orm import mapper, relation, sessionmaker, scoped_session, eagerload from sqlalchemy.sql import select

import os

STATUS_INCONSISTENT = 'INCONSISTENT' STATUS_OK = 'OK'

IMAGE_FORMAT_JPEG = 'JPEG'

class AbstractItem(object): def init(self, itemId, status, size, format): super(AbstractItem, self).init()

    self.id = itemId
    self.status = status
    self.width = size[0](0) if type(size[0](0)) == int else int(size[0](0))
    self.height = size[1](1) if type(size[1](1)) == int else int(size[1](1))
    self.format = format

class OriginalItem(AbstractItem): def init(self, itemId, status, size, format): assert itemId is not None super(OriginalItem, self).init(itemId, status, size, format)

class DerivedItem(AbstractItem): def init(self, status, size, format, originalItem): self.originalItem = originalItem super(DerivedItem, self).init("%s-%sx%s-%s" % (originalItem.id, size0, size1, format),status, size, format)

engine = create_engine('sqlite:///:memory:', encoding='utf-8', echo=False) metadata = MetaData() sm = sessionmaker(bind=engine, autoflush=True, transactional=True)

abstract_item = Table('abstract_item', metadata, Column('id', String(255), primary_key=True), Column('status', String(255), index=True, nullable=False), Column('width', Integer, index=True, nullable=False), Column('height', Integer, index=True, nullable=False), Column('format', String(255), index=True, nullable=False), Column('type', String(255), nullable=False) )

original_item = Table('original_item', metadata, Column('id', String(255), ForeignKey('abstract_item.id'), primary_key=True), )

derived_item = Table('derived_item', metadata, Column('id', String(255), ForeignKey('abstract_item.id'), primary_key=True), Column('original_item_id', String(255), ForeignKey('original_item.id', ondelete="CASCADE")) )

mapper(AbstractItem, abstract_item, polymorphic_on=abstract_item.c.type, polymorphic_identity='ABSTRACT_ITEM') mapper(OriginalItem, original_item, inherits=AbstractItem, polymorphic_identity='ORIGINAL_ITEM') mapper(DerivedItem, derived_item, properties={ 'originalItem' : relation(OriginalItem, primaryjoin=derived_item.c.original_item_id==original_item.c.id) }, inherits=AbstractItem , polymorphic_identity='DERIVED_ITEM')

metadata.create_all(engine)

session = sm() originalItem = OriginalItem('MYID12435', STATUS_OK, (800, 600), IMAGE_FORMAT_JPEG) session.save(originalItem) session.commit() session.close()

session = sm() item = DerivedItem(STATUS_OK, (100, 100), IMAGE_FORMAT_JPEG, originalItem) session.save(item) session.commit() session.close()

session = sm() foundItem = session.query(DerivedItem)\ .filter_by(width=100)\ .filter_by(height=100)\ .filter_by(format=IMAGE_FORMAT_JPEG)\ .join('originalItem')\ .filter_by(id='MYID12435')\ .first()

assert foundItem is not None assert foundItem.status == STATUS_OK assert foundItem.width == 100 assert foundItem.height == 100 assert foundItem.format == IMAGE_FORMAT_JPEG assert foundItem.originalItem.id == 'MYID12435' assert foundItem.originalItem.status == STATUS_OK assert foundItem.originalItem.width == 800 assert foundItem.originalItem.height == 600 assert foundItem.originalItem.format == IMAGE_FORMAT_JPEG

session.commit() session.close()

=>The generated SQL is wrong, and what I get is : sqlalchemy.exceptions.OperationalError: (OperationalError) no such column: original_item.id u'SELECT abstract_item.id AS abstract_item_id, derived_item.id AS derived_item_id, abstract_item.status AS abstract_item_status, abstract_item.width AS abstract_item_width, abstract_item.height AS abstract_item_height, abstract_item.format AS abstract_item_format, abstract_item.type AS abstract_item_type, derived_item.original_item_id AS derived_item_original_item_id \nFROM abstract_item JOIN derived_item ON abstract_item.id = derived_item.id JOIN (abstract_item JOIN original_item ON abstract_item.id = original_item.id) ON derived_item.original_item_id = original_item.id \nWHERE abstract_item.width = ? AND abstract_item.height = ? AND abstract_item.format = ? AND abstract_item.id = ? ORDER BY abstract_item.oid \n LIMIT 1 OFFSET 0' 100, 'JPEG', 'MYID12435'

Comments (6)