On Wed, 22 Feb 2017 11:15:05 -0500
mike bayer <mike...@zzzcomputing.com> wrote:
>
>
> On 02/22/2017 10:17 AM, Michael Williamson wrote:
> > Using CTEs directly inside union() (or similar functions such as
> > intersect()) causes an error:
> >
> >
> > query_1 = s.query(Record.id).cte()
> > query_2 = s.query(Record.id).cte()
> >
> > select_from = union(query_1, query_2)
>
> what does the above intend to mean? I don't know what SQL one would
> expect from that. A CTE is specifically a SELECT that goes on top of
> another SELECT as a sort of "aliased" subquery. a UNION doesn't
> make any sense on the "outside" of that unless you can show me.
>
> I can see this instead:
>
> q1 = s.query(Record.id)
> q2 = s.query(Record.id)
>
> select_from = union(q1, q2).cte()
>
> but that's not what the above is saying.
My expectation was as in the working code I wrote: that is, select all
of the rows from the CTE.
select_from = union(
select([query_1.c.id]).select_from(query_1),
select([query_2.c.id]).select_from(query_2),
)
If the answer is "you're doing something weird, don't do that", that's
fine, I just thought I'd point out a situation where the error that was
generated didn't make it immediately clear where I'd make a mistake.
>
>
>
>
>
> > print(s.query(func.count(1)).select_from(select_from).all())
> >
> >
> > sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError)
> > syntax error at or near "SELECT" LINE 2: SELECT record.id AS id
> > ^
> > [SQL: 'WITH anon_2 AS \nSELECT record.id AS id \nFROM record,
> > \nanon_3 AS \nSELECT record.id AS id \nFROM record\n SELECT
> > count(%(count_2)s) AS count_1 \nFROM (anon_2 UNION anon_3) AS
> > anon_1'] [parameters: {'count_2': 1}]
> >
> > The way I got things working was to wrap the CTE in a SELECT rather
> > than using it directly:
> >
> > select_from = union(
> > select([query_1.c.id]).select_from(query_1),
> > select([query_2.c.id]).select_from(query_2),
> > )
> > print(s.query(func.count(1)).select_from(select_from).all())
> >
> > However, the original error didn't make it especially obvious to me
> > what was going on. Perhaps either the API could be changed to allow
> > using the CTE directly, or a clearer error could be emitted? Or am I
> > just thinking about this all wrong?
> >
> > I also got an error when using subquery() instead of cte(), which
> > was fixed in the same way -- presumably this is the same issue?
> >
> > Thanks
> >
> > Michael
> >
>
--
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.
