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.