nvl() and coalesce() with Oracle 8

[Former user]

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

[Former user]

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

[Former user]

>>> 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

[Mike Bayer]

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

[Former user]

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.

[Mike Bayer]

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.

[Former user]

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?

[Mike Bayer]

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.

[Former user]

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."