The PL/SQL Challenge (www.plsqlchallenge.com) offers a daily quiz on the PL/SQL language, through which thousands of Oracle technologists demonstrate and deepen their knowledge of PL/SQL. This blog contains posts by the PL/SQL Challenge founder, Steven Feuerstein, as well as comments from players.

30 June 2011

Leaving off "c" in "abdefg" a trick? (3571)

The 29 June quiz asked you to figure out the text that would be displayed on the screen when this block was executed:

A number of players were unhappy with this quiz. Here are some of the comments:

I would bet a lot of people got this wrong (like me) because they failed to realize you didn't have a "c" in your input string. If the input string included the "c" (i.e. "abcdefg") then the second one would have returned "-7,2,=ab". I don't mind the question being a little tricky, but that one seemed to be purposely misleading.

and

This quiz is wrong. I tested it both on 10.2.0.4 and 11.2.0.1 Both returned the answer that I gave. It output both: TESTS: ef TESTS: ab Here is the script I used: BEGIN DBMS_OUTPUT.PUT_LINE('TESTS: '||SUBSTR('abcdefg',5,2)); DBMS_OUTPUT.PUT_LINE('TESTS: '||SUBSTR('abcdefg',-7,2)); END; This isn't the first time where I have found issues in the daily quizzes.

Finally, one of our most active (and consistently high ranking) players wrote to us as follows:

"Darn you :) I got this one wrong, because I misread the string as "abcdefg", not 'abdefg'. Just teaches us to read carefully - these sorts of typos happen in real code too. It's a good quiz."

Well, I can tell you with complete certainty that there was no intention to make this a tricky question. In fact, I didn't even notice that I'd left "c" out of the string. Looking over the comments, though, I believe that it actually made the quiz better than it would have been if it had included the "c". Consider: a player took the time to write code to validate the results and didn't even notice that he or she used the wrong string in the test.

When we are testing and debugging our code, one of our biggest problems is making assumptions about the data or about the code we are examining. Unverified assumptions are real "killers." I have wasted so many hours debugging programs over the years and then discovering that the problem didn't lie in my algorithm, but in the lack (or presence) of some problematic data. Or I read the code carelessly, seeing a string or value of some sort and thought it was "X" when it was "Y".

Happens a lot, doesn't it?
We tend to see and hear what we want or expect to see and hear, not necessarily what is actually there.

So this quiz tested more than your understanding of SUBSTR. It tested your ability to read the code closely and not make assumptions. I am OK with that. How about you?

Everyday I read the quiz carefully, because I want to be certain about the question. It never gives me a "top" score as I spend more time reading the quiz. So I counted the number of letters in the string and got it right.That gave me a nice score and a 13th place for the day.

I believe Stephen when he says that he had not intended to skip a letter in the string literal; however, I disagree with his assessment that this accident improved the quiz. Quite the opposite, I argue that it seriously diminished this quiz’s value. If the scores are intended to reflect players’ relative proficiency with PL./SQL, then this quiz missed the mark. The unfortunate choice of literal in this case makes it impossible to distinguish the players that misunderstood the behavior of the SUBSTR function from those that misread the string. Had the string literal consisted of unordered or nonconsecutive characters, the scores would likely have more accurately represented players’ PL/SQL knowledge.

Some have suggested that quizzes such as this one are in some way similar to debugging. This viewpoint, however, misses some important distinctions. Debugging involves identifying and resolving discrepancies with intended behavior which implies that the intended behavior is known. In general, code that is being debugged is available in machine readable form. In the case of most quizzes, however, players have neither prescribed behavior nor machine readable code. Manually typing the code, as some discovered with this quiz, is no guarantee that the code has been read correctly and is, in fact, prone to mistakes. Also, while real-life debugging may be time constrained, one is unlikely to be penalized for each few seconds spent evaluating the code.

This quiz also demonstrates a serious deficiency with all-or-nothing scoring. In this case knowledge of at least three aspects of SUBSTR is being evaluated: where the search begins; the portion of the string returned for a successful search; and the returned value when the absolute value of the search offset is greater than the length of the string. A misunderstanding of any of these would result in a score of zero. All-or-nothing scoring, when multiple facets are being tested, does not permit meaningful evaluation of one’s proficiency relative to others.