From 6785a09670cfc90ef0f6977baa3d7dc26c8d1751 Mon Sep 17 00:00:00 2001
From: Mike Bayer <mike_mp@zzzcomputing.com>
Date: Tue, 9 Dec 2025 09:07:20 -0500
Subject: [PATCH] use os.urandom() for CTE, aliased anon id

Fixed issue where anonymous label generation for :class:`.CTE` constructs
could produce name collisions when Python's garbage collector reused memory
addresses during complex query compilation. The anonymous name generation
for :class:`.CTE` and other aliased constructs like :class:`.Alias`,
:class:`.Subquery` and others now use :func:`os.urandom` to generate unique
identifiers instead of relying on object ``id()``, ensuring uniqueness even
in cases of aggressive garbage collection and memory reuse.

Fixes: #12990
Change-Id: If56a53840684bc7d2b7637f1e154dfed1cac5f32
---
 doc/build/changelog/unreleased_21/12990.rst | 11 +++++++++++
 lib/sqlalchemy/sql/elements.py              |  4 ++--
 lib/sqlalchemy/sql/selectable.py            |  5 ++++-
 test/orm/test_query.py                      |  3 ++-
 4 files changed, 19 insertions(+), 4 deletions(-)
 create mode 100644 doc/build/changelog/unreleased_21/12990.rst

diff --git a/doc/build/changelog/unreleased_21/12990.rst b/doc/build/changelog/unreleased_21/12990.rst
new file mode 100644
index 0000000000..92b441c9d2
--- /dev/null
+++ b/doc/build/changelog/unreleased_21/12990.rst
@@ -0,0 +1,11 @@
+.. change::
+    :tags: bug, sql
+    :tickets: 12990
+
+    Fixed issue where anonymous label generation for :class:`.CTE` constructs
+    could produce name collisions when Python's garbage collector reused memory
+    addresses during complex query compilation. The anonymous name generation
+    for :class:`.CTE` and other aliased constructs like :class:`.Alias`,
+    :class:`.Subquery` and others now use :func:`os.urandom` to generate unique
+    identifiers instead of relying on object ``id()``, ensuring uniqueness even
+    in cases of aggressive garbage collection and memory reuse.
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 674560d7e1..0b97df18c7 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -5953,7 +5953,7 @@ class _anonymous_label(_truncated_label):
 
     @classmethod
     def safe_construct_with_key(
-        cls, seed: int, body: str, sanitize_key: bool = False
+        cls, seed: int | str, body: str, sanitize_key: bool = False
     ) -> typing_Tuple[_anonymous_label, str]:
         # need to escape chars that interfere with format
         # strings in any case, issue #8724
@@ -5969,7 +5969,7 @@ class _anonymous_label(_truncated_label):
 
     @classmethod
     def safe_construct(
-        cls, seed: int, body: str, sanitize_key: bool = False
+        cls, seed: int | str, body: str, sanitize_key: bool = False
     ) -> _anonymous_label:
         # need to escape chars that interfere with format
         # strings in any case, issue #8724
diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py
index 0668bc3672..e1bf22856c 100644
--- a/lib/sqlalchemy/sql/selectable.py
+++ b/lib/sqlalchemy/sql/selectable.py
@@ -16,6 +16,7 @@ from __future__ import annotations
 import collections
 from enum import Enum
 import itertools
+import os
 from typing import AbstractSet
 from typing import Any as TODO_Any
 from typing import Any
@@ -1744,7 +1745,9 @@ class AliasedReturnsRows(NoInit, NamedFromClause):
                 name = getattr(selectable, "name", None)
                 if isinstance(name, _anonymous_label):
                     name = None
-            name = _anonymous_label.safe_construct(id(self), name or "anon")
+            name = _anonymous_label.safe_construct(
+                os.urandom(10).hex(), name or "anon"
+            )
         self.name = name
 
     def _refresh_for_new_column(self, column: ColumnElement[Any]) -> None:
diff --git a/test/orm/test_query.py b/test/orm/test_query.py
index 5863b553fc..e39a70c47c 100644
--- a/test/orm/test_query.py
+++ b/test/orm/test_query.py
@@ -73,6 +73,7 @@ from sqlalchemy.testing.assertions import assert_raises
 from sqlalchemy.testing.assertions import assert_raises_message
 from sqlalchemy.testing.assertions import assert_warns_message
 from sqlalchemy.testing.assertions import eq_
+from sqlalchemy.testing.assertions import eq_regex
 from sqlalchemy.testing.assertions import expect_deprecated
 from sqlalchemy.testing.assertions import expect_raises
 from sqlalchemy.testing.assertions import expect_warnings
@@ -2175,7 +2176,7 @@ class ExpressionTest(QueryTest, AssertsCompiledSQL):
 
         eq_(a1.name, "foo1")
         eq_(a2.name, "foo2")
-        eq_(a3.name, "%%(%d anon)s" % id(a3))
+        eq_regex(a3.name, r"%\([0-9a-z]+ anon\)s")
 
     def test_labeled_subquery(self):
         User = self.classes.User
-- 
2.47.3