On 05/12/2017 04:55 AM, Jarek wrote:
Hello!
It looks that SQLAlchemy doesn't properly handle union with limits in
the following scenario:
res1 = Session.query( Messages ).order_by( Messages.ts ).limit(100)
res2 = Session.query( Messages1 ).order_by( Messages1.ts ).limit(100)
res3 = res1.union_all( res2 )
SQLAlchemy creates the following final query:
SELECT <fields> FROM Messages order by ts limit 100
UNION ALL SELECT <fields> FROM Messages1 order by ts limit 100
that's not at all what I get and that's not how Query.union_all() does
it, it wraps the inner things inside a subquery, works fine. MCVE:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
Base.metadata.create_all(e)
s = Session(e)
A1 = aliased(A)
res1 = s.query(A).order_by(A.id).limit(100)
res2 = s.query(A1).order_by(A1.id).limit(100)
res3 = res1.union_all(res2)
res3.all()
query:
SELECT anon_1.a_id AS anon_1_a_id
FROM ((SELECT a.id AS a_id
FROM a ORDER BY a.id
LIMIT %(param_1)s) UNION ALL (SELECT a_1.id AS a_1_id
FROM a AS a_1 ORDER BY a_1.id
LIMIT %(param_2)s)) AS anon_1
please provide accurate details, thanks
Which fails with:
ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near
"UNION"
To fix this, both queries should be enclosed in parenthesis:
(SELECT <fields> FROM Messages order by ts limit 100 )
UNION ALL ( SELECT <fields> FROM Messages1 order by ts limit 100 )
Best regards
Jarek
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
