diff options
Diffstat (limited to 'test/sql/test_join_rewriting.py')
| -rw-r--r-- | test/sql/test_join_rewriting.py | 120 |
1 files changed, 116 insertions, 4 deletions
diff --git a/test/sql/test_join_rewriting.py b/test/sql/test_join_rewriting.py index 5a9bdd1d3..801d5ce9a 100644 --- a/test/sql/test_join_rewriting.py +++ b/test/sql/test_join_rewriting.py @@ -1,10 +1,11 @@ from sqlalchemy import Table, Column, Integer, MetaData, ForeignKey, select -from sqlalchemy.testing import fixtures, AssertsCompiledSQL +from sqlalchemy.testing import fixtures, AssertsCompiledSQL, eq_ from sqlalchemy import util from sqlalchemy.engine import default from sqlalchemy import testing + m = MetaData() a = Table('a', m, @@ -30,6 +31,15 @@ e = Table('e', m, Column('id', Integer, primary_key=True) ) +b_key = Table('b_key', m, + Column('id', Integer, primary_key=True, key='bid'), + ) + +a_to_b_key = Table('a_to_b_key', m, + Column('aid', Integer, ForeignKey('a.id')), + Column('bid', Integer, ForeignKey('b_key.bid')), + ) + class _JoinRewriteTestBase(AssertsCompiledSQL): def _test(self, s, assert_): self.assert_compile( @@ -38,10 +48,22 @@ class _JoinRewriteTestBase(AssertsCompiledSQL): ) compiled = s.compile(dialect=self.__dialect__) - for key, col in zip([c.key for c in s.c], s.inner_columns): + + # column name should be in result map, as we never render + # .key in SQL + for key, col in zip([c.name for c in s.c], s.inner_columns): key = key % compiled.anon_map assert col in compiled.result_map[key][1] + _a_bkeyselect_bkey = "" + + def test_a_bkeyselect_bkey(self): + assoc = a_to_b_key.select().alias() + j1 = assoc.join(b_key) + j2 = a.join(j1) + + s = select([a, b_key], use_labels=True).select_from(j2) + self._test(s, self._a_bkeyselect_bkey) def test_a_bc(self): j1 = b.join(c) @@ -60,6 +82,27 @@ class _JoinRewriteTestBase(AssertsCompiledSQL): self._test(s, self._a_bc) + def test_a_bkeyassoc(self): + j1 = b_key.join(a_to_b_key) + j2 = a.join(j1) + + s = select([a, b_key.c.bid], use_labels=True).\ + select_from(j2) + + self._test(s, self._a_bkeyassoc) + + def test_a_bkeyassoc_aliased(self): + bkey_alias = b_key.alias() + a_to_b_key_alias = a_to_b_key.alias() + + j1 = bkey_alias.join(a_to_b_key_alias) + j2 = a.join(j1) + + s = select([a, bkey_alias.c.bid], use_labels=True).\ + select_from(j2) + + self._test(s, self._a_bkeyassoc_aliased) + def test_a__b_dc(self): j1 = c.join(d) j2 = b.join(j1) @@ -94,6 +137,7 @@ class _JoinRewriteTestBase(AssertsCompiledSQL): self._a_bc_comma_a1_selbc ) + class JoinRewriteTest(_JoinRewriteTestBase, fixtures.TestBase): """test rendering of each join with right-nested rewritten as aliased SELECT statements..""" @@ -149,6 +193,36 @@ class JoinRewriteTest(_JoinRewriteTestBase, fixtures.TestBase): "ON a_1.id = anon_2.b_a_id ORDER BY anon_2.b_id" ) + _a_bkeyassoc = ( + "SELECT a.id AS a_id, anon_1.b_key_id AS b_key_id " + "FROM a JOIN " + "(SELECT b_key.id AS b_key_id, a_to_b_key.aid AS a_to_b_key_aid, " + "a_to_b_key.bid AS a_to_b_key_bid FROM b_key " + "JOIN a_to_b_key ON b_key.id = a_to_b_key.bid) AS anon_1 " + "ON a.id = anon_1.a_to_b_key_aid" + ) + + _a_bkeyassoc_aliased = ( + "SELECT a.id AS a_id, anon_1.b_key_1_id AS b_key_1_id " + "FROM a JOIN (SELECT b_key_1.id AS b_key_1_id, " + "a_to_b_key_1.aid AS a_to_b_key_1_aid, " + "a_to_b_key_1.bid AS a_to_b_key_1_bid FROM b_key AS b_key_1 " + "JOIN a_to_b_key AS a_to_b_key_1 ON b_key_1.id = a_to_b_key_1.bid) AS " + "anon_1 ON a.id = anon_1.a_to_b_key_1_aid" + ) + + _a_bkeyselect_bkey = ( + "SELECT a.id AS a_id, anon_2.anon_1_aid AS anon_1_aid, " + "anon_2.anon_1_bid AS anon_1_bid, anon_2.b_key_id AS b_key_id " + "FROM a JOIN (SELECT anon_1.aid AS anon_1_aid, anon_1.bid AS anon_1_bid, " + "b_key.id AS b_key_id " + "FROM (SELECT a_to_b_key.aid AS aid, a_to_b_key.bid AS bid " + "FROM a_to_b_key) AS anon_1 " + "JOIN b_key ON b_key.id = anon_1.bid) AS anon_2 ON a.id = anon_2.anon_1_aid" + ) + + + class JoinPlainTest(_JoinRewriteTestBase, fixtures.TestBase): """test rendering of each join with normal nesting.""" @util.classproperty @@ -156,6 +230,12 @@ class JoinPlainTest(_JoinRewriteTestBase, fixtures.TestBase): dialect = default.DefaultDialect() return dialect + _a_bkeyselect_bkey = ( + "SELECT a.id AS a_id, b_key.id AS b_key_id FROM a JOIN " + "((SELECT a_to_b_key.aid AS aid, a_to_b_key.bid AS bid " + "FROM a_to_b_key) AS anon_1 JOIN b_key ON b_key.id = anon_1.bid) " + "ON a.id = anon_1.aid" + ) _a__b_dc = ( "SELECT a.id AS a_id, b.id AS b_id, " "b.a_id AS b_a_id, c.id AS c_id, " @@ -194,6 +274,19 @@ class JoinPlainTest(_JoinRewriteTestBase, fixtures.TestBase): "ON a_1.id = anon_1.b_a_id ORDER BY anon_1.b_id" ) + _a_bkeyassoc = ( + "SELECT a.id AS a_id, b_key.id AS b_key_id " + "FROM a JOIN " + "(b_key JOIN a_to_b_key ON b_key.id = a_to_b_key.bid) " + "ON a.id = a_to_b_key.aid" + ) + + _a_bkeyassoc_aliased = ( + "SELECT a.id AS a_id, b_key_1.id AS b_key_1_id FROM a " + "JOIN (b_key AS b_key_1 JOIN a_to_b_key AS a_to_b_key_1 " + "ON b_key_1.id = a_to_b_key_1.bid) ON a.id = a_to_b_key_1.aid" + ) + class JoinNoUseLabelsTest(_JoinRewriteTestBase, fixtures.TestBase): @util.classproperty def __dialect__(cls): @@ -208,6 +301,12 @@ class JoinNoUseLabelsTest(_JoinRewriteTestBase, fixtures.TestBase): assert_ ) + _a_bkeyselect_bkey = ( + "SELECT a.id, b_key.id FROM a JOIN ((SELECT a_to_b_key.aid AS aid, " + "a_to_b_key.bid AS bid FROM a_to_b_key) AS anon_1 " + "JOIN b_key ON b_key.id = anon_1.bid) ON a.id = anon_1.aid" + ) + _a__b_dc = ( "SELECT a.id, b.id, " "b.a_id, c.id, " @@ -245,10 +344,21 @@ class JoinNoUseLabelsTest(_JoinRewriteTestBase, fixtures.TestBase): "ON a_1.id = anon_1.b_a_id ORDER BY anon_1.b_id" ) + _a_bkeyassoc = ( + "SELECT a.id, b_key.id FROM a JOIN (b_key JOIN a_to_b_key " + "ON b_key.id = a_to_b_key.bid) ON a.id = a_to_b_key.aid" + ) + + _a_bkeyassoc_aliased = ( + "SELECT a.id, b_key_1.id FROM a JOIN (b_key AS b_key_1 " + "JOIN a_to_b_key AS a_to_b_key_1 ON b_key_1.id = a_to_b_key_1.bid) " + "ON a.id = a_to_b_key_1.aid" + ) + class JoinExecTest(_JoinRewriteTestBase, fixtures.TestBase): """invoke the SQL on the current backend to ensure compatibility""" - _a_bc = _a_bc_comma_a1_selbc = _a__b_dc = None + _a_bc = _a_bc_comma_a1_selbc = _a__b_dc = _a_bkeyassoc = _a_bkeyassoc_aliased = None @classmethod def setup_class(cls): @@ -259,7 +369,9 @@ class JoinExecTest(_JoinRewriteTestBase, fixtures.TestBase): m.drop_all(testing.db) def _test(self, selectable, assert_): - testing.db.execute(selectable) + result = testing.db.execute(selectable) + for col in selectable.inner_columns: + assert col in result._metadata._keymap class DialectFlagTest(fixtures.TestBase, AssertsCompiledSQL): |