Junk DNA - Junk Oracle, part 0000002

Most high level programming languages implement a selection control mechanism whereby a variable can be used to control program execution via. a multiway branch, a syntactic alternative to IF-THEN-ELSIF-ELSIF-ELSIF-ELSIF-ELSE-ENDIF construct.

In C, Java, PHP, C# and many more imperative programming languages, this branching logic is implemented using a SWITCH statement. An example is shown below – this is very basic well understood stuff.

switch (x) {
            case  1:  printf("%d", 10);    break;
            case 15:  printf("%d", 503);   break;
            case  2:  printf("%d", 20);    break;
            case  3:  printf("%d", 30);    break;
            default:  printf("%d", x*100); break;
}

You could rewrite the trivial code snippet above with IF and ELSE as:

  if (x == 1) { printf("%d", 10); }
   else if (x == 15) { printf("%d", 503); }
    else if (x == 2} { printf("%d", 20); }
     else if (x == 3} { printf("%d", 30); }
      else { printf("%d", x*100); }

Declarative programming languages implement the same type language construct too, XSLT using choose and ANSI SQL using the CASE statement. The ANSI SQL CASE statement, as implemented in Oracle, with a bit of test data to provide a working example, is shown below.

SQL>
SQL>
SQL>
SQL> CREATE TABLE log(x NUMBER);

Table created.

SQL>
SQL> CREATE OR REPLACE FUNCTION SomeUserFunction(xx NUMBER) RETURN NUMBER DETERMINISTIC AS
 2   PRAGMA AUTONOMOUS_TRANSACTION;
 3    BEGIN
 4     INSERT INTO log(x) VALUES(xx);
 5      COMMIT;
 6     RETURN xx * 10;
 7    END;
 8 /

Function created.

SQL>
SQL> CREATE TABLE test(x NUMBER);
Table created.

SQL> INSERT INTO test(x) VALUES(1);
1 row created.
SQL> INSERT INTO test(x) VALUES(2);
1 row created.
SQL>
SQL> SELECT CASE SomeUserFunction(x)
 2            WHEN 1 THEN SomeUserFunction(x)
 3            WHEN 15 THEN 500
 4            WHEN 2 THEN SomeUserFunction(x)
 5            WHEN 3 THEN SomeUserFunction(x)
 6            ELSE SomeUserFunction(x)
 7          END functionResult
 8   FROM test
 9     WHERE x = 2;

FUNCTIONRESULT
--------------
 20

SQL>

Before continuing, review the code above and ask yourself how many times is the function “SomeUserFunction” above invoked?

This is not a trick question – just look at the code snippet and answer the question, to yourself quietly if you like. Note the DETERMINISTIC keyword in the function SomeUserFunction(..).

I am performing a bit of logging in the example code, so merely querying the table “log” will provide the answer. You answer is …. well is should be once, after all, optimising compilers have been around for decades, many decades, and well before Oracle introduced the ANSI CASE statement into their implementation of SQL. I’ve also annotated the function with the DETERMINISTIC keyword too, so once it is.

Alas, it is twice. My head sinks into my hands every time I encounter this sort of rubbish. I wouldn’t write a compiler like this. It is just a sloppy implementation … really who would write yet alone design a language with some “gotcha” like this? Would you be proud of writing code that behaved like this? ANSI have neither designed nor specified this behaviour either. Imagine if the function has side-effects – how difficult would it be to find that bug?

If is sloppy elsewhere too – here’s a silly example, yet the function is invoked twice by Oracle too.

 SELECT NVL(SomeUserFunction(x), SomeUserFunction(x)) FROM DUAL;

So, put another way, the code above isn’t at all logically equivalent to

  x NUMBER;
  x := SomeUserFunction(x);
  IF x = NULL THEN
    x := SomeUserFunction(x);
  END IF;

I think it is worth pointing out too that

SELECT COALESCE(SomeUserFunction(x), SomeUserFunction(x)) FROM DUAL;

and

SELECT NVL(SomeUserFunction(x), SomeUserFunction(x)) FROM DUAL;

are not logically equivalent either, and certainly COALESCE as written and used above is not equivalent to NVL (COALESCE would result in SomeUserFunction(..) being invoked once, NVL twice). Needless to say that DECODE in Oracle exhibits the double SomeUserFunction(x) invocation too. I am not fibbing – try it yourself! Try with even more simple code if you like – how about

  SELECT COALESCE(1, 2/0) FROM DUAL;

vs.

  SELECT NVL(1, 2/0) FROM DUAL;

Amazing isn’t it? NVL results in a runtime error!

I’m viewing this as a junk Oracle, a term I introduced in a previous post, mainly as it’s a junk code, unexpected and inconsistent logic, and to have a conditional API existing in (near) replicate functionality (NVL vs. COALESCE vs. DECODE vs. CASE) of different flavours with subtle and poorly documented side effects, … well some might argue this is reflective of Oracle’s evolutionary development, but even at the time the implementation of NVL has to be accepted as poor. It’s more junk Oracle I’m afraid.

— Published by Mike, 21:42:16 31 October 2016