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:
#!/usr/bin/env python3
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column('id', Integer(), primary_key=True)
_elem = Column('_elem',Integer(), ForeignKey('p.id'))
value = Column(String(32))
class I(Base):
__tablename__ = 'i'
id = Column(Integer, primary_key=True)
_elem = Column('_elem',Integer(), ForeignKey('p.id'))
value = Column(Integer())
elem = relationship('P', backref='items')
class P(Base):
__tablename__ = "p"
id = Column(Integer(), primary_key=True)
attrs = relationship("A", lazy=False, backref="elem")#,
e = create_engine("postgresql://localhost/test", echo=True)
Base.metadata.drop_all(e)
Base.metadata.create_all(e)
sess = Session(e)
sumq = func.sum(I.value).label('sum1')
# this one produces valid query
sess.query(P,sumq).outerjoin(P.items).group_by(P).order_by(sumq)[:10]
#but calling .all() raises exception
sess.query(P,sumq).outerjoin(P.items).group_by(P).order_by(sumq).all()
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,...
Where query is:
'SELECT p.id AS p_id, sum(i.value) AS sum1, a_1.id AS a_1_id, a_1._elem AS
a_1__elem, a_1.value AS a_1_value \nFROM p LEFT OUTER JOIN i ON p.id =
i._elem LEFT OUTER JOIN a AS a_1 ON p.id = a_1._elem GROUP BY p.id ORDER BY
sum1' {}
And in case of call with limits it's built properly:
'FROM (SELECT p.id AS p_id, sum(i.value) AS sum1
FROM p LEFT OUTER JOIN i ON p.id = i._elem GROUP BY p.id ORDER BY sum1
LIMIT %(param_1)s) AS anon_1 LEFT OUTER JOIN a AS a_1 ON anon_1.p_id =
a_1._elem ORDER BY anon_1.sum1'
So is it bug or some limitation? Any idea how to workaround it?
regards,
Robert Tasarz
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
