From f76cae4bc92da640c155337da5d089075ebae0d8 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Tue, 25 Jun 2013 17:32:51 -0400 Subject: - rework of correlation, continuing on #2668, #2746 - add support for correlations to propagate all the way in; because correlations require context now, need to make sure a select enclosure of any level takes effect any number of levels deep. - fix what we said correlate_except() was supposed to do when we first released #2668 - "the FROM clause is left intact if the correlated SELECT is not used in the context of an enclosing SELECT..." - it was not considering the "existing_froms" collection at all, and prohibited additional FROMs from being placed in an any() or has(). - add test for multilevel any() - lots of docs, including glossary entries as we really need to define "WHERE clause", "columns clause" etc. so that we can explain correlation better - based on the insight that a SELECT can correlate anything that ultimately came from an enclosing SELECT that links to this one via WHERE/columns/HAVING/ORDER BY, have the compiler keep track of the FROM lists that correspond in this way, link it to the asfrom flag, so that we send to _get_display_froms() the exact list of candidate FROMs to correlate. no longer need any asfrom logic in the Select() itself - preserve 0.8.1's behavior for correlation when no correlate options are given, not to mention 0.7 and prior's behavior of not propagating implicit correlation more than one level.. this is to reduce surprises/hard-to-debug situations when a user isn't trying to correlate anything. --- test/orm/test_query.py | 94 ++++++++++++++++++++++++++----------- test/sql/test_compiler.py | 110 +++++++++++++++++++++++++++++++++++++++----- test/sql/test_selectable.py | 44 +++++++++++++++++- 3 files changed, 210 insertions(+), 38 deletions(-) (limited to 'test') diff --git a/test/orm/test_query.py b/test/orm/test_query.py index c38fb351e..61ce39c04 100644 --- a/test/orm/test_query.py +++ b/test/orm/test_query.py @@ -622,8 +622,21 @@ class InvalidGenerationsTest(QueryTest, AssertsCompiledSQL): class OperatorTest(QueryTest, AssertsCompiledSQL): """test sql.Comparator implementation for MapperProperties""" - def _test(self, clause, expected): - self.assert_compile(clause, expected, dialect=default.DefaultDialect()) + __dialect__ = 'default' + + def _test(self, clause, expected, entity=None): + dialect = default.DefaultDialect() + if entity is not None: + # specify a lead entity, so that when we are testing + # correlation, the correlation actually happens + sess = Session() + lead = sess.query(entity) + context = lead._compile_context() + context.statement.use_labels = True + lead = context.statement.compile(dialect=dialect) + expected = (str(lead) + " WHERE " + expected).replace("\n", "") + clause = sess.query(entity).filter(clause) + self.assert_compile(clause, expected) def test_arithmetic(self): User = self.classes.User @@ -711,7 +724,8 @@ class OperatorTest(QueryTest, AssertsCompiledSQL): self._test(User.addresses.any(Address.id==17), "EXISTS (SELECT 1 " "FROM addresses " - "WHERE users.id = addresses.user_id AND addresses.id = :id_1)" + "WHERE users.id = addresses.user_id AND addresses.id = :id_1)", + entity=User ) u7 = User(id=7) @@ -719,21 +733,16 @@ class OperatorTest(QueryTest, AssertsCompiledSQL): self._test(Address.user == u7, ":param_1 = addresses.user_id") - self._test(Address.user != u7, "addresses.user_id != :user_id_1 OR addresses.user_id IS NULL") + self._test(Address.user != u7, + "addresses.user_id != :user_id_1 OR addresses.user_id IS NULL") self._test(Address.user == None, "addresses.user_id IS NULL") self._test(Address.user != None, "addresses.user_id IS NOT NULL") - def test_foo(self): - Node = self.classes.Node - nalias = aliased(Node) - self._test( - nalias.parent.has(Node.data=='some data'), - "EXISTS (SELECT 1 FROM nodes WHERE nodes.id = nodes_1.parent_id AND nodes.data = :data_1)" - ) def test_selfref_relationship(self): + Node = self.classes.Node nalias = aliased(Node) @@ -742,50 +751,62 @@ class OperatorTest(QueryTest, AssertsCompiledSQL): self._test( Node.children.any(Node.data=='n1'), "EXISTS (SELECT 1 FROM nodes AS nodes_1 WHERE " - "nodes.id = nodes_1.parent_id AND nodes_1.data = :data_1)" + "nodes.id = nodes_1.parent_id AND nodes_1.data = :data_1)", + entity=Node ) # needs autoaliasing self._test( - Node.children==None, - "NOT (EXISTS (SELECT 1 FROM nodes AS nodes_1 WHERE nodes.id = nodes_1.parent_id))" + Node.children == None, + "NOT (EXISTS (SELECT 1 FROM nodes AS nodes_1 " + "WHERE nodes.id = nodes_1.parent_id))", + entity=Node ) self._test( - Node.parent==None, + Node.parent == None, "nodes.parent_id IS NULL" ) self._test( - nalias.parent==None, + nalias.parent == None, "nodes_1.parent_id IS NULL" ) self._test( - nalias.children==None, - "NOT (EXISTS (SELECT 1 FROM nodes WHERE nodes_1.id = nodes.parent_id))" + nalias.children == None, + "NOT (EXISTS (SELECT 1 FROM nodes WHERE nodes_1.id = nodes.parent_id))", + entity=nalias ) self._test( nalias.children.any(Node.data=='some data'), "EXISTS (SELECT 1 FROM nodes WHERE " - "nodes_1.id = nodes.parent_id AND nodes.data = :data_1)") + "nodes_1.id = nodes.parent_id AND nodes.data = :data_1)", + entity=nalias) - # fails, but I think I want this to fail + # this fails because self-referential any() is auto-aliasing; + # the fact that we use "nalias" here means we get two aliases. #self._test( - # Node.children.any(nalias.data=='some data'), + # Node.children.any(nalias.data == 'some data'), # "EXISTS (SELECT 1 FROM nodes AS nodes_1 WHERE " - # "nodes.id = nodes_1.parent_id AND nodes_1.data = :data_1)" + # "nodes.id = nodes_1.parent_id AND nodes_1.data = :data_1)", + # entity=Node # ) self._test( - nalias.parent.has(Node.data=='some data'), - "EXISTS (SELECT 1 FROM nodes WHERE nodes.id = nodes_1.parent_id AND nodes.data = :data_1)" + nalias.parent.has(Node.data == 'some data'), + "EXISTS (SELECT 1 FROM nodes WHERE nodes.id = nodes_1.parent_id " + "AND nodes.data = :data_1)", + entity=nalias ) + self._test( - Node.parent.has(Node.data=='some data'), - "EXISTS (SELECT 1 FROM nodes AS nodes_1 WHERE nodes_1.id = nodes.parent_id AND nodes_1.data = :data_1)" + Node.parent.has(Node.data == 'some data'), + "EXISTS (SELECT 1 FROM nodes AS nodes_1 WHERE " + "nodes_1.id = nodes.parent_id AND nodes_1.data = :data_1)", + entity=Node ) self._test( @@ -807,6 +828,27 @@ class OperatorTest(QueryTest, AssertsCompiledSQL): nalias.children.contains(Node(id=7)), "nodes_1.id = :param_1" ) + def test_multilevel_any(self): + User, Address, Dingaling = \ + self.classes.User, self.classes.Address, self.classes.Dingaling + sess = Session() + + q = sess.query(User).filter( + User.addresses.any( + and_(Address.id == Dingaling.address_id, + Dingaling.data == 'x'))) + # new since #2746 - correlate_except() now takes context into account + # so its usage in any() is not as disrupting. + self.assert_compile(q, + "SELECT users.id AS users_id, users.name AS users_name " + "FROM users " + "WHERE EXISTS (SELECT 1 " + "FROM addresses, dingalings " + "WHERE users.id = addresses.user_id AND " + "addresses.id = dingalings.address_id AND " + "dingalings.data = :data_1)" + ) + def test_op(self): User = self.classes.User diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 473a422a2..8b9f9cfa4 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -2898,11 +2898,18 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): "SELECT t1.a, t2.a FROM t1, t2 WHERE t2.a = " "(SELECT t1.a WHERE t1.a = t2.a)") - def _assert_where_backwards_correlated(self, stmt): - self.assert_compile( - stmt, - "SELECT t2.a FROM t2 WHERE t2.a = " - "(SELECT t1.a FROM t2 WHERE t1.a = t2.a)") + # note there's no more "backwards" correlation after + # we've done #2746 + #def _assert_where_backwards_correlated(self, stmt): + # self.assert_compile( + # stmt, + # "SELECT t2.a FROM t2 WHERE t2.a = " + # "(SELECT t1.a FROM t2 WHERE t1.a = t2.a)") + + #def _assert_column_backwards_correlated(self, stmt): + # self.assert_compile(stmt, + # "SELECT t2.a, (SELECT t1.a FROM t2 WHERE t1.a = t2.a) " + # "AS anon_1 FROM t2") def _assert_column_correlated(self, stmt): self.assert_compile(stmt, @@ -2914,10 +2921,6 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): "SELECT t1.a, t2.a, " "(SELECT t1.a WHERE t1.a = t2.a) AS anon_1 FROM t1, t2") - def _assert_column_backwards_correlated(self, stmt): - self.assert_compile(stmt, - "SELECT t2.a, (SELECT t1.a FROM t2 WHERE t1.a = t2.a) " - "AS anon_1 FROM t2") def _assert_having_correlated(self, stmt): self.assert_compile(stmt, @@ -2980,7 +2983,7 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): def test_correlate_except_exclusion_where(self): t1, t2, s1 = self._fixture() - self._assert_where_backwards_correlated( + self._assert_where_uncorrelated( select([t2]).where(t2.c.a == s1.correlate_except(t2))) def test_correlate_except_inclusion_column(self): @@ -2990,7 +2993,7 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): def test_correlate_except_exclusion_column(self): t1, t2, s1 = self._fixture() - self._assert_column_backwards_correlated( + self._assert_column_uncorrelated( select([t2, s1.correlate_except(t2).as_scalar()])) def test_correlate_except_inclusion_from(self): @@ -3003,6 +3006,11 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): self._assert_from_uncorrelated( select([t2, s1.correlate_except(t2).alias()])) + def test_correlate_except_none(self): + t1, t2, s1 = self._fixture() + self._assert_where_all_correlated( + select([t1, t2]).where(t2.c.a == s1.correlate_except(None))) + def test_correlate_except_having(self): t1, t2, s1 = self._fixture() self._assert_having_correlated( @@ -3109,6 +3117,86 @@ class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): self.assert_compile(s1.correlate(t1, t2), "SELECT t1.a FROM t1, t2 WHERE t1.a = t2.a") + def test_correlate_except_froms(self): + # new as of #2748 + t1 = table('t1', column('a')) + t2 = table('t2', column('a'), column('b')) + s = select([t2.c.b]).where(t1.c.a == t2.c.a) + s = s.correlate_except(t2).alias('s') + + s2 = select([func.foo(s.c.b)]).as_scalar() + s3 = select([t1], order_by=s2) + + self.assert_compile(s3, + "SELECT t1.a FROM t1 ORDER BY " + "(SELECT foo(s.b) AS foo_1 FROM " + "(SELECT t2.b AS b FROM t2 WHERE t1.a = t2.a) AS s)" + ) + + def test_multilevel_froms_correlation(self): + # new as of #2748 + p = table('parent', column('id')) + c = table('child', column('id'), column('parent_id'), column('pos')) + + s = c.select().where(c.c.parent_id == p.c.id).order_by(c.c.pos).limit(1) + s = s.correlate(p) + s = exists().select_from(s).where(s.c.id == 1) + s = select([p]).where(s) + self.assert_compile(s, + "SELECT parent.id FROM parent WHERE EXISTS (SELECT * " + "FROM (SELECT child.id AS id, child.parent_id AS parent_id, " + "child.pos AS pos FROM child WHERE child.parent_id = parent.id " + "ORDER BY child.pos LIMIT :param_1) WHERE id = :id_1)") + + def test_no_contextless_correlate_except(self): + # new as of #2748 + + t1 = table('t1', column('x')) + t2 = table('t2', column('y')) + t3 = table('t3', column('z')) + + s = select([t1]).where(t1.c.x == t2.c.y).\ + where(t2.c.y == t3.c.z).correlate_except(t1) + self.assert_compile(s, + "SELECT t1.x FROM t1, t2, t3 WHERE t1.x = t2.y AND t2.y = t3.z") + + def test_multilevel_implicit_correlation_disabled(self): + # test that implicit correlation with multilevel WHERE correlation + # behaves like 0.8.1, 0.7 (i.e. doesn't happen) + t1 = table('t1', column('x')) + t2 = table('t2', column('y')) + t3 = table('t3', column('z')) + + s = select([t1.c.x]).where(t1.c.x == t2.c.y) + s2 = select([t3.c.z]).where(t3.c.z == s.as_scalar()) + s3 = select([t1]).where(t1.c.x == s2.as_scalar()) + + self.assert_compile(s3, + "SELECT t1.x FROM t1 " + "WHERE t1.x = (SELECT t3.z " + "FROM t3 " + "WHERE t3.z = (SELECT t1.x " + "FROM t1, t2 " + "WHERE t1.x = t2.y))" + ) + + def test_from_implicit_correlation_disabled(self): + # test that implicit correlation with immediate and + # multilevel FROM clauses behaves like 0.8.1 (i.e. doesn't happen) + t1 = table('t1', column('x')) + t2 = table('t2', column('y')) + t3 = table('t3', column('z')) + + s = select([t1.c.x]).where(t1.c.x == t2.c.y) + s2 = select([t2, s]) + s3 = select([t1, s2]) + + self.assert_compile(s3, + "SELECT t1.x, y, x FROM t1, " + "(SELECT t2.y AS y, x FROM t2, " + "(SELECT t1.x AS x FROM t1, t2 WHERE t1.x = t2.y))" + ) + class CoercionTest(fixtures.TestBase, AssertsCompiledSQL): __dialect__ = 'default' diff --git a/test/sql/test_selectable.py b/test/sql/test_selectable.py index 6a0511faa..335083ce1 100644 --- a/test/sql/test_selectable.py +++ b/test/sql/test_selectable.py @@ -6,7 +6,7 @@ from sqlalchemy import * from sqlalchemy.testing import fixtures, AssertsCompiledSQL, \ AssertsExecutionResults from sqlalchemy import testing -from sqlalchemy.sql import util as sql_util, visitors +from sqlalchemy.sql import util as sql_util, visitors, expression from sqlalchemy import exc from sqlalchemy.sql import table, column, null from sqlalchemy import util @@ -148,6 +148,48 @@ class SelectableTest(fixtures.TestBase, AssertsExecutionResults, AssertsCompiled s = select([t])._clone() assert c in s.c.bar.proxy_set + def test_cloned_intersection(self): + t1 = table('t1', column('x')) + t2 = table('t2', column('x')) + + s1 = t1.select() + s2 = t2.select() + s3 = t1.select() + + s1c1 = s1._clone() + s1c2 = s1._clone() + s2c1 = s2._clone() + s3c1 = s3._clone() + + eq_( + expression._cloned_intersection( + [s1c1, s3c1], [s2c1, s1c2] + ), + set([s1c1]) + ) + + def test_cloned_difference(self): + t1 = table('t1', column('x')) + t2 = table('t2', column('x')) + + s1 = t1.select() + s2 = t2.select() + s3 = t1.select() + + s1c1 = s1._clone() + s1c2 = s1._clone() + s2c1 = s2._clone() + s2c2 = s2._clone() + s3c1 = s3._clone() + + eq_( + expression._cloned_difference( + [s1c1, s2c1, s3c1], [s2c1, s1c2] + ), + set([s3c1]) + ) + + def test_distance_on_aliases(self): a1 = table1.alias('a1') for s in (select([a1, table1], use_labels=True), -- cgit v1.2.1