29 March 2011

Objections to scoring on NULL arguments quiz (2141)

The 28 March quiz asked:

"Most character functions return a NULL if the values of any arguments passed to the functions is NULL. Which of the functions listed do not necessarily return NULL if an incoming argument is NULL?"

The first email I got on this quiz said: "While I understand the answers provided, I did have a little trouble interpreting the question - although I'm not sure how I might have worded it differently as yet. It will be interesting to see how much feedback you receive on this one."

And usually if any single player raises a concern about wording, well, there's more to come. And there was:

"I have an objection for yesterday's quiz. It is about CONCAT. You asked about necessity of returning NULL "if an incoming argument is NULL". So, when one of arguments is null but other argument is not NULL - that is also suitable case. Isn't it? But there is not necessity of returning NULL in this case. Which means that this function "do not necessarily return NULL if an incoming argument is NULL". May be I have not clearly enough understood this question, but this is my point."

"I did not mark the answer regarding NULLIF as a valid answer, since I found that: NULLIF(NULL, 12) would return an ORA-00932 error because expr1 can not be the literal NULL."

"What about select substr('abcde', 2) from dual;? In this case it returns 'bcde', a not-null result. I thougt, the not-mentioned 3rd parameter counts as a NULL."

"When you call nullIF function with null as first input parameter error is raised. So i think that NULLIF is not a correct answer. I have attached the script which shows that. The error is 'PLS-00619: the first operand in the NULLIF expression must not be NULL'."

And now my responses:

First, regarding the semantics of the question: I suppose I could have worded this quiz differently. I believed and still believe that the text of the question is equivalent to the following:

"Is it possible to call the function so that at least one of the arguments is NULL and it returns a non-NULL value?"

That is, if one of the arguments is NULL, that does not mean that the return value of the function would have to ("necessarily") be NULL. So all you have to do is identify a scenario in which you can call the function with NULL and have it return a non-NULL value for it to be a correct choice.

I can see why there might have been confusion with the phrasing (it's sorta, kinda like a double negative because NULL is, well, not very positive). I do not, however, feel that my use of the language here is ambiguous. On to the specifics raised....

CONCAT: if one of the aarguments to CONCAT is NULL and the other is not NULL, then the value returns is not NULL.

NULLIF: Just because you can find one scenario in which a NULL argument causes an error to be raised, does not mean this choice is incorrect. You can also identify a scenario in which you do  pass a NULL value and it returns a non-NULL value.

SUBSTR: you don't have to pass a value for the third parameter of SUBSTR - but that's because Oracle provides a default value for that parameter. So the question then is: what is that default value? In this case, it is not NULL, as you can see from the header of the program in the STANDARD package:
                POS PLS_INTEGER,
                LEN PLS_INTEGER := 2147483647)
So I have no plans to change my scoring on this quiz. Your thoughts?

Regards, SF


  1. Hello!

    First, I'd wrote ‘May be I have not clearly enough understood this question’, but after more careful reading I've found that indeed I have not clearly enough understood the answer about CONCAT.
    Sorry for my stipidity.

    I am ashamed of this objection, would you mind I remain anonymous here?

  2. You get to decide if you want to remain anonymous - no problem!

    But please do not feel ashamed or "stupid". In my experience, if even one person might have a misunderstanding or question, they represent many others with the same concern. So you are doing everyone a favor by asking.

    The handling of default values for arguments is a subtle and important process to understand. It's easy to think of the value as being NULL since it is invisible.

    Thanks for raising this point!


  3. From my point of view that wording while not perfect is unambiguous enough.


  4. Agree, understandable even at the very first reading. But I am looking forward for April Fool Day's quiz.

  5. Works for me, as the English language is not as clear as PL/SQL (which itself can be open to interpretation at times, such as when from a DBA account apparently!).