https://bitbucket.org/zzzeek/sqlalchemy/issues/4076/oracle-8-non-ansi-joins-needs-to-apply-to
On Wed, Sep 13, 2017 at 2:41 PM, Mike Bayer <mike...@zzzcomputing.com> wrote: > this is also a bug. the operator here shouldn't matter for the "(+)" > operator to be added, that's easy to fix. (doing it for 1.2 for now). > > On Wed, Sep 13, 2017 at 2:26 PM, Mike Bayer <mike...@zzzcomputing.com> wrote: >> On Wed, Sep 13, 2017 at 12:01 PM, Kent <jkentbo...@gmail.com> wrote: >>> I've got a strange relationship on a legacy Oracle 8i database which I need >>> to support (whether I like it or not). >> >> dude! it is 2017. get on this client! :) I literally have to >> maintain this feature for you personally :). >> >> >>> >>> The cleanest approach is specifying that the 'primaryjoin' to the >>> relationship in the mapper should include an extra join clause. I hate >>> doing this, but after many other approaches, I've found this is by far the >>> cleanest approach due to bad database design (which I can't control -- >>> legacy). >>> >>> Anyway, the attached script shows an simplified, analogous mock-up, which >>> works correctly when joins are ANSI and incorrectly with use_ansi=False. >>> >>> The script demonstrates an inconsistency in use_ansi True vs. False on >>> sqlalchemy version 1.1.14 (although my sqlalchemy is older). >>> >>> In the use_ansi=False SQL, the correct "fix" would be changing the rendered: >>> >>> AND bugs_1.deathdate IS NULL >>> >>> into >>> >>> AND bugs_1.deathdate(+) IS NULL >>> >>> This then matches the ANSI join and works on 8i (I've tested it). >>> >>> Is this something we can fix? Since the column is on the remote table and >>> specified in the join condition, it really needs "(+)" after the column name >>> in SQL. This accomplishes the same thing as the ANSI version placing this >>> join condition in the "ON ..." clause instead of the "WHERE". >>> >>> Alternatively, is there a hack I could use to fix the rendered SQL on >>> joinedloads for this particular relationship? >> >> the miracle of Docker means that I now have easy to run Oracle, SQL >> Server, etc. databases anywhere I need them so I can quickly confirm >> that this works with ansi or not: >> >> mapper(Rock, rocks_table, >> properties={ >> 'livingbugs': relationship(Bug, >> primaryjoin=and_( >> bugs_table.c.rockid == rocks_table.c.id, >> bugs_table.c.deathdate.op("(+)=")(null()), >> )), >> }) >> >> >> >> >> >>> >>> Thanks very much in advance! >>> Kent >>> >>> -- >>> SQLAlchemy - >>> The Python SQL Toolkit and Object Relational Mapper >>> >>> http://www.sqlalchemy.org/ >>> >>> To post example code, please provide an MCVE: Minimal, Complete, and >>> Verifiable Example. See http://stackoverflow.com/help/mcve for a full >>> description. >>> --- >>> You received this message because you are subscribed to the Google Groups >>> "sqlalchemy" group. >>> To unsubscribe from this group and stop receiving emails from it, send an >>> email to sqlalchemy+unsubscr...@googlegroups.com. >>> To post to this group, send email to sqlalchemy@googlegroups.com. >>> Visit this group at https://groups.google.com/group/sqlalchemy. >>> For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.