[Sqlalchemy-tickets] Issue #3014: Documentation for recursive CTE query - column order in second select needs to be same order as first select. (zzzeek/sqlalchemy)

[Stephen B. <iss...@bi...>]

New issue 3014: Documentation for recursive CTE query - column order in second select needs to be same order as first select.
https://bitbucket.org/zzzeek/sqlalchemy/issue/3014/documentation-for-recursive-cte-query

Stephen Bridgett:

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, 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".

Thank you again for all the great work developing SQLalchemy.