- Fixed bug in SQLite "join rewriting" where usage of an exists() construct

would fail to be rewritten properly, such as when the exists is
mapped to a column_property in an intricate nested-join scenario. #2967

3 files changed, 67 insertions, 7 deletions

diff --git a/doc/build/changelog/changelog_09.rst b/doc/build/changelog/changelog_09.rst
index 52bc11cdc..e6693677a 100644
--- a/doc/build/changelog/changelog_09.rst
+++ b/doc/build/changelog/changelog_09.rst
@@ -15,6 +15,14 @@
     :version: 0.9.3
 
     .. change::
+        :tags: orm, bug, sqlite
+        :tickets: 2967
+
+        Fixed bug in SQLite "join rewriting" where usage of an exists() construct
+        would fail to be rewritten properly, such as when the exists is
+        mapped to a column_property in an intricate nested-join scenario.
+
+    .. change::
         :tags: sqlite, bug
 
         The SQLite dialect will now skip unsupported arguments when reflecting
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index d597837bd..17c9c9e8b 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1286,7 +1286,7 @@ class SQLCompiler(Compiled):
         # call down to compiler.visit_join(), compiler.visit_select()
         join_name = selectable.Join.__visit_name__
         select_name = selectable.Select.__visit_name__
-
+        alias_name = selectable.Alias.__visit_name__
         def visit(element, **kw):
             if element in column_translate[-1]:
                 return column_translate[-1][element]
@@ -1307,7 +1307,6 @@ class SQLCompiler(Compiled):
                 selectable_ = selectable.Select(
                                     [right.element],
                                     use_labels=True).alias()
-
                 for c in selectable_.c:
                     c._key_label = c.key
                     c._label = c.name
@@ -1336,7 +1335,8 @@ class SQLCompiler(Compiled):
                 newelem.right = selectable_
 
                 newelem.onclause = visit(newelem.onclause, **kw)
-            elif newelem.__visit_name__ is select_name:
+            elif newelem.__visit_name__ is alias_name \
+                and newelem.element.__visit_name__ is select_name:
                 column_translate.append({})
                 newelem._copy_internals(clone=visit, **kw)
                 del column_translate[-1]
diff --git a/test/sql/test_join_rewriting.py b/test/sql/test_join_rewriting.py
index 801d5ce9a..d44a002f7 100644
--- a/test/sql/test_join_rewriting.py
+++ b/test/sql/test_join_rewriting.py
@@ -1,11 +1,10 @@
-from sqlalchemy import Table, Column, Integer, MetaData, ForeignKey, select
+from sqlalchemy import Table, Column, Integer, MetaData, ForeignKey, select, exists
 from sqlalchemy.testing import fixtures, AssertsCompiledSQL, eq_
 from sqlalchemy import util
 from sqlalchemy.engine import default
 from sqlalchemy import testing
 
 
-
 m = MetaData()
 
 a = Table('a', m,
@@ -17,6 +16,11 @@ b = Table('b', m,
         Column('a_id', Integer, ForeignKey('a.id'))
     )
 
+a_to_b = Table('a_to_b', m,
+        Column('a_id', Integer, ForeignKey('a.id')),
+        Column('b_id', Integer, ForeignKey('b.id')),
+    )
+
 c = Table('c', m,
         Column('id', Integer, primary_key=True),
         Column('b_id', Integer, ForeignKey('b.id'))
@@ -137,6 +141,26 @@ class _JoinRewriteTestBase(AssertsCompiledSQL):
             self._a_bc_comma_a1_selbc
         )
 
+    def test_a_atobalias_balias_c_w_exists(self):
+        a_to_b_alias = a_to_b.alias()
+        b_alias = b.alias()
+
+        j1 = a_to_b_alias.join(b_alias)
+        j2 = a.outerjoin(j1, a.c.id == a_to_b_alias.c.a_id)
+
+        # TODO: if we put straight a_to_b_alias here,
+        # it fails to alias the columns clause.
+        s = select([a, a_to_b_alias.c.a_id, a_to_b_alias.c.b_id,
+                    b_alias.c.id, b_alias.c.a_id,
+                    exists().select_from(c).where(c.c.b_id == b_alias.c.id).label(None)
+                ], use_labels=True).select_from(j2)
+
+        self._test(
+            s,
+            self._a_atobalias_balias_c_w_exists
+        )
+
+
 
 class JoinRewriteTest(_JoinRewriteTestBase, fixtures.TestBase):
     """test rendering of each join with right-nested rewritten as
@@ -221,7 +245,17 @@ class JoinRewriteTest(_JoinRewriteTestBase, fixtures.TestBase):
         "JOIN b_key ON b_key.id = anon_1.bid) AS anon_2 ON a.id = anon_2.anon_1_aid"
     )
 
-
+    _a_atobalias_balias_c_w_exists = (
+        "SELECT a.id AS a_id, "
+        "anon_1.a_to_b_1_a_id AS a_to_b_1_a_id, anon_1.a_to_b_1_b_id AS a_to_b_1_b_id, "
+        "anon_1.b_1_id AS b_1_id, anon_1.b_1_a_id AS b_1_a_id, "
+        "EXISTS (SELECT * FROM c WHERE c.b_id = anon_1.b_1_id) AS anon_2 "
+        "FROM a LEFT OUTER JOIN (SELECT a_to_b_1.a_id AS a_to_b_1_a_id, "
+        "a_to_b_1.b_id AS a_to_b_1_b_id, b_1.id AS b_1_id, b_1.a_id AS b_1_a_id "
+        "FROM a_to_b AS a_to_b_1 "
+        "JOIN b AS b_1 ON b_1.id = a_to_b_1.b_id) AS anon_1 "
+        "ON a.id = anon_1.a_to_b_1_a_id"
+    )
 
 class JoinPlainTest(_JoinRewriteTestBase, fixtures.TestBase):
     """test rendering of each join with normal nesting."""
@@ -287,6 +321,15 @@ class JoinPlainTest(_JoinRewriteTestBase, fixtures.TestBase):
         "ON b_key_1.id = a_to_b_key_1.bid) ON a.id = a_to_b_key_1.aid"
     )
 
+    _a_atobalias_balias_c_w_exists = (
+        "SELECT a.id AS a_id, a_to_b_1.a_id AS a_to_b_1_a_id, "
+        "a_to_b_1.b_id AS a_to_b_1_b_id, b_1.id AS b_1_id, b_1.a_id AS b_1_a_id, "
+        "EXISTS (SELECT * FROM c WHERE c.b_id = b_1.id) AS anon_1 "
+        "FROM a LEFT OUTER JOIN "
+        "(a_to_b AS a_to_b_1 JOIN b AS b_1 ON b_1.id = a_to_b_1.b_id) "
+        "ON a.id = a_to_b_1.a_id"
+    )
+
 class JoinNoUseLabelsTest(_JoinRewriteTestBase, fixtures.TestBase):
     @util.classproperty
     def __dialect__(cls):
@@ -355,10 +398,19 @@ class JoinNoUseLabelsTest(_JoinRewriteTestBase, fixtures.TestBase):
         "ON a.id = a_to_b_key_1.aid"
     )
 
+    _a_atobalias_balias_c_w_exists = (
+        "SELECT a.id, a_to_b_1.a_id, a_to_b_1.b_id, b_1.id, b_1.a_id, "
+        "EXISTS (SELECT * FROM c WHERE c.b_id = b_1.id) AS anon_1 "
+        "FROM a LEFT OUTER JOIN "
+        "(a_to_b AS a_to_b_1 JOIN b AS b_1 ON b_1.id = a_to_b_1.b_id) "
+        "ON a.id = a_to_b_1.a_id"
+    )
+
 class JoinExecTest(_JoinRewriteTestBase, fixtures.TestBase):
     """invoke the SQL on the current backend to ensure compatibility"""
 
-    _a_bc = _a_bc_comma_a1_selbc = _a__b_dc = _a_bkeyassoc = _a_bkeyassoc_aliased = None
+    _a_bc = _a_bc_comma_a1_selbc = _a__b_dc = _a_bkeyassoc = \
+        _a_bkeyassoc_aliased = _a_atobalias_balias_c_w_exists = None
 
     @classmethod
     def setup_class(cls):