Oracle use_ansi=False and many-to-many relation returns all rows

[Former user]

If user_ansi=False is used in Oracle engine, many-to-many relations doesn't work properly.

Here is the sligthly modified test code from http://www.sqlalchemy.org/docs/datamapping.myt#datamapping_manytomany

from sqlalchemy import *

engine = create_engine('oracle://user=test&password=test',use_ansi=False,echo=0)


articles = Table('articles', engine,
    Column('article_id', Integer, Sequence('articles_seq'),primary_key = True),
    Column('headline', String(150), key='headline'),
    Column('body', TEXT, key='body'),
)

keywords = Table('keywords', engine,
    Column('keyword_id', Integer, Sequence('keywords_seq'), primary_key = True),
    Column('keyword_name', String(50))
)

itemkeywords = Table('article_keywords', engine,
    Column('article_id', Integer, ForeignKey("articles.article_id")),
    Column('keyword_id', Integer, ForeignKey("keywords.keyword_id"))
)


# class definitions
class Keyword(object):
    def __init__(self, name = None):
        self.keyword_name = name

class Article(object):
    pass

# define a mapper that does many-to-many on the 'itemkeywords' association 
# table
Article.mapper = mapper(Article, articles, properties = dict(
        keywords = relation(mapper(Keyword, keywords), itemkeywords, lazy=False)
        )
    )

article = Article()
article.headline = 'a headline'
article.body = 'this is the body'
article.keywords.append(Keyword('politics'))
article.keywords.append(Keyword('entertainment'))

objectstore.commit()

article = Article()
article.headline = 'second head'
article.body = 'am i a twin?'
article.keywords.append(Keyword('bizzare'))
article.keywords.append(Keyword('entertainment'))

objectstore.commit()

s = Article.mapper.select_by(headline='a headline')
for k in s[0](0).keywords:
    print k.keyword_name

It prints all keywords from the table, not just two of them.

Inspection of SQL statements tells that only one join is in WHERE statement (on keywords:article_keywords) but article_keywords:articles relation is missing.

[Mike Bayer]

ok, use_ansi=False has basically never been maintained or tested except for maybe the first two weeks I was writing SQL construction code, I am sure it breaks in a zillion different conditions. This is becuase I had the notion that my own experience with an ansi-noncompliant Oracle was some anecdotal figment of my past, and the whole idea of using the (+) operator was somewhat of a novelty.

The mechanics of constructing JOINS has become an extremely complex beast since then and I havent looked into how well the use_ansi=False translation trick can work with all of those conditions. It might be very easy or might be more nightmarish.

so supposing that its nightmarish, under what conditions does Oracle not know about the JOIN keyword ? is that all versions of Oracle prior to 9, or is it configurable ?

[Andrija Zarić]

all Oracle versions prior to 9 use the (+) syntax for outer join, and that is not an option.

[Mike Bayer]

good news, I got some adjustments to the JOIN modifier in the oracle compiler and multi-part JOINs seem to be working now. added a test target 'oracle8' to the test suite to enable running thru all the tests with use_ansi=False. the mapper.py and objectstore.py, which are the primary killers of mapper lazy and eager loading, both pass with oracle8 now, so you should have decent results. so i will close this ticket which was fixed across changeset:1187, changeset:1188, changeset:1189.

its very likely oracle8 will have more issues so open more tickets for that, this ticket represents "oracle8/non-ansi totally broken" which is resolved now.