On Jul 10, 2012, at 2:04 AM, Russell Warren wrote: > > As per the comments in the code, the alias is absolutely required (it gets > mangled without the alias), but is seemingly pointless in the resulting SQL.
Referring to PG's docs: http://www.postgresql.org/docs/8.4/static/queries-with.html when I wrote the CTE functionality, I designed it to suit the "included_parts" example, where you can see there is an alias to included_parts. This alias fits naturally into SQLAlchemy's system of aligning object identity with lexical identity, that is, to say "x == 5", you need an "x" object that is unique in the query based on it's Python identity. But you're right, I didn't study the previous example in that doc, which refers to "t" without any alias. This usage is not nearly as natural, because "t" refers to the UNION ALL as a whole, including the two select statements, one of which was called "t" as well. So referring to both the inner and outer "t" at the same time doesn't work with the SQLA's usual approach. So some modifications to CTE are made in r079123b04dc6 (0.8) / ra742d1526e86 (0.7) such that CTEs are now rendered based on name-based logic only. A given CTE name will only be rendered as a full statement once, and precedence rules now ensure that the "outermost" CTE is the one that's rendered in all cases. Two entirely non-related CTEs with the same name now generates a CompileError. With this change, your original intuition: import sqlalchemy as sa #set up the non-recursive part of the query (sort of?)... cte = sa.select( [sa.literal(0).label("x"), ] ).cte(name = "cte", recursive = True) #bring in the recursive part (sort of?)... cte = cte.union_all( sa.select([cte.c.x + 1, ]).\ where(cte.c.x < 10) ) #select from the resulting CTE... statement = sa.select([cte.c.x, ]) print statement works as you expect: WITH RECURSIVE cte(x) AS (SELECT :param_1 AS x UNION ALL SELECT cte.x + :x_1 AS anon_1 FROM cte WHERE cte.x < :x_2) SELECT cte.x FROM cte the original output you were getting isn't all that confusing (any more than the whole CTE thing is in the first place) - it just rendered cte(x) twice, one for each version used in the query. -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
