Hello,
I have found a case where instantiating a select statement during the
declaration of the parent class in a one-to-many pair can cause a
downstream failure to generate well formed sql when performing a query
with a subqueryload option. I've boiled down a minimal example
(pasted below) which will produce the bug.
Note that the select must be called *during* the declaration (eg when
defining a custom column property) of the parent class and must refer
to a column of that class. Removing the id reference or moving the
select anywhere outside the scope of the parent class declaration will
make the bug disappear.
Cheers,
Brian Hawthorne
Amyris, Inc.
#----------------------------------------------------------------------------------
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Parent(Base):
__tablename__ = 'parent'
id = Column(Integer, primary_key=True)
select([id])
class Child(Base):
__tablename__ = 'child'
id = Column(Integer, primary_key=True)
parent_id = Column(Integer, ForeignKey('parent.id'))
parent = relationship(Parent, backref='children')
engine = create_engine('sqlite://', echo=True)
Base.metadata.create_all(engine)
session = sessionmaker(engine)()
session.add(Parent())
session.commit()
# Malformed SQL!
session.query(Parent).options(subqueryload('children')).all()
#----------------------------------------------------------------------------------
Executing the above produces the following traceback:
Traceback (most recent call last):
File "test.py", line 25, in <module>
session.query(Parent).options(subqueryload('children')).all()
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/query.py", line 1729, in all
return list(self)
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/query.py", line 1960, in instances
rows = [process[0](row, None) for row in fetch]
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/mapper.py", line 2481, in _instance
eager_populators
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/mapper.py", line 2664, in _populators
self, row, adapter)):
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/interfaces.py", line 326, in
create_row_processor
reduced_path, mapper, row, adapter)
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/strategies.py", line 890, in
create_row_processor
lambda x:x[1:]
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/query.py", line 1839, in __iter__
return self._execute_and_instances(context)
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/orm/query.py", line 1854, in
_execute_and_instances
result = conn.execute(querycontext.statement, self._params)
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/engine/base.py", line 1399, in execute
params)
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/engine/base.py", line 1532, in
_execute_clauseelement
compiled_sql, distilled_params
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/engine/base.py", line 1640, in
_execute_context
context)
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/engine/base.py", line 1633, in
_execute_context
context)
File "/usr/local/lib/python2.7/dist-packages/SQLAlchemy-0.7.2-py2.7-
linux-i686.egg/sqlalchemy/engine/default.py", line 325, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (OperationalError) no such column:
parent.id u'SELECT child.id AS child_id, child.parent_id AS
child_parent_id, anon_1.parent_id AS anon_1_parent_id \nFROM (SELECT
parent.id AS parent_id) AS anon_1 JOIN child ON parent.id =
child.parent_id ORDER BY anon_1.parent_id' ()
--
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
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
