Wednesday, September 28, 2011

Interesting Error

We just upgraded one of our 10.2.0.3 dbs to 11.2.0.2 plus some patches.  Today an interesting ORA-07445 was thrown to the alert.log:

ORA-07445: exception encountered: core dump [__intel_new_memcpy()+382] [SIGILL] [ADDR:0x3FE5CAE] [PC:0x3FE5CAE] [Illegal operand] []

Searching metalink didn't get us anywhere, so we opened a TAR (or iTar, or SR, or ServReq, or whatever the heck they are calling them these days).  While Oracle Support was looking at the issue, I looked at the query generating the error:

SELECT nvl(t.value, null) FROM table t WHERE t.id = 12345;

Huh?  If the value is null, substitute a null?  WTF?

Presto-changeo...

SELECT t.value FROM table t WHERE t.id = 12345;

...and the error goes away.

Is it a bug on Oracle's side? Sure.

Is it a stupid thing to do on our side? You betcha.

5 comments:

Noons said...

It's the non-SQL Irish coding latest coding standard: to-be-sure-to-be-sure.
Those among us from more conventional and recursive SQL backgrounds would say one shouldn't be able to test NULL and replace it with NULL: if it is unknown, how can you be sure it should be unknown?

David Aldridge said...

Do you get the same problem if the second argument to Nvl() is an expression or column that returns NULL?

btw I haven't used Nvl() in years -- I'm strictly a Coalesce() man now.

Jan S. said...

to be more more sure:
SELECT DISTINCT nvl(t.value, null) FROM table t WHERE t.id = 12345

Jan

uhesse said...

That one made me laugh out loud :-)
Very likely that we did not test this kind of NVL as part of the new version testing...

Alexandru Garbia said...

see note 1321682.1