Hello!
Sorry, I was using old sqlalchemy, after upgrade it works fine.
best regards
Jarek
Dnia 2017-05-12, pią o godzinie 08:54 -0400, mike bayer pisze:
>
> 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 [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
