Check for the endmost target when chaining contains()

Fixed regression in association proxy due to :ticket:`3769`
(allow for chained any() / has()) where contains() against
an association proxy chained in the form
(o2m relationship, associationproxy(m2o relationship, m2o relationship))
would raise an error regarding the re-application of contains()
on the final link of the chain.

Change-Id: Iea51ce84c2c5a332416fff10b1ba0e676cf0bad7
Fixes: #4150

3 files changed, 53 insertions, 1 deletions

diff --git a/doc/build/changelog/unreleased_12/4150.rst b/doc/build/changelog/unreleased_12/4150.rst
new file mode 100644
index 000000000..db3d05b0d
--- /dev/null
+++ b/doc/build/changelog/unreleased_12/4150.rst
@@ -0,0 +1,10 @@
+.. change::
+    :tags: bug, ext
+    :tickets: 4150
+
+    Fixed regression in association proxy due to :ticket:`3769`
+    (allow for chained any() / has()) where contains() against
+    an association proxy chained in the form
+    (o2m relationship, associationproxy(m2o relationship, m2o relationship))
+    would raise an error regarding the re-application of contains()
+    on the final link of the chain.
diff --git a/lib/sqlalchemy/ext/associationproxy.py b/lib/sqlalchemy/ext/associationproxy.py
index c0dbb538e..d6e9a43cc 100644
--- a/lib/sqlalchemy/ext/associationproxy.py
+++ b/lib/sqlalchemy/ext/associationproxy.py
@@ -462,6 +462,7 @@ class AssociationProxy(interfaces.InspectionAttrInfo):
         if target_assoc is not None:
             return self._comparator._criterion_exists(
                 target_assoc.contains(obj)
+                if not target_assoc.scalar else target_assoc == obj
             )
         elif self._target_is_object and self.scalar and \
                 not self._value_is_scalar:
diff --git a/test/ext/test_associationproxy.py b/test/ext/test_associationproxy.py
index 28eb7a712..d8fd4dc9d 100644
--- a/test/ext/test_associationproxy.py
+++ b/test/ext/test_associationproxy.py
@@ -1281,6 +1281,10 @@ class ComparatorTest(fixtures.MappedTest, AssertsCompiledSQL):
             # nonuselist -> nonuselist
             user = association_proxy('user_keyword', 'user')
 
+            # uselist assoc_proxy -> collection -> assoc_proxy -> scalar object
+            # (o2m relationship, associationproxy(m2o relationship, m2o relationship))
+            singulars = association_proxy("user_keywords", "singular")
+
         class UserKeyword(cls.Comparable):
             def __init__(self, user=None, keyword=None):
                 self.user = user
@@ -1289,6 +1293,8 @@ class ComparatorTest(fixtures.MappedTest, AssertsCompiledSQL):
             common_users = association_proxy("keyword", "user")
             keyword_name = association_proxy("keyword", "keyword")
 
+            singular = association_proxy("user", "singular")
+
         class Singular(cls.Comparable):
             def __init__(self, value=None):
                 self.value = value
@@ -1311,7 +1317,8 @@ class ComparatorTest(fixtures.MappedTest, AssertsCompiledSQL):
             'singular': relationship(Singular)
         })
         mapper(Keyword, keywords, properties={
-            'user_keyword': relationship(UserKeyword, uselist=False)
+            'user_keyword': relationship(UserKeyword, uselist=False),
+            'user_keywords': relationship(UserKeyword)
         })
 
         mapper(UserKeyword, userkeywords, properties={
@@ -1707,6 +1714,40 @@ class ComparatorTest(fixtures.MappedTest, AssertsCompiledSQL):
         )
         self._equivalent(q1, q2)
 
+    def test_filter_contains_chained_any_to_has_to_eq(self):
+        User = self.classes.User
+        Keyword = self.classes.Keyword
+        UserKeyword = self.classes.UserKeyword
+        Singular = self.classes.Singular
+
+        singular = self.session.query(Singular).order_by(Singular.id).first()
+
+        q1 = self.session.query(Keyword).filter(
+            Keyword.singulars.contains(singular)
+        )
+        self.assert_compile(
+            q1,
+            "SELECT keywords.id AS keywords_id, "
+            "keywords.keyword AS keywords_keyword, "
+            "keywords.singular_id AS keywords_singular_id "
+            "FROM keywords "
+            "WHERE EXISTS (SELECT 1 "
+            "FROM userkeywords "
+            "WHERE keywords.id = userkeywords.keyword_id AND "
+            "(EXISTS (SELECT 1 "
+            "FROM users "
+            "WHERE users.id = userkeywords.user_id AND "
+            ":param_1 = users.singular_id)))",
+            checkparams={"param_1": singular.id}
+        )
+
+        q2 = self.session.query(Keyword).filter(
+            Keyword.user_keywords.any(
+                UserKeyword.user.has(User.singular == singular)
+            )
+        )
+        self._equivalent(q1, q2)
+
     def test_has_criterion_nul(self):
         # but we don't allow that with any criterion...
         User = self.classes.User