I am trying to perform a query in which I from multiple correlated subqueries. My code works with 0.7.10, but not with 0.9.8.
Base = declarative_base() class User(Base): __tablename__ = 'user' id = Column(Integer, primary_key=True) name = Column(String) class Action(Base): __tablename__ = 'action' id = Column(Integer, primary_key=True) user_id = Column(Integer, ForeignKey(User.id)) action = Column(String) date = Column(DateTime) engine = create_engine('sqlite:///:memory:') Base.metadata.create_all(bind=engine) sess = sessionmaker(bind=engine)() sess.add(User(id=1, name='Meelap')) sess.flush() sess.add(Action(id=1, user_id=1, action="login", date=datetime.now())) sess.flush() num_actions = sess.query(func.count()).filter(Action.user_id == User.id). correlate(User).as_scalar() first_action = sess.query(func.min(Action.date)).filter(Action.user_id == User.id).correlate(User).as_scalar() q1 = sess.query(User.name, num_actions, first_action) print sqlalchemy.__version__ print print str(q1) print print q1.all() With 0.7.10, it works as I expect with the User table being dropped from the FROMs of the subqueries. 0.7.10 SELECT "user".name AS user_name, (SELECT count(*) AS count_1 FROM action WHERE action.user_id = "user".id) AS anon_1, (SELECT min(action.date) AS min_1 FROM action WHERE action.user_id = "user".id) AS anon_2 FROM "user" [(u'Meelap', 1, datetime.datetime(2014, 11, 4, 19, 37, 15, 260873))] With 0.9.8, the str(query) is the same with User having been dropped from subqueries, but the error output shows a different subquery being executed that selects from both Action and User. 0.9.8 SELECT "user".name AS user_name, (SELECT count(*) AS count_1 FROM action WHERE action.user_id = "user".id) AS anon_1, (SELECT min(action.date) AS min_1 FROM action WHERE action.user_id = "user".id) AS anon_2 FROM "user" Traceback (most recent call last): File "sql.py", line 39, in <module> print q1.all() File "/home/ubuntu/.virtualenvs/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py" , line 2320, in all return list(self) File "/home/ubuntu/.virtualenvs/env/lib/python2.7/site-packages/sqlalchemy/orm/loading.py" , line 76, in instances labels) for row in fetch] File "/home/ubuntu/.virtualenvs/env/lib/python2.7/site-packages/sqlalchemy/orm/query.py" , line 3523, in proc return row[column] File "/home/ubuntu/.virtualenvs/env/lib/python2.7/site-packages/sqlalchemy/engine/result.py" , line 331, in _key_fallback expression._string_or_unprintable(key)) sqlalchemy.exc.NoSuchColumnError: 'Could not locate column in row for column \'(SELECT count(*) AS count_1 \nFROM action, "user" \nWHERE action.user_id = "user".id)\'' I searched the changelogs but haven't found anything that could explain this and I'd appreciate any help. Thanks! -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.