- Fixed bug in SQLite join rewriting where anonymized column names

due to repeats would not correctly be rewritten in subqueries.
This would affect SELECT queries with any kind of subquery + join.
fixes #3057

4 files changed, 69 insertions, 1 deletions

diff --git a/doc/build/changelog/changelog_09.rst b/doc/build/changelog/changelog_09.rst
index 60ab05d12..ecebfeab5 100644
--- a/doc/build/changelog/changelog_09.rst
+++ b/doc/build/changelog/changelog_09.rst
@@ -15,6 +15,15 @@
     :version: 0.9.5
 
     .. change::
+        :tags: bug, orm
+        :tickets: 3057
+        :versions: 1.0.0
+
+        Fixed bug in SQLite join rewriting where anonymized column names
+        due to repeats would not correctly be rewritten in subqueries.
+        This would affect SELECT queries with any kind of subquery + join.
+
+    .. change::
         :tags: bug, sql
         :tickets: 3012
         :versions: 1.0.0
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index ce6056418..bf0ac3def 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -3488,3 +3488,6 @@ class AnnotatedColumnElement(Annotated):
     def info(self):
         return self._Annotated__element.info
 
+    @util.memoized_property
+    def anon_label(self):
+        return self._Annotated__element.anon_label
diff --git a/test/sql/test_join_rewriting.py b/test/sql/test_join_rewriting.py
index 35797871a..4e83cafab 100644
--- a/test/sql/test_join_rewriting.py
+++ b/test/sql/test_join_rewriting.py
@@ -16,6 +16,10 @@ b = Table('b', m,
         Column('a_id', Integer, ForeignKey('a.id'))
     )
 
+b_a = Table('b_a', m,
+        Column('id', Integer, primary_key=True),
+    )
+
 b1 = Table('b1', m,
         Column('id', Integer, primary_key=True),
         Column('a_id', Integer, ForeignKey('a.id'))
@@ -201,6 +205,24 @@ class _JoinRewriteTestBase(AssertsCompiledSQL):
             self._b_ab1_union_c_ab2
         )
 
+    def test_b_a_id_double_overlap_annotated(self):
+        # test issue #3057
+        # this involves annotations so try to loop those in.
+        j1 = b.join(b_a, b.c.id == b_a.c.id)
+        annot = [
+                    b.c.id._annotate({}),
+                    b.c.a_id._annotate({}),
+                    b_a.c.id._annotate({})
+                ]
+
+        s = select(annot).select_from(j1).apply_labels().alias()
+
+        s = select(list(s.c)).apply_labels()
+
+        self._test(
+            s,
+            self._b_a_id_double_overlap_annotated
+        )
 
 class JoinRewriteTest(_JoinRewriteTestBase, fixtures.TestBase):
     """test rendering of each join with right-nested rewritten as
@@ -320,6 +342,13 @@ class JoinRewriteTest(_JoinRewriteTestBase, fixtures.TestBase):
         "FROM a JOIN b2 ON a.id = b2.a_id) AS anon_2 ON anon_2.a_id = b.a_id)"
     )
 
+    _b_a_id_double_overlap_annotated = (
+        "SELECT anon_1.b_id AS anon_1_b_id, anon_1.b_a_id AS anon_1_b_a_id, "
+        "anon_1.id_1 AS anon_1_id_1 "
+        "FROM (SELECT b.id AS b_id, b.a_id AS b_a_id, b_a.id AS id_1 "
+        "FROM b JOIN b_a ON b.id = b_a.id) AS anon_1"
+    )
+
 class JoinPlainTest(_JoinRewriteTestBase, fixtures.TestBase):
     """test rendering of each join with normal nesting."""
     @util.classproperty
@@ -413,6 +442,13 @@ class JoinPlainTest(_JoinRewriteTestBase, fixtures.TestBase):
         "JOIN (a JOIN b2 ON a.id = b2.a_id) ON a.id = b.a_id)"
     )
 
+    _b_a_id_double_overlap_annotated = (
+        "SELECT anon_1.b_id AS anon_1_b_id, anon_1.b_a_id AS anon_1_b_a_id, "
+        "anon_1.id_1 AS anon_1_id_1 FROM "
+        "(SELECT b.id AS b_id, b.a_id AS b_a_id, b_a.id AS id_1 "
+        "FROM b JOIN b_a ON b.id = b_a.id) AS anon_1"
+    )
+
 class JoinNoUseLabelsTest(_JoinRewriteTestBase, fixtures.TestBase):
     @util.classproperty
     def __dialect__(cls):
@@ -506,6 +542,12 @@ class JoinNoUseLabelsTest(_JoinRewriteTestBase, fixtures.TestBase):
         "FROM b JOIN (a JOIN b2 ON a.id = b2.a_id) ON a.id = b.a_id)"
     )
 
+    _b_a_id_double_overlap_annotated = (
+        "SELECT anon_1.b_id, anon_1.b_a_id, anon_1.id_1 FROM "
+        "(SELECT b.id AS b_id, b.a_id AS b_a_id, b_a.id AS id_1 "
+        "FROM b JOIN b_a ON b.id = b_a.id) AS anon_1"
+    )
+
 class JoinExecTest(_JoinRewriteTestBase, fixtures.TestBase):
     """invoke the SQL on the current backend to ensure compatibility"""
 
@@ -513,7 +555,8 @@ class JoinExecTest(_JoinRewriteTestBase, fixtures.TestBase):
 
     _a_bc = _a_bc_comma_a1_selbc = _a__b_dc = _a_bkeyassoc = \
         _a_bkeyassoc_aliased = _a_atobalias_balias_c_w_exists = \
-        _a_atobalias_balias = _b_ab1_union_c_ab2 = None
+        _a_atobalias_balias = _b_ab1_union_c_ab2 = \
+        _b_a_id_double_overlap_annotated = None
 
     @classmethod
     def setup_class(cls):
diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py
index ed97bb37f..3ee8127b6 100644
--- a/test/sql/test_selectable.py
+++ b/test/sql/test_selectable.py
@@ -1549,6 +1549,19 @@ class AnnotationsTest(fixtures.TestBase):
         t = Table('t', MetaData(), c1)
         is_(c1_a.table, t)
 
+    def test_basic_attrs(self):
+        t = Table('t', MetaData(),
+                        Column('x', Integer, info={'q': 'p'}),
+                        Column('y', Integer, key='q'))
+        x_a = t.c.x._annotate({})
+        y_a = t.c.q._annotate({})
+        t.c.x.info['z'] = 'h'
+
+        eq_(y_a.key, 'q')
+        is_(x_a.table, t)
+        eq_(x_a.info, {'q': 'p', 'z': 'h'})
+        eq_(t.c.x.anon_label, x_a.anon_label)
+
     def test_custom_constructions(self):
         from sqlalchemy.schema import Column
         class MyColumn(Column):