On 02/22/2017 11:25 AM, Michael Williamson wrote:
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.
Well, union(cte, cte) is just not a covered use case. I guess you
expected it to implicitly generate a SELECT against each CTE first, so
yeah, that's not what union(x, y) implies; the internal objects are
taken as is.
An example is to do the same thing just with a straight Table object and
you can see putting those into UNION produces SQL that isn't valid:
>>> from sqlalchemy import table, union, column
>>> t1 = table('t', column('x'))
>>> t2 = table('s', column('y'))
>>> u = union(t1, t2)
>>> print u
UNION
>>> print u.select()
SELECT x
FROM (t UNION s)
then to make it "right":
>>> u = union(t1.select(), t2.select())
>>> print u
SELECT t.x
FROM t UNION SELECT s.y
FROM s
for this kind of case SQLAlchemy tends to try to render what you're
asking for literally. the approach of having it look for failures
ahead of time might be appropriate however at the same time it's kind of
nice (and a little more performant) that it is open ended and doesn't
assume that what you're asking isn't somehow possible, perhaps the given
FROM has event attached to it that change its behavior or something.
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.
