On Jan 31, 2014, at 2:49 PM, Robert Tasarz <[email protected]> wrote:
> Hi, > > I'm almost sure this is a bug, but maybe I'm missing something obvious. > I've tested it with Python 3.3, SQLAlchemy 0.9.1, PostgreSQL 9.3 and reduced > the issue to the following code: > > > > Here's most relevant part of the exception: > Traceback (most recent call last): > File > "/srv/websites/sika/local/lib/python3.3/dist-packages/sqlalchemy/engine/base.py", > line 867, in _execute_context > context) > File > "/srv/websites/sika/local/lib/python3.3/dist-packages/sqlalchemy/engine/default.py", > line 388, in do_execute > cursor.execute(statement, parameters) > psycopg2.ProgrammingError: column "a_1.id" must appear in the GROUP BY clause > or be used in an aggregate function > LINE 1: SELECT p.id AS p_id, sum(i.value) AS sum1, a_1.id AS a_1_id,… When joined eager loading is used, if LIMIT is also applied as you have in that [:10], SQLAlchemy wraps the query to be limited inside of a subquery, so that the LEFT OUTER JOIN for the eager loading can safely load all related rows without being subject to the LIMIT. This wrapping doesn’t occur when GROUP BY is used - GROUP BY is not usually used in conjunction with loads of a full entity, as this is typically inefficient - it is usually used with a query that is only loading individual columns, and then if the query overall is to return entities, a JOIN against the GROUP BY as a subquery is used (see http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx for a description of this). So in that sense, you’re getting that subquery behavior out of the box due to the LIMIT, but with plain all(), this isn’t applied. you can call from_self() to produce the self-wrapping effect: sess.query(P,sumq).outerjoin(P.items).group_by(P).order_by(sumq).from_self().all() perhaps the presence of GROUP BY should be added to the list of things that cause the automatic wrapping with joined eager loading to occur, though the current behavior has been this way for nearly 8 years and nobody’s asked for it before.
signature.asc
Description: Message signed with OpenPGP using GPGMail
