Description
Migrated issue, originally created by Anonymous
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'
Metadata
Metadata
Assignees
Labels
Type
Projects
Relationships
Development
Participants
Activity
sqlalchemy-bot commentedon Jun 22, 2008
Michael Bayer (@zzzeek) wrote:
the join is self referential between abstract_item->abstract_item and must be aliased:
Upgrade to 0.4.6 and your original statement is disallowed, since later versions of 0.4 detect the self-referential join for you. But works in 0.4.2p3 as well (but please upgrade to 0.4.6 and only report bugs against the most recently released versions of SQLA).
sqlalchemy-bot commentedon Jun 22, 2008
Changes by Michael Bayer (@zzzeek):
sqlalchemy-bot commentedon Jun 22, 2008
Anonymous wrote:
OK, so I upgraded to 0.4.6, and I still have an issue with the SQL that is generated when using lazy=False :
mapper(DerivedItem, derived_item,
properties={
'originalItem' : relation(OriginalItem, primaryjoin=derived_item.c.original_item_id==original_item.c.id,lazy=False, join_depth=2)
}, inherits=AbstractItem , polymorphic_identity='DERIVED_ITEM')
(same with join_depth=1 and join_depth=3
What I get is :
sqlalchemy.exceptions.OperationalError: (OperationalError) no such column: anon_2.abstract_item_id u'SELECT anon_1.derived_item_original_item_id AS anon_1_derived_item_original_item_id, anon_1.abstract_item_id AS anon_1_abstract_item_id, anon_1.derived_item_id AS anon_1_derived_item_id, anon_1.abstract_item_status AS anon_1_abstract_item_status, anon_1.abstract_item_width AS anon_1_abstract_item_width, anon_1.abstract_item_height AS anon_1_abstract_item_height, anon_1.abstract_item_format AS anon_1_abstract_item_format, anon_1.abstract_item_type AS anon_1_abstract_item_type, anon_2.abstract_item_id AS anon_2_abstract_item_id, anon_2.original_item_id AS anon_2_original_item_id, anon_2.abstract_item_status AS anon_2_abstract_item_status, anon_2.abstract_item_width AS anon_2_abstract_item_width, anon_2.abstract_item_height AS anon_2_abstract_item_height, anon_2.abstract_item_format AS anon_2_abstract_item_format, anon_2.abstract_item_type AS anon_2_abstract_item_type \nFROM (SELECT derived_item.original_item_id AS derived_item_original_item_id, 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, abstract_item.oid AS abstract_item_oid \nFROM abstract_item JOIN derived_item ON abstract_item.id = derived_item.id JOIN (SELECT abstract_item.id AS abstract_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, original_item.id AS original_item_id \nFROM abstract_item JOIN original_item ON abstract_item.id = original_item.id) AS anon_3 ON derived_item.original_item_id = anon_3.original_item_id \nWHERE abstract_item.width = ? AND abstract_item.height = ? AND abstract_item.format = ? AND anon_3.abstract_item_id = ? ORDER BY abstract_item.oid \n LIMIT 1 OFFSET 0) AS anon_1 LEFT OUTER JOIN (SELECT anon_1.derived_item_original_item_id AS anon_1_derived_item_original_item_id, anon_1.abstract_item_id AS anon_1_abstract_item_id, anon_1.derived_item_id AS anon_1_derived_item_id, anon_1.abstract_item_status AS anon_1_abstract_item_status, anon_1.abstract_item_width AS anon_1_abstract_item_width, anon_1.abstract_item_height AS anon_1_abstract_item_height, anon_1.abstract_item_format AS anon_1_abstract_item_format, anon_1.abstract_item_type AS anon_1_abstract_item_type \nFROM (SELECT derived_item.original_item_id AS derived_item_original_item_id, 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, abstract_item.oid AS abstract_item_oid \nFROM abstract_item JOIN derived_item ON abstract_item.id = derived_item.id JOIN (SELECT abstract_item.id AS abstract_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, original_item.id AS original_item_id \nFROM abstract_item JOIN original_item ON abstract_item.id = original_item.id) AS anon_3 ON derived_item.original_item_id = anon_3.original_item_id \nWHERE abstract_item.width = ? AND abstract_item.height = ? AND abstract_item.format = ? AND anon_3.abstract_item_id = ? ORDER BY abstract_item.oid \n LIMIT 1 OFFSET 0) AS anon_1) AS anon_2 ON anon_1.derived_item_original_item_id = anon_2.original_item_id ORDER BY anon_1.oid, anon_2.oid' 100, 'JPEG', 'MYID12435', 100, 100, 'JPEG', 'MYID12435'
However, SA 0.5 beta does not complain, but does not perform the eager loading either, which leads to the following exception:
sqlalchemy/orm/mapper.py", line 1580, in _load_scalar_attributes
raise sa_exc.UnboundExecutionError("Instance %s is not bound to a Session; attribute refresh operation cannot proceed" % (state_str(state)))
sqlalchemy.exc.UnboundExecutionError: Instance OriginalItem@0xbb4450 is not bound to a Session; attribute refresh operation cannot proceed
I attach the new bug.py
sqlalchemy-bot commentedon Jun 22, 2008
Changes by Anonymous:
sqlalchemy-bot commentedon Jun 22, 2008
Anonymous wrote:
bug.py with lazy loading turned off
sqlalchemy-bot commentedon Jun 22, 2008
Changes by Anonymous:
sqlalchemy-bot commentedon Jun 22, 2008
Michael Bayer (@zzzeek) wrote:
version 0.5 had a slight issue with both tests regarding query.join() from a joined-table mapper to an aliased joined-table mapper on the same base table, this is fixed in 3e8b095. The eager loading you're looking for is supported in version 0.5.
sqlalchemy-bot commentedon Jun 22, 2008
Changes by Michael Bayer (@zzzeek):
sqlalchemy-bot commentedon Jun 22, 2008
Michael Bayer (@zzzeek) wrote:
Replying to guest:
this error is because the
Sessionhas expired its contents after thecommit(). You have to leave the session opened in order for the objects committed to be used, since they need to refresh their contents. Read over http://www.sqlalchemy.org/docs/05/session.html#unitofwork_using_committing for info on this.