this issue is fixed in the 0.9 and 1.0 branches, https://bitbucket.org/zzzeek/sqlalchemy/issue/3241/multiple-anonymous-expressions-in-query <https://bitbucket.org/zzzeek/sqlalchemy/issue/3241/multiple-anonymous-expressions-in-query>.
> On Nov 5, 2014, at 3:33 AM, Michael Bayer <mike...@zzzcomputing.com> wrote: > >> >> On Nov 4, 2014, at 10:47 PM, Meelap Shah <meelaps...@gmail.com >> <mailto:meelaps...@gmail.com>> wrote: >> >> >> 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. > > the error output here is just showing the “column” in isolation of the > surrounding context so you see “user” coming out in the FROM clause, but you > can see this is not in the ultimate query. > > SQLAlchemy here is not behaving well due to the presence of two > anonymously-named columns, I will file a bug, however the solution is to give > these expressions labels: > > num_actions = sess.query(func.count()).filter(Action.user_id == > User.id).correlate(User).label('a') > first_action = sess.query(func.min(Action.date)).filter(Action.user_id == > User.id).correlate(User).label('b') > q1 = sess.query(User.name, num_actions, first_action) > > > >> >> 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 >> <mailto:sqlalchemy+unsubscr...@googlegroups.com>. >> To post to this group, send email to sqlalchemy@googlegroups.com >> <mailto:sqlalchemy@googlegroups.com>. >> Visit this group at http://groups.google.com/group/sqlalchemy >> <http://groups.google.com/group/sqlalchemy>. >> For more options, visit https://groups.google.com/d/optout >> <https://groups.google.com/d/optout>. > > > -- > 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 > <mailto:sqlalchemy+unsubscr...@googlegroups.com>. > To post to this group, send email to sqlalchemy@googlegroups.com > <mailto:sqlalchemy@googlegroups.com>. > Visit this group at http://groups.google.com/group/sqlalchemy > <http://groups.google.com/group/sqlalchemy>. > For more options, visit https://groups.google.com/d/optout > <https://groups.google.com/d/optout>. -- 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.