From 0c9d55db73776d12a6898929092a42e586f3c4bf Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Mon, 22 Oct 2012 13:29:12 -0400 Subject: The auto-correlation feature of :func:`.select`, and by proxy that of :class:`.orm.Query`, will not take effect for a SELECT statement that is being rendered directly in the FROM list of the enclosing SELECT. Correlation in SQL only applies to column expressions such as those in the WHERE, ORDER BY, columns clause. [ticket:2595] --- test/sql/test_compiler.py | 13 +++------ test/sql/test_generative.py | 71 ++++++++++++++++++++++++++++----------------- 2 files changed, 49 insertions(+), 35 deletions(-) (limited to 'test/sql') diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py index 5b7a5d1d7..04443a0ed 100644 --- a/test/sql/test_compiler.py +++ b/test/sql/test_compiler.py @@ -516,11 +516,13 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): def test_where_subquery(self): s = select([addresses.c.street], addresses.c.user_id == users.c.user_id, correlate=True).alias('s') + + # don't correlate in a FROM list self.assert_compile(select([users, s.c.street], from_obj=s), "SELECT users.user_id, users.user_name, " "users.password, s.street FROM users, " "(SELECT addresses.street AS street FROM " - "addresses WHERE addresses.user_id = " + "addresses, users WHERE addresses.user_id = " "users.user_id) AS s") self.assert_compile(table1.select(table1.c.myid == select([table1.c.myid], table1.c.name @@ -556,14 +558,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): 'mytable AS ta WHERE EXISTS (SELECT 1 FROM ' 'myothertable WHERE myothertable.otherid = ' 'ta.myid)) AS sq2, mytable') - s = select([addresses.c.street], addresses.c.user_id - == users.c.user_id, correlate=True).alias('s') - self.assert_compile(select([users, s.c.street], from_obj=s), - "SELECT users.user_id, users.user_name, " - "users.password, s.street FROM users, " - "(SELECT addresses.street AS street FROM " - "addresses WHERE addresses.user_id = " - "users.user_id) AS s") + # test constructing the outer query via append_column(), which # occurs in the ORM's Query object diff --git a/test/sql/test_generative.py b/test/sql/test_generative.py index f1c118e15..d0a6522d5 100644 --- a/test/sql/test_generative.py +++ b/test/sql/test_generative.py @@ -1139,30 +1139,6 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): column("col3"), ) - def test_select(self): - 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') - 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 WHERE ' - 'table2.col1 = table1.col1) ORDER BY ' - 'table1.col3') - s = select([t2], t2.c.col1 == t1.c.col1, correlate=False) - 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_columns(self): s = t1.select() @@ -1201,11 +1177,12 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): '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 WHERE ' + 'table2.col3 AS col3 FROM table2, table1 WHERE ' 'table1.col1 = table2.col1) WHERE ' 'table1.col2 = col2') s3 = s.correlate(None) @@ -1216,13 +1193,25 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): '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 WHERE ' + '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, ' @@ -1231,6 +1220,7 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): '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 ' @@ -1239,6 +1229,35 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL): '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() self.assert_compile(s, -- cgit v1.2.1