From f00544a589d5002ddf0146706c4ba67509452ea7 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 13 Oct 2013 16:42:32 -0400 Subject: - Added new option to :func:`.relationship` ``distinct_target_key``. This enables the subquery eager loader strategy to apply a DISTINCT to the innermost SELECT subquery, to assist in the case where duplicate rows are generated by the innermost query which corresponds to this relationship (there's not yet a general solution to the issue of dupe rows within subquery eager loading, however, when joins outside of the innermost subquery produce dupes). When the flag is set to ``True``, the DISTINCT is rendered unconditionally, and when it is set to ``None``, DISTINCT is rendered if the innermost relationship targets columns that do not comprise a full primary key. The option defaults to False in 0.8 (e.g. off by default in all cases), None in 0.9 (e.g. automatic by default). Thanks to Alexander Koval for help with this. [ticket:2836] --- test/orm/test_subquery_relations.py | 194 ++++++++++++++++++++++++++++++++++++ 1 file changed, 194 insertions(+) (limited to 'test/orm/test_subquery_relations.py') diff --git a/test/orm/test_subquery_relations.py b/test/orm/test_subquery_relations.py index a6cc37691..176a30078 100644 --- a/test/orm/test_subquery_relations.py +++ b/test/orm/test_subquery_relations.py @@ -1563,3 +1563,197 @@ class CyclicalInheritingEagerTestTwo(fixtures.DeclarativeMappedTest, d = session.query(Director).options(subqueryload('*')).first() assert len(list(session)) == 3 + +class SubqueryloadDistinctTest(fixtures.DeclarativeMappedTest, + testing.AssertsCompiledSQL): + __dialect__ = 'default' + + run_inserts = 'once' + run_deletes = None + + @classmethod + def setup_classes(cls): + Base = cls.DeclarativeBasic + + class Director(Base): + __tablename__ = 'director' + id = Column(Integer, primary_key=True, + test_needs_autoincrement=True) + name = Column(String(50)) + + class DirectorPhoto(Base): + __tablename__ = 'director_photo' + id = Column(Integer, primary_key=True, + test_needs_autoincrement=True) + path = Column(String(255)) + director_id = Column(Integer, ForeignKey('director.id')) + director = relationship(Director, backref="photos") + + class Movie(Base): + __tablename__ = 'movie' + id = Column(Integer, primary_key=True, test_needs_autoincrement=True) + director_id = Column(Integer, ForeignKey('director.id')) + director = relationship(Director, backref="movies") + title = Column(String(50)) + credits = relationship("Credit", backref="movie") + + class Credit(Base): + __tablename__ = 'credit' + id = Column(Integer, primary_key=True, test_needs_autoincrement=True) + movie_id = Column(Integer, ForeignKey('movie.id')) + + @classmethod + def insert_data(cls): + Movie = cls.classes.Movie + Director = cls.classes.Director + DirectorPhoto = cls.classes.DirectorPhoto + Credit = cls.classes.Credit + + d = Director(name='Woody Allen') + d.photos = [DirectorPhoto(path='/1.jpg'), + DirectorPhoto(path='/2.jpg')] + d.movies = [Movie(title='Manhattan', credits=[Credit(), Credit()]), + Movie(title='Sweet and Lowdown', credits=[Credit()])] + sess = create_session() + sess.add_all([d]) + sess.flush() + + def test_distinct_strategy_opt_m2o(self): + self._run_test_m2o(True, None) + self._run_test_m2o(False, None) + + def test_distinct_unrelated_opt_m2o(self): + self._run_test_m2o(None, True) + self._run_test_m2o(None, False) + + def _run_test_m2o(self, + director_strategy_level, + photo_strategy_level): + + # test where the innermost is m2o, e.g. + # Movie->director + + Movie = self.classes.Movie + Director = self.classes.Director + + Movie.director.property.distinct_target_key = director_strategy_level + Director.photos.property.distinct_target_key = photo_strategy_level + + # the DISTINCT is controlled by + # only the Movie->director relationship, *not* the + # Director.photos + expect_distinct = director_strategy_level in (True, None) + + s = create_session() + + q = ( + s.query(Movie) + .options( + subqueryload(Movie.director) + .subqueryload(Director.photos) + ) + ) + ctx = q._compile_context() + + q2 = ctx.attributes[ + ('subquery', (inspect(Movie), inspect(Movie).attrs.director)) + ] + self.assert_compile( + q2, + 'SELECT director.id AS director_id, ' + 'director.name AS director_name, ' + 'anon_1.movie_director_id AS anon_1_movie_director_id ' + 'FROM (SELECT%s movie.director_id AS movie_director_id ' + 'FROM movie) AS anon_1 ' + 'JOIN director ON director.id = anon_1.movie_director_id ' + 'ORDER BY anon_1.movie_director_id' % ( + " DISTINCT" if expect_distinct else "") + ) + + ctx2 = q2._compile_context() + result = s.execute(q2) + rows = result.fetchall() + + if expect_distinct: + eq_(rows, [ + (1, 'Woody Allen', 1), + ]) + else: + eq_(rows, [ + (1, 'Woody Allen', 1), (1, 'Woody Allen', 1), + ]) + + q3 = ctx2.attributes[ + ('subquery', (inspect(Director), inspect(Director).attrs.photos)) + ] + + self.assert_compile( + q3, + 'SELECT director_photo.id AS director_photo_id, ' + 'director_photo.path AS director_photo_path, ' + 'director_photo.director_id AS director_photo_director_id, ' + 'director_1.id AS director_1_id ' + 'FROM (SELECT%s movie.director_id AS movie_director_id ' + 'FROM movie) AS anon_1 ' + 'JOIN director AS director_1 ON director_1.id = anon_1.movie_director_id ' + 'JOIN director_photo ON director_1.id = director_photo.director_id ' + 'ORDER BY director_1.id' % ( + " DISTINCT" if expect_distinct else "") + ) + result = s.execute(q3) + rows = result.fetchall() + if expect_distinct: + eq_(rows, [ + (1, u'/1.jpg', 1, 1), + (2, u'/2.jpg', 1, 1), + ]) + else: + eq_(rows, [ + (1, u'/1.jpg', 1, 1), + (2, u'/2.jpg', 1, 1), + (1, u'/1.jpg', 1, 1), + (2, u'/2.jpg', 1, 1), + ]) + + + movies = q.all() + + # check number of persistent objects in session + eq_(len(list(s)), 5) + + def test_cant_do_distinct_in_joins(self): + """the DISTINCT feature here works when the m2o is in the innermost + mapper, but when we are just joining along relationships outside + of that, we can still have dupes, and there's no solution to that. + + """ + Movie = self.classes.Movie + Credit = self.classes.Credit + + s = create_session() + + q = ( + s.query(Credit) + .options( + subqueryload(Credit.movie) + .subqueryload(Movie.director) + ) + ) + + ctx = q._compile_context() + + q2 = ctx.attributes[ + ('subquery', (inspect(Credit), Credit.movie.property)) + ] + ctx2 = q2._compile_context() + q3 = ctx2.attributes[ + ('subquery', (inspect(Movie), Movie.director.property)) + ] + + result = s.execute(q3) + eq_( + result.fetchall(), + [ + (1, 'Woody Allen', 1), (1, 'Woody Allen', 1), + ] + ) \ No newline at end of file -- cgit v1.2.1 From ab38a67995738ccaa17ecbcdd35feace6adcfc16 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 14 Oct 2013 10:35:54 -0400 Subject: - fix non-deterministic ordering point --- test/orm/test_subquery_relations.py | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) (limited to 'test/orm/test_subquery_relations.py') diff --git a/test/orm/test_subquery_relations.py b/test/orm/test_subquery_relations.py index 176a30078..c1b6e5eea 100644 --- a/test/orm/test_subquery_relations.py +++ b/test/orm/test_subquery_relations.py @@ -1708,12 +1708,13 @@ class SubqueryloadDistinctTest(fixtures.DeclarativeMappedTest, (2, u'/2.jpg', 1, 1), ]) else: - eq_(rows, [ + # oracle might not order the way we expect here + eq_(set(tuple(t) for t in rows), set([ (1, u'/1.jpg', 1, 1), (2, u'/2.jpg', 1, 1), (1, u'/1.jpg', 1, 1), (2, u'/2.jpg', 1, 1), - ]) + ])) movies = q.all() -- cgit v1.2.1 From ca851e5815152c7eb1009ad3e0dcf6fc5c6d527e Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Fri, 18 Oct 2013 19:03:21 -0400 Subject: use a set here also --- test/orm/test_subquery_relations.py | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'test/orm/test_subquery_relations.py') diff --git a/test/orm/test_subquery_relations.py b/test/orm/test_subquery_relations.py index c1b6e5eea..ef6649c3b 100644 --- a/test/orm/test_subquery_relations.py +++ b/test/orm/test_subquery_relations.py @@ -1703,10 +1703,10 @@ class SubqueryloadDistinctTest(fixtures.DeclarativeMappedTest, result = s.execute(q3) rows = result.fetchall() if expect_distinct: - eq_(rows, [ + eq_(set(tuple(t) for t in rows), set([ (1, u'/1.jpg', 1, 1), (2, u'/2.jpg', 1, 1), - ]) + ])) else: # oracle might not order the way we expect here eq_(set(tuple(t) for t in rows), set([ -- cgit v1.2.1 From 59ca4633acd42d90dc01aef9a40373ee98080481 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sun, 17 Nov 2013 13:45:23 -0500 Subject: - remove informix dialect, moved out to https://bitbucket.org/zzzeek/sqlalchemy_informixdb - remove informix, maxdb, access symbols from tests etc. --- test/orm/test_subquery_relations.py | 4 ---- 1 file changed, 4 deletions(-) (limited to 'test/orm/test_subquery_relations.py') diff --git a/test/orm/test_subquery_relations.py b/test/orm/test_subquery_relations.py index ef6649c3b..3181e0909 100644 --- a/test/orm/test_subquery_relations.py +++ b/test/orm/test_subquery_relations.py @@ -632,7 +632,6 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL): ], q.all()) self.assert_sql_count(testing.db, go, 6) - @testing.fails_on('maxdb', 'FIXME: unknown') def test_limit(self): """Limit operations combined with lazy-load relationships.""" @@ -706,7 +705,6 @@ class EagerTest(_fixtures.FixtureTest, testing.AssertsCompiledSQL): eq_([User(id=7, address=Address(id=1))], l) self.assert_sql_count(testing.db, go, 2) - @testing.fails_on('maxdb', 'FIXME: unknown') def test_many_to_one(self): users, Address, addresses, User = (self.tables.users, self.classes.Address, @@ -1144,7 +1142,6 @@ class SelfReferentialTest(fixtures.MappedTest): Column('parent_id', Integer, ForeignKey('nodes.id')), Column('data', String(30))) - @testing.fails_on('maxdb', 'FIXME: unknown') def test_basic(self): nodes = self.tables.nodes @@ -1309,7 +1306,6 @@ class SelfReferentialTest(fixtures.MappedTest): ]), d) self.assert_sql_count(testing.db, go, 3) - @testing.fails_on('maxdb', 'FIXME: unknown') def test_no_depth(self): """no join depth is set, so no eager loading occurs.""" -- cgit v1.2.1 From 84f1d3417978197c695850b3711ea4b7e2582be8 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 16 Dec 2013 19:17:41 -0500 Subject: - An adjustment to the :func:`.subqueryload` strategy which ensures that the query runs after the loading process has begun; this is so that the subqueryload takes precedence over other loaders that may be hitting the same attribute due to other eager/noload situations at the wrong time. [ticket:2887] --- test/orm/test_subquery_relations.py | 56 ++++++++++++++++++++++++++++++++++++- 1 file changed, 55 insertions(+), 1 deletion(-) (limited to 'test/orm/test_subquery_relations.py') diff --git a/test/orm/test_subquery_relations.py b/test/orm/test_subquery_relations.py index 3181e0909..2740b5cf0 100644 --- a/test/orm/test_subquery_relations.py +++ b/test/orm/test_subquery_relations.py @@ -10,6 +10,7 @@ from sqlalchemy.testing import eq_, assert_raises, \ assert_raises_message from sqlalchemy.testing.assertsql import CompiledSQL from sqlalchemy.testing import fixtures +from sqlalchemy.testing.entities import ComparableEntity from test.orm import _fixtures import sqlalchemy as sa @@ -1753,4 +1754,57 @@ class SubqueryloadDistinctTest(fixtures.DeclarativeMappedTest, [ (1, 'Woody Allen', 1), (1, 'Woody Allen', 1), ] - ) \ No newline at end of file + ) + + +class JoinedNoLoadConflictTest(fixtures.DeclarativeMappedTest): + """test for [ticket:2887]""" + + @classmethod + def setup_classes(cls): + Base = cls.DeclarativeBasic + + class Parent(ComparableEntity, Base): + __tablename__ = 'parent' + + id = Column(Integer, primary_key=True) + name = Column(String(20)) + + children = relationship('Child', + back_populates='parent', + lazy='noload' + ) + + class Child(ComparableEntity, Base): + __tablename__ = 'child' + + id = Column(Integer, primary_key=True) + name = Column(String(20)) + parent_id = Column(Integer, ForeignKey('parent.id')) + + parent = relationship('Parent', back_populates='children', lazy='joined') + + @classmethod + def insert_data(cls): + Parent = cls.classes.Parent + Child = cls.classes.Child + + s = Session() + s.add(Parent(name='parent', children=[Child(name='c1')])) + s.commit() + + def test_subqueryload_on_joined_noload(self): + Parent = self.classes.Parent + Child = self.classes.Child + + s = Session() + + # here we have Parent->subqueryload->Child->joinedload->parent->noload->children. + # the actual subqueryload has to emit *after* we've started populating + # Parent->subqueryload->child. + parent = s.query(Parent).options([subqueryload('children')]).first() + eq_( + parent.children, + [Child(name='c1')] + ) + -- cgit v1.2.1 From de2cfa2d1e949dfdd7ec4c34a1a4ff76f41ec888 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 17 Dec 2013 16:28:43 -0500 Subject: autoincrement here --- test/orm/test_subquery_relations.py | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'test/orm/test_subquery_relations.py') diff --git a/test/orm/test_subquery_relations.py b/test/orm/test_subquery_relations.py index 2740b5cf0..f36820e70 100644 --- a/test/orm/test_subquery_relations.py +++ b/test/orm/test_subquery_relations.py @@ -1767,7 +1767,7 @@ class JoinedNoLoadConflictTest(fixtures.DeclarativeMappedTest): class Parent(ComparableEntity, Base): __tablename__ = 'parent' - id = Column(Integer, primary_key=True) + id = Column(Integer, primary_key=True, test_needs_autoincrement=True) name = Column(String(20)) children = relationship('Child', @@ -1778,7 +1778,7 @@ class JoinedNoLoadConflictTest(fixtures.DeclarativeMappedTest): class Child(ComparableEntity, Base): __tablename__ = 'child' - id = Column(Integer, primary_key=True) + id = Column(Integer, primary_key=True, test_needs_autoincrement=True) name = Column(String(20)) parent_id = Column(Integer, ForeignKey('parent.id')) -- cgit v1.2.1