the issue is quite simple as can be seen in this demonstration:
from sqlalchemy import *
# two columns. Nothing up my sleeve !
c1 = Column('c1', Integer)
c2 = Column('c2', Integer)
# put one of them into a Select.
# generate _from_objects collection of c1 too early
s = select([c1])
t = Table('t', MetaData(), c1, c2)
# c1 has the wrong _from_objects
assert c1._from_objects == []
# c2 has the correct one
assert c2._from_objects == [t]
# see it here
assert str(select([c1])) == "SELECT t.c1"
assert str(select([c2])) == "SELECT t.c2 \nFROM t"
what to do about it, unsure. Removing the caching from _from_objects is the
immediate fix.
However, the original select() is still wrong. _from_objects gave it the wrong
data, period. How to guard against this issue, a potentially expensive reorg
of Select internals. As well as future issues of this sort, using Column
objects which generates cached state, then mutating the Column.
On Aug 18, 2011, at 3:12 PM, brianhawthorne wrote:
> 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.
>
--
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.
