- edited description
Documentation for recursive CTE query - column order in second select needs to be same order as first select.
Firstly thank you for all the great work developing SQLalchemy.
I was trying to create a recursive CTE query using the "sub_part" example given on:
(1) http://docs.sqlalchemy.org/en/rel_0_9/core/selectable.html and on: (2) http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html
both in sections titled: "cte(name=None, recursive=False)"
however the query hangs, - when tried on SQLite 3 and on Postgres 8.4. (I am using SQLalchemy version 0.9.3)
when I compared the query with the SQL query given on the Postgresql page: http://www.postgresql.org/docs/8.4/static/queries-with.html (half way down that page)
the postgres SQL always has the columns in each SELECT in the order: "sub_part, part, quantity" (in the first select) and: "p.sub_part, p.part, p.quantity" (in the second select)
However the SQLalchemy ORM query on page: http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html has: Part.sub_part, Part.part, Part.quantity (for the first select), but then in the second select the order is changed to: parts_alias.part, parts_alias.sub_part, parts_alias.quantity
If I change the order of columns in this second select to:
session.query(
parts_alias.sub_part,
parts_alias.part,
parts_alias.quantity).\
(ie. putting "sub_part" first to match the order in the first select)
Then the query works well. It seems to be important that the columns are in same order for the recursive cte union to work.
This applies:
(1) to the ORM query page: http://docs.sqlalchemy.org/en/rel_0_9/orm/query.html section titled: cte(name=None, recursive=False)
(2) and to the CORE selectable page: http://docs.sqlalchemy.org/en/rel_0_9/core/selectable.html section titled: cte(name=None, recursive=False)
Could these two pages be updated to correct this?
Also on that CORE selectable page, I think the select_from function:
"select_from(included_parts.join(parts, included_parts.c.part==parts.c.part))"
at the end of that CTE "statement=" recursive query isn't really needed, as the final query doesn't select from "parts", although it could be useful if wish to select additional columns from "part", so is still helpful.
Thank you again for all the great work developing SQLalchemy.
Comments (5)
-
reporter -
reporter - edited description
-
repo owner - changed status to resolved
- reverse order of columns in sample CTEs as this is a UNION and the cols need to line up
- alter this in the unit tests as well as these queries were just copied from the tests
- remove the included_parts.join(parts) from the core CTE doc (also just copied from the
test, where we want to make sure joins don't get screwed up with the CTE) as it doesn't
contribute to the query itself
fixes
#3014
→ <<cset b23bb79581ff>>
-
repo owner - reverse order of columns in sample CTEs as this is a UNION and the cols need to line up
- alter this in the unit tests as well as these queries were just copied from the tests
- remove the included_parts.join(parts) from the core CTE doc (also just copied from the
test, where we want to make sure joins don't get screwed up with the CTE) as it doesn't
contribute to the query itself
fixes
#3014
→ <<cset df7fe021a945>>
-
reporter That's great. Thank you very much for fixing this so quickly.
- Log in to comment