diff options
| -rw-r--r-- | doc/build/changelog/changelog_08.rst | 20 | ||||
| -rw-r--r-- | doc/build/changelog/migration_08.rst | 56 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 9 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/expression.py | 43 | ||||
| -rw-r--r-- | test/orm/test_froms.py | 6 | ||||
| -rw-r--r-- | test/orm/test_query.py | 29 | ||||
| -rw-r--r-- | test/sql/test_compiler.py | 272 | ||||
| -rw-r--r-- | test/sql/test_generative.py | 293 |
8 files changed, 523 insertions, 205 deletions
diff --git a/doc/build/changelog/changelog_08.rst b/doc/build/changelog/changelog_08.rst index c71f3be0c..8c34dab09 100644 --- a/doc/build/changelog/changelog_08.rst +++ b/doc/build/changelog/changelog_08.rst @@ -8,14 +8,30 @@ .. note:: - Be sure to *re-read* :doc:`migration_08` for this release. There are some new behavioral changes as of 0.8.0 - not present in 0.8.0b2, including: + not present in 0.8.0b2. They are present in the + migration document as follows: * :ref:`legacy_is_orphan_addition` * :ref:`metadata_create_drop_tables` + * :ref:`correlation_context_specific` + + .. change:: + :tags: bug, sql + :tickets: 2668 + + The behavior of SELECT correlation has been improved such that + the :meth:`.Select.correlate` and :meth:`.Select.correlate_except` + methods, as well as their ORM analogues, will still retain + "auto-correlation" behavior in that the FROM clause is modified + only if the output would be legal SQL; that is, the FROM clause + is left intact if the correlated SELECT is not used in the context + of an enclosing SELECT inside of the WHERE, columns, or HAVING clause. + The two methods now only specify conditions to the default + "auto correlation", rather than absolute FROM lists. + .. change:: :tags: feature, mysql :pullreq: 42 diff --git a/doc/build/changelog/migration_08.rst b/doc/build/changelog/migration_08.rst index bb6d85e58..971dd2f51 100644 --- a/doc/build/changelog/migration_08.rst +++ b/doc/build/changelog/migration_08.rst @@ -7,9 +7,10 @@ What's New in SQLAlchemy 0.8? This document describes changes between SQLAlchemy version 0.7, undergoing maintenance releases as of October, 2012, and SQLAlchemy version 0.8, which is expected for release - in late 2012. + in early 2013. Document date: October 25, 2012 + Updated: March 9, 2013 Introduction ============ @@ -1145,6 +1146,59 @@ entity, ``query.correlate(someentity)``. :ticket:`2179` +.. _correlation_context_specific: + +Correlation is now always context-specific +------------------------------------------ + +To allow a wider variety of correlation scenarios, the behavior of +:meth:`.Select.correlate` and :meth:`.Query.correlate` has changed slightly +such that the SELECT statement will omit the "correlated" target from the +FROM clause only if the statement is actually used in that context. Additionally, +it's no longer possible for a SELECT statement that's placed as a FROM +in an enclosing SELECT statement to "correlate" (i.e. omit) a FROM clause. + +This change only makes things better as far as rendering SQL, in that it's no +longer possible to render illegal SQL where there are insufficient FROM +objects relative to what's being selected:: + + from sqlalchemy.sql import table, column, select + + t1 = table('t1', column('x')) + t2 = table('t2', column('y')) + s = select([t1, t2]).correlate(t1) + + print(s) + +Prior to this change, the above would return:: + + SELECT t1.x, t2.y FROM t2 + +which is invalid SQL as "t1" is not referred to in any FROM clause. + +Now, in the absense of an enclosing SELECT, it returns:: + + SELECT t1.x, t2.y FROM t1, t2 + +Within a SELECT, the correlation takes effect as expected:: + + s2 = select([t1, t2]).where(t1.c.x == t2.c.y).where(t1.c.x == s) + + print (s2) + + SELECT t1.x, t2.y FROM t1, t2 + WHERE t1.x = t2.y AND t1.x = + (SELECT t1.x, t2.y FROM t2) + +This change is not expected to impact any existing applications, as +the correlation behavior remains identical for properly constructed +expressions. Only an application that relies, most likely within a +testing scenario, on the invalid string output of a correlated +SELECT used in a non-correlating context would see any change. + +:ticket:`2668` + + .. _metadata_create_drop_tables: create_all() and drop_all() will now honor an empty list as such diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 59e46de12..90e906727 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -1086,14 +1086,9 @@ class SQLCompiler(engine.Compiled): positional_names=None, **kwargs): entry = self.stack and self.stack[-1] or {} - if not asfrom: - existingfroms = entry.get('from', None) - else: - # don't render correlations if we're rendering a FROM list - # entry - existingfroms = [] + existingfroms = entry.get('from', None) - froms = select._get_display_froms(existingfroms) + froms = select._get_display_froms(existingfroms, asfrom=asfrom) correlate_froms = set(sql._from_objects(*froms)) diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 490004e39..41eaace71 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -4980,7 +4980,7 @@ class CompoundSelect(SelectBase): INTERSECT_ALL = util.symbol('INTERSECT ALL') def __init__(self, keyword, *selects, **kwargs): - self._should_correlate = kwargs.pop('correlate', False) + self._auto_correlate = kwargs.pop('correlate', False) self.keyword = keyword self.selects = [] @@ -5159,7 +5159,7 @@ class Select(HasPrefixes, SelectBase): :class:`SelectBase` superclass. """ - self._should_correlate = correlate + self._auto_correlate = correlate if distinct is not False: if distinct is True: self._distinct = True @@ -5232,7 +5232,7 @@ class Select(HasPrefixes, SelectBase): return froms - def _get_display_froms(self, existing_froms=None): + def _get_display_froms(self, existing_froms=None, asfrom=False): """Return the full list of 'from' clauses to be displayed. Takes into account a set of existing froms which may be @@ -5258,18 +5258,29 @@ class Select(HasPrefixes, SelectBase): # using a list to maintain ordering froms = [f for f in froms if f not in toremove] - if len(froms) > 1 or self._correlate or self._correlate_except: + if not asfrom: if self._correlate: - froms = [f for f in froms if f not in - _cloned_intersection(froms, - self._correlate)] + froms = [ + f for f in froms if f not in + _cloned_intersection( + _cloned_intersection(froms, existing_froms or ()), + self._correlate + ) + ] if self._correlate_except: - froms = [f for f in froms if f in _cloned_intersection(froms, - self._correlate_except)] - if self._should_correlate and existing_froms: - froms = [f for f in froms if f not in - _cloned_intersection(froms, - existing_froms)] + froms = [ + f for f in froms if f in + _cloned_intersection( + froms, + self._correlate_except + ) + ] + + if self._auto_correlate and existing_froms and len(froms) > 1: + froms = [ + f for f in froms if f not in + _cloned_intersection(froms, existing_froms) + ] if not len(froms): raise exc.InvalidRequestError("Select statement '%s" @@ -5642,7 +5653,7 @@ class Select(HasPrefixes, SelectBase): :ref:`correlated_subqueries` """ - self._should_correlate = False + self._auto_correlate = False if fromclauses and fromclauses[0] is None: self._correlate = () else: @@ -5662,7 +5673,7 @@ class Select(HasPrefixes, SelectBase): :ref:`correlated_subqueries` """ - self._should_correlate = False + self._auto_correlate = False if fromclauses and fromclauses[0] is None: self._correlate_except = () else: @@ -5673,7 +5684,7 @@ class Select(HasPrefixes, SelectBase): """append the given correlation expression to this select() construct.""" - self._should_correlate = False + self._auto_correlate = False self._correlate = set(self._correlate).union( _interpret_as_from(f) for f in fromclause) diff --git a/test/orm/test_froms.py b/test/orm/test_froms.py index 4c566948a..b98333e3d 100644 --- a/test/orm/test_froms.py +++ b/test/orm/test_froms.py @@ -174,9 +174,7 @@ class RawSelectTest(QueryTest, AssertsCompiledSQL): ) # a little tedious here, adding labels to work around Query's - # auto-labelling. TODO: can we detect only one table in the - # "froms" and then turn off use_labels ? note: this query is - # incorrect SQL with the correlate of users in the FROM list. + # auto-labelling. s = sess.query(addresses.c.id.label('id'), addresses.c.email_address.label('email')).\ filter(addresses.c.user_id == users.c.id).correlate(users).\ @@ -188,7 +186,7 @@ class RawSelectTest(QueryTest, AssertsCompiledSQL): "SELECT users.id AS users_id, users.name AS users_name, " "anon_1.email AS anon_1_email " "FROM users JOIN (SELECT addresses.id AS id, " - "addresses.email_address AS email FROM addresses " + "addresses.email_address AS email FROM addresses, users " "WHERE addresses.user_id = users.id) AS anon_1 " "ON anon_1.id = users.id", ) diff --git a/test/orm/test_query.py b/test/orm/test_query.py index f418d2581..ac9c95f41 100644 --- a/test/orm/test_query.py +++ b/test/orm/test_query.py @@ -194,22 +194,33 @@ class RawSelectTest(QueryTest, AssertsCompiledSQL): Address = self.classes.Address self.assert_compile( - select([User]).where(User.id == Address.user_id). - correlate(Address), - "SELECT users.id, users.name FROM users " - "WHERE users.id = addresses.user_id" + select([User.name, Address.id, + select([func.count(Address.id)]).\ + where(User.id == Address.user_id).\ + correlate(User).as_scalar() + ]), + "SELECT users.name, addresses.id, " + "(SELECT count(addresses.id) AS count_1 " + "FROM addresses WHERE users.id = addresses.user_id) AS anon_1 " + "FROM users, addresses" ) def test_correlate_aliased_entity(self): User = self.classes.User Address = self.classes.Address - aa = aliased(Address, name="aa") + uu = aliased(User, name="uu") self.assert_compile( - select([User]).where(User.id == aa.user_id). - correlate(aa), - "SELECT users.id, users.name FROM users " - "WHERE users.id = aa.user_id" + select([uu.name, Address.id, + select([func.count(Address.id)]).\ + where(uu.id == Address.user_id).\ + correlate(uu).as_scalar() + ]), + # curious, "address.user_id = uu.id" is reversed here + "SELECT uu.name, addresses.id, " + "(SELECT count(addresses.id) AS count_1 " + "FROM addresses WHERE addresses.user_id = uu.id) AS anon_1 " + "FROM users AS uu, addresses" ) def test_columns_clause_entity(self): diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 3b8aed23f..fe52402ec 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -87,6 +87,7 @@ keyed = Table('keyed', metadata, Column('z', Integer), ) + class SelectTest(fixtures.TestBase, AssertsCompiledSQL): __dialect__ = 'default' @@ -424,35 +425,6 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): "AS z FROM keyed) AS anon_2) AS anon_1" ) - def test_dont_overcorrelate(self): - self.assert_compile(select([table1], from_obj=[table1, - table1.select()]), - "SELECT mytable.myid, mytable.name, " - "mytable.description FROM mytable, (SELECT " - "mytable.myid AS myid, mytable.name AS " - "name, mytable.description AS description " - "FROM mytable)") - - def test_full_correlate(self): - # intentional - t = table('t', column('a'), column('b')) - s = select([t.c.a]).where(t.c.a == 1).correlate(t).as_scalar() - - s2 = select([t.c.a, s]) - self.assert_compile(s2, - "SELECT t.a, (SELECT t.a WHERE t.a = :a_1) AS anon_1 FROM t") - - # unintentional - t2 = table('t2', column('c'), column('d')) - s = select([t.c.a]).where(t.c.a == t2.c.d).as_scalar() - s2 = select([t, t2, s]) - assert_raises(exc.InvalidRequestError, str, s2) - - # intentional again - s = s.correlate(t, t2) - s2 = select([t, t2, s]) - self.assert_compile(s, "SELECT t.a WHERE t.a = t2.d") - def test_exists(self): s = select([table1.c.myid]).where(table1.c.myid == 5) @@ -3189,6 +3161,246 @@ class SchemaTest(fixtures.TestBase, AssertsCompiledSQL): "(:rem_id, :datatype_id, :value)") +class CorrelateTest(fixtures.TestBase, AssertsCompiledSQL): + __dialect__ = 'default' + + def test_dont_overcorrelate(self): + self.assert_compile(select([table1], from_obj=[table1, + table1.select()]), + "SELECT mytable.myid, mytable.name, " + "mytable.description FROM mytable, (SELECT " + "mytable.myid AS myid, mytable.name AS " + "name, mytable.description AS description " + "FROM mytable)") + + def _fixture(self): + t1 = table('t1', column('a')) + t2 = table('t2', column('a')) + return t1, t2, select([t1]).where(t1.c.a == t2.c.a) + + def _assert_where_correlated(self, stmt): + self.assert_compile( + stmt, + "SELECT t2.a FROM t2 WHERE t2.a = " + "(SELECT t1.a FROM t1 WHERE t1.a = t2.a)") + + def _assert_where_all_correlated(self, stmt): + self.assert_compile( + stmt, + "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)") + + def _assert_column_correlated(self, stmt): + self.assert_compile(stmt, + "SELECT t2.a, (SELECT t1.a FROM t1 WHERE t1.a = t2.a) " + "AS anon_1 FROM t2") + + def _assert_column_all_correlated(self, stmt): + self.assert_compile(stmt, + "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, + "SELECT t2.a FROM t2 HAVING t2.a = " + "(SELECT t1.a FROM t1 WHERE t1.a = t2.a)") + + def _assert_from_uncorrelated(self, stmt): + self.assert_compile(stmt, + "SELECT t2.a, anon_1.a FROM t2, " + "(SELECT t1.a AS a FROM t1, t2 WHERE t1.a = t2.a) AS anon_1") + + def _assert_from_all_uncorrelated(self, stmt): + self.assert_compile(stmt, + "SELECT t1.a, t2.a, anon_1.a FROM t1, t2, " + "(SELECT t1.a AS a FROM t1, t2 WHERE t1.a = t2.a) AS anon_1") + + def _assert_where_uncorrelated(self, stmt): + self.assert_compile(stmt, + "SELECT t2.a FROM t2 WHERE t2.a = " + "(SELECT t1.a FROM t1, t2 WHERE t1.a = t2.a)") + + def _assert_column_uncorrelated(self, stmt): + self.assert_compile(stmt, + "SELECT t2.a, (SELECT t1.a FROM t1, t2 " + "WHERE t1.a = t2.a) AS anon_1 FROM t2") + + def _assert_having_uncorrelated(self, stmt): + self.assert_compile(stmt, + "SELECT t2.a FROM t2 HAVING t2.a = " + "(SELECT t1.a FROM t1, t2 WHERE t1.a = t2.a)") + + def _assert_where_single_full_correlated(self, stmt): + self.assert_compile(stmt, + "SELECT t1.a FROM t1 WHERE t1.a = (SELECT t1.a)") + + def test_correlate_semiauto_where(self): + t1, t2, s1 = self._fixture() + self._assert_where_correlated( + select([t2]).where(t2.c.a == s1.correlate(t2))) + + def test_correlate_semiauto_column(self): + t1, t2, s1 = self._fixture() + self._assert_column_correlated( + select([t2, s1.correlate(t2).as_scalar()])) + + def test_correlate_semiauto_from(self): + t1, t2, s1 = self._fixture() + self._assert_from_uncorrelated( + select([t2, s1.correlate(t2).alias()])) + + def test_correlate_semiauto_having(self): + t1, t2, s1 = self._fixture() + self._assert_having_correlated( + select([t2]).having(t2.c.a == s1.correlate(t2))) + + def test_correlate_except_inclusion_where(self): + t1, t2, s1 = self._fixture() + self._assert_where_correlated( + select([t2]).where(t2.c.a == s1.correlate_except(t1))) + + def test_correlate_except_exclusion_where(self): + t1, t2, s1 = self._fixture() + self._assert_where_backwards_correlated( + select([t2]).where(t2.c.a == s1.correlate_except(t2))) + + def test_correlate_except_inclusion_column(self): + t1, t2, s1 = self._fixture() + self._assert_column_correlated( + select([t2, s1.correlate_except(t1).as_scalar()])) + + def test_correlate_except_exclusion_column(self): + t1, t2, s1 = self._fixture() + self._assert_column_backwards_correlated( + select([t2, s1.correlate_except(t2).as_scalar()])) + + def test_correlate_except_inclusion_from(self): + t1, t2, s1 = self._fixture() + self._assert_from_uncorrelated( + select([t2, s1.correlate_except(t1).alias()])) + + def test_correlate_except_exclusion_from(self): + t1, t2, s1 = self._fixture() + self._assert_from_uncorrelated( + select([t2, s1.correlate_except(t2).alias()])) + + def test_correlate_except_having(self): + t1, t2, s1 = self._fixture() + self._assert_having_correlated( + select([t2]).having(t2.c.a == s1.correlate_except(t1))) + + def test_correlate_auto_where(self): + t1, t2, s1 = self._fixture() + self._assert_where_correlated( + select([t2]).where(t2.c.a == s1)) + + def test_correlate_auto_column(self): + t1, t2, s1 = self._fixture() + self._assert_column_correlated( + select([t2, s1.as_scalar()])) + + def test_correlate_auto_from(self): + t1, t2, s1 = self._fixture() + self._assert_from_uncorrelated( + select([t2, s1.alias()])) + + def test_correlate_auto_having(self): + t1, t2, s1 = self._fixture() + self._assert_having_correlated( + select([t2]).having(t2.c.a == s1)) + + def test_correlate_disabled_where(self): + t1, t2, s1 = self._fixture() + self._assert_where_uncorrelated( + select([t2]).where(t2.c.a == s1.correlate(None))) + + def test_correlate_disabled_column(self): + t1, t2, s1 = self._fixture() + self._assert_column_uncorrelated( + select([t2, s1.correlate(None).as_scalar()])) + + def test_correlate_disabled_from(self): + t1, t2, s1 = self._fixture() + self._assert_from_uncorrelated( + select([t2, s1.correlate(None).alias()])) + + def test_correlate_disabled_having(self): + t1, t2, s1 = self._fixture() + self._assert_having_uncorrelated( + select([t2]).having(t2.c.a == s1.correlate(None))) + + def test_correlate_all_where(self): + t1, t2, s1 = self._fixture() + self._assert_where_all_correlated( + select([t1, t2]).where(t2.c.a == s1.correlate(t1, t2))) + + def test_correlate_all_column(self): + t1, t2, s1 = self._fixture() + self._assert_column_all_correlated( + select([t1, t2, s1.correlate(t1, t2).as_scalar()])) + + def test_correlate_all_from(self): + t1, t2, s1 = self._fixture() + self._assert_from_all_uncorrelated( + select([t1, t2, s1.correlate(t1, t2).alias()])) + + def test_correlate_where_all_unintentional(self): + t1, t2, s1 = self._fixture() + assert_raises_message( + exc.InvalidRequestError, + "returned no FROM clauses due to auto-correlation", + select([t1, t2]).where(t2.c.a == s1).compile + ) + + def test_correlate_from_all_ok(self): + t1, t2, s1 = self._fixture() + self.assert_compile( + select([t1, t2, s1]), + "SELECT t1.a, t2.a, a FROM t1, t2, " + "(SELECT t1.a AS a FROM t1, t2 WHERE t1.a = t2.a)" + ) + + def test_correlate_auto_where_singlefrom(self): + t1, t2, s1 = self._fixture() + s = select([t1.c.a]) + s2 = select([t1]).where(t1.c.a == s) + self.assert_compile(s2, + "SELECT t1.a FROM t1 WHERE t1.a = " + "(SELECT t1.a FROM t1)") + + def test_correlate_semiauto_where_singlefrom(self): + t1, t2, s1 = self._fixture() + + s = select([t1.c.a]) + + s2 = select([t1]).where(t1.c.a == s.correlate(t1)) + self._assert_where_single_full_correlated(s2) + + def test_correlate_except_semiauto_where_singlefrom(self): + t1, t2, s1 = self._fixture() + + s = select([t1.c.a]) + + s2 = select([t1]).where(t1.c.a == s.correlate_except(t2)) + self._assert_where_single_full_correlated(s2) + + def test_correlate_alone_noeffect(self): + # new as of #2668 + t1, t2, s1 = self._fixture() + self.assert_compile(s1.correlate(t1, t2), + "SELECT t1.a FROM t1, t2 WHERE t1.a = t2.a") + class CoercionTest(fixtures.TestBase, AssertsCompiledSQL): __dialect__ = 'default' @@ -3315,4 +3527,4 @@ class ResultMapTest(fixtures.TestBase): ) is_( comp.result_map['t1_a'][1][2], t1.c.a - )
\ No newline at end of file + ) diff --git a/test/sql/test_generative.py b/test/sql/test_generative.py index e868cbe88..8b2abef0e 100644 --- a/test/sql/test_generative.py +++ b/test/sql/test_generative.py @@ -590,13 +590,18 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL): def test_correlated_select(self): s = select(['*'], t1.c.col1 == t2.c.col1, from_obj=[t1, t2]).correlate(t2) + class Vis(CloningVisitor): def visit_select(self, select): select.append_whereclause(t1.c.col2 == 7) - self.assert_compile(Vis().traverse(s), - "SELECT * FROM table1 WHERE table1.col1 = table2.col1 " - "AND table1.col2 = :col2_1") + self.assert_compile( + select([t2]).where(t2.c.col1 == Vis().traverse(s)), + "SELECT table2.col1, table2.col2, table2.col3 " + "FROM table2 WHERE table2.col1 = " + "(SELECT * FROM table1 WHERE table1.col1 = table2.col1 " + "AND table1.col2 = :col2_1)" + ) def test_this_thing(self): s = select([t1]).where(t1.c.col1 == 'foo').alias() @@ -616,35 +621,49 @@ class ClauseTest(fixtures.TestBase, AssertsCompiledSQL): 'AS table1_1 WHERE table1_1.col1 = ' ':col1_1) AS anon_1') - def test_select_fromtwice(self): + def test_select_fromtwice_one(self): t1a = t1.alias() - s = select([1], t1.c.col1 == t1a.c.col1, from_obj=t1a).correlate(t1) + s = select([1], t1.c.col1 == t1a.c.col1, from_obj=t1a).correlate(t1a) + s = select([t1]).where(t1.c.col1 == s) self.assert_compile(s, - 'SELECT 1 FROM table1 AS table1_1 WHERE ' - 'table1.col1 = table1_1.col1') - + "SELECT table1.col1, table1.col2, table1.col3 FROM table1 " + "WHERE table1.col1 = " + "(SELECT 1 FROM table1, table1 AS table1_1 " + "WHERE table1.col1 = table1_1.col1)" + ) s = CloningVisitor().traverse(s) self.assert_compile(s, - 'SELECT 1 FROM table1 AS table1_1 WHERE ' - 'table1.col1 = table1_1.col1') + "SELECT table1.col1, table1.col2, table1.col3 FROM table1 " + "WHERE table1.col1 = " + "(SELECT 1 FROM table1, table1 AS table1_1 " + "WHERE table1.col1 = table1_1.col1)") + def test_select_fromtwice_two(self): s = select([t1]).where(t1.c.col1 == 'foo').alias() s2 = select([1], t1.c.col1 == s.c.col1, from_obj=s).correlate(t1) - self.assert_compile(s2, - 'SELECT 1 FROM (SELECT table1.col1 AS ' - 'col1, table1.col2 AS col2, table1.col3 AS ' - 'col3 FROM table1 WHERE table1.col1 = ' - ':col1_1) AS anon_1 WHERE table1.col1 = ' - 'anon_1.col1') - s2 = ReplacingCloningVisitor().traverse(s2) - self.assert_compile(s2, - 'SELECT 1 FROM (SELECT table1.col1 AS ' - 'col1, table1.col2 AS col2, table1.col3 AS ' - 'col3 FROM table1 WHERE table1.col1 = ' - ':col1_1) AS anon_1 WHERE table1.col1 = ' - 'anon_1.col1') + s3 = select([t1]).where(t1.c.col1 == s2) + self.assert_compile(s3, + "SELECT table1.col1, table1.col2, table1.col3 " + "FROM table1 WHERE table1.col1 = " + "(SELECT 1 FROM " + "(SELECT table1.col1 AS col1, table1.col2 AS col2, " + "table1.col3 AS col3 FROM table1 " + "WHERE table1.col1 = :col1_1) " + "AS anon_1 WHERE table1.col1 = anon_1.col1)" + ) + + s4 = ReplacingCloningVisitor().traverse(s3) + self.assert_compile(s4, + "SELECT table1.col1, table1.col2, table1.col3 " + "FROM table1 WHERE table1.col1 = " + "(SELECT 1 FROM " + "(SELECT table1.col1 AS col1, table1.col2 AS col2, " + "table1.col3 AS col3 FROM table1 " + "WHERE table1.col1 = :col1_1) " + "AS anon_1 WHERE table1.col1 = anon_1.col1)" + ) class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): __dialect__ = 'default' @@ -763,67 +782,125 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): 'FROM addresses WHERE users_1.id = ' 'addresses.user_id') - def test_table_to_alias(self): - + def test_table_to_alias_1(self): t1alias = t1.alias('t1alias') vis = sql_util.ClauseAdapter(t1alias) ff = vis.traverse(func.count(t1.c.col1).label('foo')) assert list(_from_objects(ff)) == [t1alias] + def test_table_to_alias_2(self): + t1alias = t1.alias('t1alias') + vis = sql_util.ClauseAdapter(t1alias) self.assert_compile(vis.traverse(select(['*'], from_obj=[t1])), 'SELECT * FROM table1 AS t1alias') + + def test_table_to_alias_3(self): + t1alias = t1.alias('t1alias') + vis = sql_util.ClauseAdapter(t1alias) self.assert_compile(select(['*'], t1.c.col1 == t2.c.col2), 'SELECT * FROM table1, table2 WHERE ' 'table1.col1 = table2.col2') + + def test_table_to_alias_4(self): + t1alias = t1.alias('t1alias') + vis = sql_util.ClauseAdapter(t1alias) self.assert_compile(vis.traverse(select(['*'], t1.c.col1 == t2.c.col2)), 'SELECT * FROM table1 AS t1alias, table2 ' 'WHERE t1alias.col1 = table2.col2') + + def test_table_to_alias_5(self): + t1alias = t1.alias('t1alias') + vis = sql_util.ClauseAdapter(t1alias) self.assert_compile(vis.traverse(select(['*'], t1.c.col1 == t2.c.col2, from_obj=[t1, t2])), 'SELECT * FROM table1 AS t1alias, table2 ' 'WHERE t1alias.col1 = table2.col2') - self.assert_compile(vis.traverse(select(['*'], t1.c.col1 - == t2.c.col2, from_obj=[t1, - t2]).correlate(t1)), - 'SELECT * FROM table2 WHERE t1alias.col1 = ' - 'table2.col2') - self.assert_compile(vis.traverse(select(['*'], t1.c.col1 - == t2.c.col2, from_obj=[t1, - t2]).correlate(t2)), - 'SELECT * FROM table1 AS t1alias WHERE ' - 't1alias.col1 = table2.col2') + + def test_table_to_alias_6(self): + t1alias = t1.alias('t1alias') + vis = sql_util.ClauseAdapter(t1alias) + self.assert_compile( + select([t1alias, t2]).where(t1alias.c.col1 == + vis.traverse(select(['*'], + t1.c.col1 == t2.c.col2, + from_obj=[t1, t2]).correlate(t1))), + "SELECT t1alias.col1, t1alias.col2, t1alias.col3, " + "table2.col1, table2.col2, table2.col3 " + "FROM table1 AS t1alias, table2 WHERE t1alias.col1 = " + "(SELECT * FROM table2 WHERE t1alias.col1 = table2.col2)" + ) + + def test_table_to_alias_7(self): + t1alias = t1.alias('t1alias') + vis = sql_util.ClauseAdapter(t1alias) + self.assert_compile( + select([t1alias, t2]).where(t1alias.c.col1 == + vis.traverse(select(['*'], + t1.c.col1 == t2.c.col2, + from_obj=[t1, t2]).correlate(t2))), + "SELECT t1alias.col1, t1alias.col2, t1alias.col3, " + "table2.col1, table2.col2, table2.col3 " + "FROM table1 AS t1alias, table2 " + "WHERE t1alias.col1 = " + "(SELECT * FROM table1 AS t1alias " + "WHERE t1alias.col1 = table2.col2)") + + def test_table_to_alias_8(self): + t1alias = t1.alias('t1alias') + vis = sql_util.ClauseAdapter(t1alias) self.assert_compile(vis.traverse(case([(t1.c.col1 == 5, t1.c.col2)], else_=t1.c.col1)), 'CASE WHEN (t1alias.col1 = :col1_1) THEN ' 't1alias.col2 ELSE t1alias.col1 END') + + def test_table_to_alias_9(self): + t1alias = t1.alias('t1alias') + vis = sql_util.ClauseAdapter(t1alias) self.assert_compile(vis.traverse(case([(5, t1.c.col2)], value=t1.c.col1, else_=t1.c.col1)), 'CASE t1alias.col1 WHEN :param_1 THEN ' 't1alias.col2 ELSE t1alias.col1 END') + def test_table_to_alias_10(self): s = select(['*'], from_obj=[t1]).alias('foo') self.assert_compile(s.select(), 'SELECT foo.* FROM (SELECT * FROM table1) ' 'AS foo') + + def test_table_to_alias_11(self): + s = select(['*'], from_obj=[t1]).alias('foo') + t1alias = t1.alias('t1alias') + vis = sql_util.ClauseAdapter(t1alias) self.assert_compile(vis.traverse(s.select()), 'SELECT foo.* FROM (SELECT * FROM table1 ' 'AS t1alias) AS foo') + + def test_table_to_alias_12(self): + s = select(['*'], from_obj=[t1]).alias('foo') self.assert_compile(s.select(), 'SELECT foo.* FROM (SELECT * FROM table1) ' 'AS foo') + + def test_table_to_alias_13(self): + t1alias = t1.alias('t1alias') + vis = sql_util.ClauseAdapter(t1alias) ff = vis.traverse(func.count(t1.c.col1).label('foo')) self.assert_compile(select([ff]), 'SELECT count(t1alias.col1) AS foo FROM ' 'table1 AS t1alias') assert list(_from_objects(ff)) == [t1alias] + #def test_table_to_alias_2(self): # TODO: self.assert_compile(vis.traverse(select([func.count(t1.c # .col1).l abel('foo')]), clone=True), "SELECT # count(t1alias.col1) AS foo FROM table1 AS t1alias") + def test_table_to_alias_14(self): + t1alias = t1.alias('t1alias') + vis = sql_util.ClauseAdapter(t1alias) t2alias = t2.alias('t2alias') vis.chain(sql_util.ClauseAdapter(t2alias)) self.assert_compile(vis.traverse(select(['*'], t1.c.col1 @@ -831,28 +908,59 @@ class ClauseAdapterTest(fixtures.TestBase, AssertsCompiledSQL): 'SELECT * FROM table1 AS t1alias, table2 ' 'AS t2alias WHERE t1alias.col1 = ' 't2alias.col2') + + def test_table_to_alias_15(self): + t1alias = t1.alias('t1alias') + vis = sql_util.ClauseAdapter(t1alias) + t2alias = t2.alias('t2alias') + vis.chain(sql_util.ClauseAdapter(t2alias)) self.assert_compile(vis.traverse(select(['*'], t1.c.col1 == t2.c.col2, from_obj=[t1, t2])), 'SELECT * FROM table1 AS t1alias, table2 ' 'AS t2alias WHERE t1alias.col1 = ' 't2alias.col2') - self.assert_compile(vis.traverse(select(['*'], t1.c.col1 - == t2.c.col2, from_obj=[t1, - t2]).correlate(t1)), - 'SELECT * FROM table2 AS t2alias WHERE ' - 't1alias.col1 = t2alias.col2') - self.assert_compile(vis.traverse(select(['*'], t1.c.col1 - == t2.c.col2, from_obj=[t1, - t2]).correlate(t2)), - 'SELECT * FROM table1 AS t1alias WHERE ' - 't1alias.col1 = t2alias.col2') + + def test_table_to_alias_16(self): + t1alias = t1.alias('t1alias') + vis = sql_util.ClauseAdapter(t1alias) + t2alias = t2.alias('t2alias') + vis.chain(sql_util.ClauseAdapter(t2alias)) + self.assert_compile( + select([t1alias, t2alias]).where( + t1alias.c.col1 == + vis.traverse(select(['*'], + t1.c.col1 == t2.c.col2, + from_obj=[t1, t2]).correlate(t1)) + ), + "SELECT t1alias.col1, t1alias.col2, t1alias.col3, " + "t2alias.col1, t2alias.col2, t2alias.col3 " + "FROM table1 AS t1alias, table2 AS t2alias " + "WHERE t1alias.col1 = " + "(SELECT * FROM table2 AS t2alias " + "WHERE t1alias.col1 = t2alias.col2)" + ) + + def test_table_to_alias_17(self): + t1alias = t1.alias('t1alias') + vis = sql_util.ClauseAdapter(t1alias) + t2alias = t2.alias('t2alias') + vis.chain(sql_util.ClauseAdapter(t2alias)) + self.assert_compile( + t2alias.select().where(t2alias.c.col2 == + vis.traverse(select(['*'], + t1.c.col1 == t2.c.col2, + from_obj=[t1, t2]).correlate(t2))), + 'SELECT t2alias.col1, t2alias.col2, t2alias.col3 ' + 'FROM table2 AS t2alias WHERE t2alias.col2 = ' + '(SELECT * FROM table1 AS t1alias WHERE ' + 't1alias.col1 = t2alias.col2)') def test_include_exclude(self): m = MetaData() - a=Table( 'a',m, - Column( 'id', Integer, primary_key=True), - Column( 'xxx_id', Integer, - ForeignKey( 'a.id', name='adf',use_alter=True ) + a = Table('a', m, + Column('id', Integer, primary_key=True), + Column('xxx_id', Integer, + ForeignKey('a.id', name='adf', use_alter=True) ) ) @@ -1167,93 +1275,6 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): 'SELECT table1.col1, table1.col2, ' 'table1.col3 FROM table1') - def test_correlation(self): - s = select([t2], t1.c.col1 == t2.c.col1) - self.assert_compile(s, - 'SELECT table2.col1, table2.col2, ' - 'table2.col3 FROM table2, table1 WHERE ' - 'table1.col1 = table2.col1') - s2 = select([t1], t1.c.col2 == s.c.col2) - # dont correlate in a FROM entry - self.assert_compile(s2, - 'SELECT table1.col1, table1.col2, ' - 'table1.col3 FROM table1, (SELECT ' - 'table2.col1 AS col1, table2.col2 AS col2, ' - 'table2.col3 AS col3 FROM table2, table1 WHERE ' - 'table1.col1 = table2.col1) WHERE ' - 'table1.col2 = col2') - s3 = s.correlate(None) - self.assert_compile(select([t1], t1.c.col2 == s3.c.col2), - 'SELECT table1.col1, table1.col2, ' - 'table1.col3 FROM table1, (SELECT ' - 'table2.col1 AS col1, table2.col2 AS col2, ' - 'table2.col3 AS col3 FROM table2, table1 ' - 'WHERE table1.col1 = table2.col1) WHERE ' - 'table1.col2 = col2') - # dont correlate in a FROM entry - self.assert_compile(select([t1], t1.c.col2 == s.c.col2), - 'SELECT table1.col1, table1.col2, ' - 'table1.col3 FROM table1, (SELECT ' - 'table2.col1 AS col1, table2.col2 AS col2, ' - 'table2.col3 AS col3 FROM table2, table1 WHERE ' - 'table1.col1 = table2.col1) WHERE ' - 'table1.col2 = col2') - - # but correlate in a WHERE entry - s_w = select([t2.c.col1]).where(t1.c.col1 == t2.c.col1) - self.assert_compile(select([t1], t1.c.col2 == s_w), - 'SELECT table1.col1, table1.col2, table1.col3 ' - 'FROM table1 WHERE table1.col2 = ' - '(SELECT table2.col1 FROM table2 ' - 'WHERE table1.col1 = table2.col1)' - ) - - - s4 = s3.correlate(t1) - self.assert_compile(select([t1], t1.c.col2 == s4.c.col2), - 'SELECT table1.col1, table1.col2, ' - 'table1.col3 FROM table1, (SELECT ' - 'table2.col1 AS col1, table2.col2 AS col2, ' - 'table2.col3 AS col3 FROM table2 WHERE ' - 'table1.col1 = table2.col1) WHERE ' - 'table1.col2 = col2') - - self.assert_compile(select([t1], t1.c.col2 == s3.c.col2), - 'SELECT table1.col1, table1.col2, ' - 'table1.col3 FROM table1, (SELECT ' - 'table2.col1 AS col1, table2.col2 AS col2, ' - 'table2.col3 AS col3 FROM table2, table1 ' - 'WHERE table1.col1 = table2.col1) WHERE ' - 'table1.col2 = col2') - - self.assert_compile(t1.select().where(t1.c.col1 - == 5).order_by(t1.c.col3), - 'SELECT table1.col1, table1.col2, ' - 'table1.col3 FROM table1 WHERE table1.col1 ' - '= :col1_1 ORDER BY table1.col3') - - # dont correlate in FROM - self.assert_compile(t1.select().select_from(select([t2], - t2.c.col1 - == t1.c.col1)).order_by(t1.c.col3), - 'SELECT table1.col1, table1.col2, ' - 'table1.col3 FROM table1, (SELECT ' - 'table2.col1 AS col1, table2.col2 AS col2, ' - 'table2.col3 AS col3 FROM table2, table1 WHERE ' - 'table2.col1 = table1.col1) ORDER BY ' - 'table1.col3') - - # still works if you actually add that table to correlate() - s = select([t2], t2.c.col1 == t1.c.col1) - s = s.correlate(t1).order_by(t2.c.col3) - - self.assert_compile(t1.select().select_from(s).order_by(t1.c.col3), - 'SELECT table1.col1, table1.col2, ' - 'table1.col3 FROM table1, (SELECT ' - 'table2.col1 AS col1, table2.col2 AS col2, ' - 'table2.col3 AS col3 FROM table2 WHERE ' - 'table2.col1 = table1.col1 ORDER BY ' - 'table2.col3) ORDER BY table1.col3') def test_prefixes(self): s = t1.select() |