- changed milestone to 0.7.8
Malformed query generated by sqlalchemy when using a union with a CTE
Issue #2490
resolved
I'm trying to use SQLAlchemy to generate a query that involves both CTEs and a union. Unfortunately whenever I apply a union the generated SQL does not include the defined CTE.
Here's the SQL including the WITH, without a UNION:
(Pdb) import sqlalchemy as sa
(Pdb) print str(sa.select([sa.select([sa.sql.null().label('a1')](sa.select([sa.sql.null().label('a1'))).cte('bb')]))
WITH bb AS
(SELECT NULL AS a1)
SELECT bb.a1
FROM bb
Now the same query but with a call to union:
(Pdb) print str(sa.select([sa.select([sa.sql.null().label('a1')](sa.select([sa.sql.null().label('a1'))).cte('bb')]).union_all(sa.select([sa.sql.null().label('b1')](sa.sql.null().label('b1')))))
SELECT bb.a1
FROM bb UNION ALL SELECT NULL AS b1
I'd expect the query with the union call to still include the WITH, but it does not.
Here is a patch that, while needs refactoring, generates the CTE as expected:
--- sqlalchemy.orig/sql/compiler.py 2012-05-20 17:39:41.000000000 +0000
+++ sqlalchemy/sql/compiler.py 2012-05-20 17:39:00.000000000 +0000
@@ -562,6 +562,15 @@
text += (cs._limit is not None or cs._offset is not None) and \
self.limit_clause(cs) or ""
+ if self.ctes and \
+ compound_index==1 and not entry:
+ cte_text = self.get_cte_preamble(self.ctes_recursive) + " "
+ cte_text += ", \n".join(
+ [for txt in self.ctes.values()](txt)
+ )
+ cte_text += "\n "
+ text = cte_text + text
+
self.stack.pop(-1)
if asfrom and parens:
return "(" + text + ")"
Comments (3)
-
repo owner -
repo owner - changed status to resolved
OK moved it into a separate method to avoid some of the repetition. thanks for the fix !
339f3abdeb63bba68492da2aac903a98c32ca421 1ac57f0b52e3e89097129931d46ebbbb39ee7927
-
repo owner - removed milestone
Removing milestone: 0.7.8 (automated comment)
- Log in to comment
its possible the patch will be used as is....adding to 0.7.8 for now but might get pushed as 0.7.8 is due for release.