- 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.
[ticket:2783]

4 files changed, 55 insertions, 12 deletions

diff --git a/doc/build/changelog/changelog_07.rst b/doc/build/changelog/changelog_07.rst
index b3b37861e..59276c73a 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/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 9dc56d1f0..2e6301f15 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -811,12 +811,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)
@@ -839,9 +844,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 c90a3dcb0..2868af221 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -3764,10 +3764,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)
 
@@ -3776,8 +3776,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 59b347ccd..71663ca78 100644
--- a/test/sql/test_cte.py
+++ b/test/sql/test_cte.py
@@ -350,3 +350,32 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL):
             checkpositional=('x', 'y'),
             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"'
+        )