CTE cloning overwrites alias

[sqlalchemy-bot]

Migrated issue, originally created by Michael Bayer (@zzzeek)

similar to #4204, never tested:

--- a/test/sql/test_cte.py
+++ b/test/sql/test_cte.py
@@ -5,6 +5,8 @@ from sqlalchemy.dialects import mssql
 from sqlalchemy.engine import default
 from sqlalchemy.exc import CompileError
 from sqlalchemy.sql.elements import quoted_name
+from sqlalchemy.sql.visitors import cloned_traverse
+
 
 class CTETest(fixtures.TestBase, AssertsCompiledSQL):
 
@@ -436,6 +438,17 @@ class CTETest(fixtures.TestBase, AssertsCompiledSQL):
                             "FROM regional_sales AS rs WHERE "
                             "rs.amount < :amount_2")
 
+        cloned = cloned_traverse(s, {}, {})
+        self.assert_compile(cloned,
+                            "WITH regional_sales AS "
+                            "(SELECT orders.region AS region, "
+                            "orders.amount AS amount FROM orders) "
+                            "SELECT rs.region FROM regional_sales AS rs "
+                            "WHERE rs.amount > :amount_1 "
+                            "UNION ALL SELECT rs.region "
+                            "FROM regional_sales AS rs WHERE "
+                            "rs.amount < :amount_2")
+
     def test_reserved_quote(self):
         orders = table('orders',
                        column('order'),

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Clone _cte_alias instead of assigning "self"

Fixed bug in :class:.CTE construct along the same lines as that of
🎫4204 where a :class:.CTE that was aliased would not copy itself
correctly during a "clone" operation as is frequent within the ORM as well
as when using the :meth:.ClauseElement.params method.

Change-Id: Id68d72dd244dedfc7bd6116c9a5123c51a55ea20
Fixes: #4210
(cherry picked from commit 9a13f00)

→ 10c3d02

[sqlalchemy-bot]

Changes by Michael Bayer (@zzzeek):

• changed status to closed

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

Clone _cte_alias instead of assigning "self"

Fixed bug in :class:.CTE construct along the same lines as that of
🎫4204 where a :class:.CTE that was aliased would not copy itself
correctly during a "clone" operation as is frequent within the ORM as well
as when using the :meth:.ClauseElement.params method.

Change-Id: Id68d72dd244dedfc7bd6116c9a5123c51a55ea20
Fixes: #4210

→ 9a13f00

[sqlalchemy-bot]

Michael Bayer (@zzzeek) wrote:

so the test added here actually has the wrong SQL in it, which will be fixed by the next version of #4204.

+            "WITH RECURSIVE foo(n) AS (SELECT values(:values_1) AS n "

+            "UNION ALL SELECT foo.n + :n_1 AS anon_1 FROM t AS foo "

+            "WHERE foo.n < :n_2) SELECT sum(foo.n) AS sum_1 FROM foo"

"t" is not defined. should be:

            "WITH RECURSIVE foo(n) AS (SELECT values(:values_1) AS n "
            "UNION ALL SELECT foo.n + :n_1 AS anon_1 FROM foo "
            "WHERE foo.n < :n_2) SELECT sum(foo.n) AS sum_1 FROM foo"