From 2174426599e68ebef4a1eab7f090de0674c25c82 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Tue, 12 Nov 2024 14:50:50 -0500
Subject: [PATCH] dont leak mutating bindparams list into AnalyzedFunction

Fixed issue in "lambda SQL" feature where the tracking of bound parameters
could be corrupted if the same lambda were evaluated across multiple
compile phases, including when using the same lambda across multiple engine
instances or with statement caching disabled.

Fixes: #12084
Change-Id: I327aa93ce7feb2326a22113164bd834b96b6b889
(cherry picked from commit 5bbefc41b7b2695c95c9c93bcaabd8c4731e348e)
---
 doc/build/changelog/unreleased_20/12084.rst |  9 +++++
 lib/sqlalchemy/sql/lambdas.py               |  2 +-
 test/sql/test_lambdas.py                    | 41 +++++++++++++++++++++
 3 files changed, 51 insertions(+), 1 deletion(-)
 create mode 100644 doc/build/changelog/unreleased_20/12084.rst

diff --git a/doc/build/changelog/unreleased_20/12084.rst b/doc/build/changelog/unreleased_20/12084.rst
new file mode 100644
index 0000000000..0eef5c9a1c
--- /dev/null
+++ b/doc/build/changelog/unreleased_20/12084.rst
@@ -0,0 +1,9 @@
+.. change::
+    :tags: bug, sql
+    :tickets: 12084
+
+    Fixed issue in "lambda SQL" feature where the tracking of bound parameters
+    could be corrupted if the same lambda were evaluated across multiple
+    compile phases, including when using the same lambda across multiple engine
+    instances or with statement caching disabled.
+
diff --git a/lib/sqlalchemy/sql/lambdas.py b/lib/sqlalchemy/sql/lambdas.py
index 7a6b7b8f77..2657b2c243 100644
--- a/lib/sqlalchemy/sql/lambdas.py
+++ b/lib/sqlalchemy/sql/lambdas.py
@@ -278,7 +278,7 @@ class LambdaElement(elements.ClauseElement):
                         rec = AnalyzedFunction(
                             tracker, self, apply_propagate_attrs, fn
                         )
-                        rec.closure_bindparams = bindparams
+                        rec.closure_bindparams = list(bindparams)
                         lambda_cache[key] = rec
                     else:
                         rec = lambda_cache[key]
diff --git a/test/sql/test_lambdas.py b/test/sql/test_lambdas.py
index 17991ea2e3..9eb20dd4e5 100644
--- a/test/sql/test_lambdas.py
+++ b/test/sql/test_lambdas.py
@@ -1889,6 +1889,47 @@ class LambdaElementTest(
                 (7, "foo"),
             )
 
+    def test_bindparam_not_cached(self, user_address_fixture, testing_engine):
+        """test #12084"""
+
+        users, addresses = user_address_fixture
+
+        engine = testing_engine(
+            share_pool=True, options={"query_cache_size": 0}
+        )
+        with engine.begin() as conn:
+            conn.execute(
+                users.insert(),
+                [{"id": 7, "name": "bar"}, {"id": 8, "name": "foo"}],
+            )
+
+        def make_query(stmt, *criteria):
+            for crit in criteria:
+                stmt += lambda s: s.where(crit)
+
+            return stmt
+
+        for i in range(2):
+            with engine.connect() as conn:
+                stmt = lambda_stmt(lambda: select(users))
+                # create a filter criterion that will never match anything
+                stmt1 = make_query(
+                    stmt,
+                    users.c.name == "bar",
+                    users.c.name == "foo",
+                )
+
+                assert len(conn.scalars(stmt1).all()) == 0
+
+                stmt2 = make_query(
+                    stmt,
+                    users.c.name == "bar",
+                    users.c.name == "bar",
+                    users.c.name == "foo",
+                )
+
+                assert len(conn.scalars(stmt2).all()) == 0
+
 
 class DeferredLambdaElementTest(
     fixtures.TestBase, testing.AssertsExecutionResults, AssertsCompiledSQL
-- 
2.47.3