Issues

Issue #1760 new

nvl() and coalesce() with Oracle 8

Anonymous created an issue

When coalesce() is called with only 2 arguments and Oracle 8, it should use nvl(). (Oracle 9i supports coalesce)

It would also be nice to support the "nvl()" function across the board and simply translate it to coalesce() if called from any database besides Oracle.

In sqlserver it is ISNULL() Some databases may use VALUE()

Mysql might support coalesce, otherwise IFNULL()

I believe ansi is coalesce()

See also #1758

Comments (11)

  1. Anonymous

    This is on 0.5.8, and apparently the check for "use_ansi" will be different in 0.6. But the function seems to be working well for me:

    class coalesce(ReturnTypeFromArgs):
         name = 'coalesce'
    
    @compiles(coalesce)
    def compile_coalesce(element, compiler, **kw):
         return "coalesce(%s)" % compiler.process(element.clauses)
    
    @compiles(coalesce, 'oracle')
    def compile_coalesce(element, compiler, **kw):
        if compiler.dialect.use_ansi:
            return "coalesce(%s)" % compiler.process(element.clauses)
        else:
            sql = "nvl(%s)"
            clauses = map(compiler.process, element.clauses)
            for i in xrange(len(clauses) - 2):
                sql %= "%s, nvl(%%s)" % clauses[i](i)
            return sql % ", ".join(clauses[-2:](-2:))
    

    If older Oracle is being used, the arguments are nested in nvl() calls.

    • Kent
  2. Anonymous
    >>> DBSession.execute(select([coalesce(None,None,None,3,None,5)](coalesce(None,None,None,3,None,5)))).fetchall()[0](0)[0](0)
    17:52:37,290 INFO  [sqlalchemy.engine.base.Engine.0x...cc90](sqlalchemy.engine.base.Engine.0x...cc90) 
    SELECT nvl(NULL, nvl(NULL, nvl(NULL, nvl(:param_1, nvl(NULL, :param_2))))) AS coalesce_1 
    FROM DUAL
    17:52:37,291 INFO  [sqlalchemy.engine.base.Engine.0x...cc90](sqlalchemy.engine.base.Engine.0x...cc90) 
    {'param_1': 3, 'param_2': 5}
    3
    
  3. Mike Bayer repo owner

    the nested nvl() feels a little surprising to me as an automatic behavior. I'm thinking a doc section called "oracle 8 workarounds".

  4. Anonymous

    Replying to zzzeek:

    the nested nvl() feels a little surprising to me as an automatic behavior. I'm thinking a doc section called "oracle 8 workarounds".

    Surprising perhaps, but it really is simply implementing ansi coalesce for Oracle 8, who doesn't. In other words, since I implemented this, my code can invoke coalesce() and get a consistent response regardless of whether the database is oracle 8, oracle 9, postgres, etc. (Our project must work with all). I'd think this is a great feature for any other unfortunate individuals like myself who need to support oracle 8.

  5. Mike Bayer repo owner

    Oh I understand the feature, I'm just proposing that oracle 8 users should explicitly decide they want to do it this way, by reading the docs, observing the proposed workaround, then deciding to use it.

  6. Anonymous

    Replying to zzzeek:

    Oh I understand the feature, I'm just proposing that oracle 8 users should explicitly decide they want to do it this way, by reading the docs, observing the proposed workaround, then deciding to use it.

    You've got a couple things going for you: * I don't mind since I've implemented it already * oracle 8 users are fairly scarce?

  7. Mike Bayer repo owner

    Replying to guest:

    You've got a couple things going for you: * I don't mind since I've implemented it already * oracle 8 users are fairly scarce?

    They are very scarce, since we have few Oracle users in the first place, which is why I'm leaning towards "recipe" on this one. Oracle users in my experience are the least tolerant of surprise behaviors since they are steeped in a deep cultural tradition of DBA oversight and highly idiosyncratic techniques.

  8. Anonymous

    Replying to zzzeek:

    They are very scarce, since we have few Oracle users in the first place, which is why I'm leaning towards "recipe" on this one. Oracle users in my experience are the least tolerant of surprise behaviors since they are steeped in a deep cultural tradition of DBA oversight and highly idiosyncratic techniques.

    What makes me suppose it is safe is that if this is all true, they won't expect to be able to use coalesce() in the first place. If they do try however, the python idiom would say "do the right thing."

  9. Log in to comment