Calling min or max on a SelectResults instance that has ordering,
generates invalid SQL. The attached example runs on SQLite, but will
not run on Postgresql. Here is the query it generates:
SELECT max(t3.t1_id)
FROM (SELECT t1.id AS t1_id, t1.name AS t1_name, t2.id AS t2_id, t2.id
AS t2_name
FROM t1, t2
WHERE t1.id = t2.t1_id) AS t3 ORDER BY t3.t1_id ASC, t3.t2_name DESC,
t3.t1_name ASC 'SELECT max(t3.t1_id) \nFROM (SELECT t1.id AS t1_id,
t1.name AS t1_name, t2.id AS t2_id, t2.id AS t2_name \nFROM t1, t2
\nWHERE t1.id = t2.t1_id) AS t3 ORDER BY t3.t1_id ASC, t3.t2_name DESC,
t3.t1_name ASC' {}
and here is the error:
raise exceptions.SQLError(statement, parameters, e)
sqlalchemy.exceptions.SQLError: (ProgrammingError) ERROR: column
"t3.t1_id" must appear in the GROUP BY clause or be used in an aggregate
function
Sorry that the test case is more complicated than necessary. I was
duplicating my code trying to find the error's cause.
Randall
"""Delete fails after access to a lazy attribute.
"""
from sqlalchemy import *
from sqlalchemy.ext.selectresults import SelectResults
metadata = DynamicMetaData(name="test")
t1 = Table('t1', metadata,
Column('id', Integer, primary_key=True),
Column('name', String)
)
t2 = Table('t2', metadata,
Column('id', Integer, primary_key=True),
Column('t1_id', Integer, ForeignKey(t1.c.id)),
Column('name', String)
)
t3 = select([t1.c.id.label('t1_id'), t1.c.name.label('t1_name'),
t2.c.id.label('t2_id'), t2.c.id.label('t2_name'),
],
t1.c.id == t2.c.t1_id
).alias('t3')
class T1(object):
pass
class T2(object):
pass
class T3(object):
pass
mapper(T1, t1,
)
mapper(T2, t2,
properties={'t1':relation(T1, backref=backref('t2s', private=True))}
)
mapper(T3, t3)
engine = create_engine("sqlite:///:memory:")
engine = create_engine("postgres:///test")
engine.echo = True
metadata.connect(engine)
metadata.create_all()
# Populate tables.
session = create_session(bind_to=engine)
for i in range(10):
t1obj = T1()
t1obj.name = 'test'
session.save(t1obj)
for i in range(5):
t2obj = T2()
t2obj.name = 'test'
t1obj.t2s.append(t2obj)
session.flush()
session.close()
# SelectResults
session = create_session(bind_to=engine)
query = session.query(T3)
sr = SelectResults(query)
ordering = [asc(T3.c.t1_id), desc(T3.c.t2_id), asc(T3.c.t1_name)]
sr = sr.order_by(ordering)
print sr.max(T3.c.t1_id)
session.flush()
session.close()
-------------------------------------------------------------------------
Take Surveys. Earn Cash. Influence the Future of IT
Join SourceForge.net's Techsay panel and you'll get the chance to share your
opinions on IT & business topics through brief surveys -- and earn cash
http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
