From 6b82c6e89bfaf1499ba7dd560a3da99d327b4a05 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sat, 30 Jan 2010 19:00:40 +0000 Subject: [PATCH] - use_ansi=False won't leak into the FROM/WHERE clause of a statement that's selecting from a subquery that also uses JOIN/OUTERJOIN. --- CHANGES | 4 ++++ lib/sqlalchemy/dialects/oracle/base.py | 11 ++++++++--- test/dialect/test_oracle.py | 18 ++++++++++++++++++ 3 files changed, 30 insertions(+), 3 deletions(-) diff --git a/CHANGES b/CHANGES index fa9d2366d9..b1b8271585 100644 --- a/CHANGES +++ b/CHANGES @@ -728,6 +728,10 @@ CHANGES - an NCLOB type is added to the base types. + - use_ansi=False won't leak into the FROM/WHERE clause of + a statement that's selecting from a subquery that also + uses JOIN/OUTERJOIN. + - added native INTERVAL type to the dialect. This supports only the DAY TO SECOND interval type so far due to lack of support in cx_oracle for YEAR TO MONTH. [ticket:1467] diff --git a/lib/sqlalchemy/dialects/oracle/base.py b/lib/sqlalchemy/dialects/oracle/base.py index bb9ed32506..5f7a302929 100644 --- a/lib/sqlalchemy/dialects/oracle/base.py +++ b/lib/sqlalchemy/dialects/oracle/base.py @@ -351,9 +351,14 @@ class OracleCompiler(compiler.SQLCompiler): clauses.append(visitors.cloned_traverse(join.onclause, {}, {'binary':visit_binary})) else: clauses.append(join.onclause) - + + for j in join.left, join.right: + if isinstance(j, expression.Join): + visit_join(j) + for f in froms: - visitors.traverse(f, {}, {'join':visit_join}) + if isinstance(f, expression.Join): + visit_join(f) return sql.and_(*clauses) def visit_outer_join_column(self, vc): @@ -404,7 +409,7 @@ class OracleCompiler(compiler.SQLCompiler): existingfroms = self.stack[-1]['from'] else: existingfroms = None - + froms = select._get_display_froms(existingfroms) whereclause = self._get_nonansi_join_whereclause(froms) if whereclause is not None: diff --git a/test/dialect/test_oracle.py b/test/dialect/test_oracle.py index 5378280af9..a6c3c19bcd 100644 --- a/test/dialect/test_oracle.py +++ b/test/dialect/test_oracle.py @@ -203,6 +203,24 @@ AND mytable.myid = myothertable.otherid(+)", "mytable.myid = myothertable.otherid ORDER BY mytable.name) WHERE " "ROWNUM <= :ROWNUM_1) WHERE ora_rn > :ora_rn_1", dialect=oracle.dialect(use_ansi=False)) + subq = select([table1]).\ + select_from(table1.outerjoin(table2, table1.c.myid==table2.c.otherid)).alias() + q = select([table3]).select_from(table3.outerjoin(subq, table3.c.userid==subq.c.myid)) + + self.assert_compile(q, "SELECT thirdtable.userid, thirdtable.otherstuff " + "FROM thirdtable LEFT OUTER JOIN (SELECT mytable.myid AS myid, mytable.name" + " AS name, mytable.description AS description " + "FROM mytable LEFT OUTER JOIN myothertable ON mytable.myid = " + "myothertable.otherid) anon_1 ON thirdtable.userid = anon_1.myid", + dialect=oracle.dialect(use_ansi=True)) + + self.assert_compile(q, "SELECT thirdtable.userid, thirdtable.otherstuff " + "FROM thirdtable, (SELECT mytable.myid AS myid, mytable.name AS name, " + "mytable.description AS description FROM mytable, myothertable " + "WHERE mytable.myid = myothertable.otherid(+)) anon_1 " + "WHERE thirdtable.userid = anon_1.myid(+)", + dialect=oracle.dialect(use_ansi=False)) + def test_alias_outer_join(self): address_types = table('address_types', column('id'), -- 2.47.3