diff options
| -rw-r--r-- | doc/build/changelog/changelog_07.rst | 8 | ||||
| -rw-r--r-- | doc/build/changelog/changelog_08.rst | 8 | ||||
| -rw-r--r-- | doc/build/changelog/changelog_09.rst | 8 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 22 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/expression.py | 8 | ||||
| -rw-r--r-- | test/sql/test_cte.py | 29 |
6 files changed, 71 insertions, 12 deletions
diff --git a/doc/build/changelog/changelog_07.rst b/doc/build/changelog/changelog_07.rst index 99702a2f0..b9601f23f 100644 --- a/doc/build/changelog/changelog_07.rst +++ b/doc/build/changelog/changelog_07.rst @@ -7,6 +7,14 @@ :version: 0.7.11 .. change:: + :tags: sql, bug, cte + :tickets: 2783 + + Fixed bug in common table expression system where if the CTE were + used only as an ``alias()`` construct, it would not render using the + WITH keyword. Also in 0.8.3, 0.7.11. + + .. change:: :tags: bug, sql :tickets: 2784 diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst index a9c07649e..4bac27445 100644 --- a/doc/build/changelog/changelog_08.rst +++ b/doc/build/changelog/changelog_08.rst @@ -7,6 +7,14 @@ :version: 0.8.3 .. change:: + :tags: sql, bug, cte + :tickets: 2783 + + Fixed bug in common table expression system where if the CTE were + used only as an ``alias()`` construct, it would not render using the + WITH keyword. Also in 0.7.11. + + .. change:: :tags: feature, examples Improved the examples in ``examples/generic_associations``, including diff --git a/doc/build/changelog/changelog_09.rst b/doc/build/changelog/changelog_09.rst index 5655ae78b..736bcc3c6 100644 --- a/doc/build/changelog/changelog_09.rst +++ b/doc/build/changelog/changelog_09.rst @@ -7,6 +7,14 @@ :version: 0.9.0 .. change:: + :tags: sql, bug, cte + :tickets: 2783 + + Fixed bug in common table expression system where if the CTE were + used only as an ``alias()`` construct, it would not render using the + WITH keyword. Also in 0.8.3, 0.7.11. + + .. change:: :tags: feature, general, py3k :tickets: 2161 diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 72bebca3d..daed7c50f 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -961,12 +961,17 @@ class SQLCompiler(engine.Compiled): self.ctes_by_name[cte_name] = cte - if cte.cte_alias: - if isinstance(cte.cte_alias, sql._truncated_label): - cte_alias = self._truncated_identifier("alias", cte.cte_alias) - else: - cte_alias = cte.cte_alias - if not cte.cte_alias and cte not in self.ctes: + if cte._cte_alias is not None: + orig_cte = cte._cte_alias + if orig_cte not in self.ctes: + self.visit_cte(orig_cte) + cte_alias_name = cte._cte_alias.name + if isinstance(cte_alias_name, sql._truncated_label): + cte_alias_name = self._truncated_identifier("alias", cte_alias_name) + else: + orig_cte = cte + cte_alias_name = None + if not cte_alias_name and cte not in self.ctes: if cte.recursive: self.ctes_recursive = True text = self.preparer.format_alias(cte, cte_name) @@ -989,9 +994,10 @@ class SQLCompiler(engine.Compiled): self, asfrom=True, **kwargs ) self.ctes[cte] = text + if asfrom: - if cte.cte_alias: - text = self.preparer.format_alias(cte, cte_alias) + if cte_alias_name: + text = self.preparer.format_alias(cte, cte_alias_name) text += " AS " + cte_name else: return self.preparer.format_alias(cte, cte_name) diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 08ef20a89..b00a7399a 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -4235,10 +4235,10 @@ class CTE(Alias): def __init__(self, selectable, name=None, recursive=False, - cte_alias=False, + _cte_alias=None, _restates=frozenset()): self.recursive = recursive - self.cte_alias = cte_alias + self._cte_alias = _cte_alias self._restates = _restates super(CTE, self).__init__(selectable, name=name) @@ -4247,8 +4247,8 @@ class CTE(Alias): self.original, name=name, recursive=self.recursive, - cte_alias=self.name - ) + _cte_alias=self, + ) def union(self, other): return CTE( diff --git a/test/sql/test_cte.py b/test/sql/test_cte.py index 28756873f..5368b9891 100644 --- a/test/sql/test_cte.py +++ b/test/sql/test_cte.py @@ -351,3 +351,32 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL): dialect=dialect ) + + def test_all_aliases(self): + orders = table('order', column('order')) + s = select([orders.c.order]).cte("regional_sales") + + r1 = s.alias() + r2 = s.alias() + + s2 = select([r1, r2]).where(r1.c.order > r2.c.order) + + self.assert_compile( + s2, + 'WITH regional_sales AS (SELECT "order"."order" ' + 'AS "order" FROM "order") ' + 'SELECT anon_1."order", anon_2."order" ' + 'FROM regional_sales AS anon_1, ' + 'regional_sales AS anon_2 WHERE anon_1."order" > anon_2."order"' + ) + + s3 = select([orders]).select_from(orders.join(r1, r1.c.order == orders.c.order)) + + self.assert_compile( + s3, + 'WITH regional_sales AS ' + '(SELECT "order"."order" AS "order" ' + 'FROM "order")' + ' SELECT "order"."order" ' + 'FROM "order" JOIN regional_sales AS anon_1 ON anon_1."order" = "order"."order"' + )
\ No newline at end of file |