From bdb7fd10e9d488f8a7bfff2f508f49e517e50f4c Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Wed, 14 Nov 2018 11:02:40 -0500 Subject: [PATCH] Add secondary selectable to FROM clauses for correlated exists In continuing with a similar theme as that of very recent :ticket:`4349`, repaired issue with :meth:`.RelationshipProperty.Comparator.any` and :meth:`.RelationshipProperty.Comparator.has` where the "secondary" selectable needs to be explicitly part of the FROM clause in the EXISTS subquery to suit the case where this "secondary" is a :class:`.Join` object. Fixes: #4366 Change-Id: Icd0d0c3871bbd0059f0c9256e2b980edc2c90551 (cherry picked from commit fdfd1680603f6663182ba4766ae2f5abdd3ced45) --- doc/build/changelog/unreleased_12/4366.rst | 10 ++++++++++ lib/sqlalchemy/orm/relationships.py | 7 +++++-- test/orm/test_query.py | 23 ++++++++++++++++++++++ 3 files changed, 38 insertions(+), 2 deletions(-) create mode 100644 doc/build/changelog/unreleased_12/4366.rst diff --git a/doc/build/changelog/unreleased_12/4366.rst b/doc/build/changelog/unreleased_12/4366.rst new file mode 100644 index 0000000000..d3332633a5 --- /dev/null +++ b/doc/build/changelog/unreleased_12/4366.rst @@ -0,0 +1,10 @@ +.. change:: + :tags: bug, orm + :tickets: 4366 + + In continuing with a similar theme as that of very recent :ticket:`4349`, + repaired issue with :meth:`.RelationshipProperty.Comparator.any` and + :meth:`.RelationshipProperty.Comparator.has` where the "secondary" + selectable needs to be explicitly part of the FROM clause in the + EXISTS subquery to suit the case where this "secondary" is a :class:`.Join` + object. \ No newline at end of file diff --git a/lib/sqlalchemy/orm/relationships.py b/lib/sqlalchemy/orm/relationships.py index 5e1fcf1328..151eebe3d0 100644 --- a/lib/sqlalchemy/orm/relationships.py +++ b/lib/sqlalchemy/orm/relationships.py @@ -1099,9 +1099,12 @@ class RelationshipProperty(StrategizedProperty): crit = j & sql.True_._ifnone(criterion) - ex = sql.exists([1], crit, from_obj=dest).correlate_except(dest) if secondary is not None: - ex = ex.correlate_except(secondary) + ex = sql.exists([1], crit, from_obj=[dest, secondary]).\ + correlate_except(dest, secondary) + else: + ex = sql.exists([1], crit, from_obj=dest).\ + correlate_except(dest) return ex def any(self, criterion=None, **kwargs): diff --git a/test/orm/test_query.py b/test/orm/test_query.py index 3a35111548..ce46e5eaf3 100644 --- a/test/orm/test_query.py +++ b/test/orm/test_query.py @@ -2308,6 +2308,8 @@ class FilterTest(QueryTest, AssertsCompiledSQL): ) def test_any(self): + # see also HasAnyTest, a newer suite which tests these at the level of + # SQL compilation User, Address = self.classes.User, self.classes.Address sess = create_session() @@ -2344,6 +2346,8 @@ class FilterTest(QueryTest, AssertsCompiledSQL): filter(~User.addresses.any()).all() def test_any_doesnt_overcorrelate(self): + # see also HasAnyTest, a newer suite which tests these at the level of + # SQL compilation User, Address = self.classes.User, self.classes.Address sess = create_session() @@ -2356,6 +2360,8 @@ class FilterTest(QueryTest, AssertsCompiledSQL): Address.email_address == 'fred@fred.com')).all() def test_has(self): + # see also HasAnyTest, a newer suite which tests these at the level of + # SQL compilation Dingaling, User, Address = ( self.classes.Dingaling, self.classes.User, self.classes.Address) @@ -2607,6 +2613,23 @@ class HasAnyTest( id = Column(Integer, primary_key=True) b_id = Column(ForeignKey(B.id)) + d = relationship( + 'D', + secondary="join(B, C)", + primaryjoin="A.b_id == B.id", + secondaryjoin="C.d_id == D.id", + uselist=False) + + def test_has_composite_secondary(self): + A, D = self.classes("A", "D") + s = Session() + self.assert_compile( + s.query(A).filter(A.d.has(D.id == 1)), + "SELECT a.id AS a_id, a.b_id AS a_b_id FROM a WHERE EXISTS " + "(SELECT 1 FROM d, b JOIN c ON c.id = b.c_id " + "WHERE a.b_id = b.id AND c.d_id = d.id AND d.id = :id_1)" + ) + def test_has_many_to_one(self): B, C = self.classes("B", "C") s = Session() -- 2.47.2