oracle 8 / non-ansi joins needs to apply (+) to all occurences of the right hand table

Issue #4076 resolved
Michael Bayer
repo owner created an issue

https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52354

"apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause"

from sqlalchemy import and_
from sqlalchemy import table, column
from sqlalchemy import select
from sqlalchemy.dialects import oracle

a = table('a', column('x'), column('y'))
b = table('b', column('q'), column('p'))


stmt = select([a]).select_from(a.outerjoin(b, and_(a.c.x > b.c.q, b.c.p == None)))
print stmt.compile(dialect=oracle.dialect(use_ansi=False))
SELECT a.x, a.y 
FROM a, b 
WHERE a.x > b.q AND b.p IS NULL

Comments (2)

  1. Michael Bayer reporter

    Ensure (+) is rendered for all right-hand members

    Fixed bug where Oracle 8 "non ansi" join mode would not add the (+) operator to expressions that used an operator other than the = operator. The (+) needs to be on all columns that are part of the right-hand side.

    Change-Id: I952e2369f11b78f5b918456ae3a5b0768d9761ec Fixes: #4076

    → <<cset 01ed5c4009ce>>

  2. Log in to comment