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.