implement independent CTEs

Added new method :meth:`_sql.HasCTE.add_cte` to each of the
:func:`_sql.select`, :func:`_sql.insert`, :func:`_sql.update` and
:func:`_sql.delete` constructs. This method will add the given
:class:`_sql.CTE` as an "independent" CTE of the statement, meaning it
renders in the WITH clause above the statement unconditionally even if it
is not otherwise referenced in the primary statement. This is a popular use
case on the PostgreSQL database where a CTE is used for a DML statement
that runs against database rows independently of the primary statement.

Fixes: #6752
Change-Id: Ibf635763e40269cbd10f4c17e208850d8e8d0188

1 files changed, 11 insertions, 2 deletions

diff --git a/lib/sqlalchemy/sql/crud.py b/lib/sqlalchemy/sql/crud.py
index de847fb7f..74f5a1d05 100644
--- a/lib/sqlalchemy/sql/crud.py
+++ b/lib/sqlalchemy/sql/crud.py
@@ -224,7 +224,17 @@ def _handle_values_anonymous_param(compiler, col, value, name, **kw):
     # rather than having
     # compiler.visit_bindparam()->compiler._truncated_identifier make up a
     # name.  Saves on call counts also.
-    if value.unique and isinstance(value.key, elements._truncated_label):
+
+    # for INSERT/UPDATE that's a CTE, we don't need names to match to
+    # external parameters and these would also conflict in the case where
+    # multiple insert/update are combined together using CTEs
+    is_cte = "visiting_cte" in kw
+
+    if (
+        not is_cte
+        and value.unique
+        and isinstance(value.key, elements._truncated_label)
+    ):
         compiler.truncated_names[("bindparam", value.key)] = name
 
     if value.type._isnull:
@@ -460,7 +470,6 @@ def _append_param_parameter(
     values,
     kw,
 ):
-
     value = parameters.pop(col_key)
 
     col_value = compiler.preparer.format_column(