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.

26 May 2011

Should We Test Knowledge of "Bugs" in Quiz? (2344)

The 24 May demonstrates the impact of using a constrained subtype for the datatype of an associated array index, as well the fact that when using a BULK COLLECT fetch to populate that collection, the constraints of the type are ignored.

Two players complained that they don't think it makes sense for a quiz to test one's knowledge of a bug. I offer their comments below and will leave it to the author first to reply with her own "story behind the quiz", and of course publish any other comments as well.

"While this is obviously known behaviour, I feel a bit ripped off in getting this wrong. I answered what was logical (to me) from the code - the array definition says the index must be -1/0/1, bulk collect will use index values 1/2/3, so bulk collect will error. To me, the behaviour of bulk collect ignoring the index by's data type constraints seems like a bug. If this is documented by Oracle as being expected behaviour, it would be good to have that reference in the answer. If not, it seems a bit rough to mark people incorrect for not knowing about a bug."

and with some code, too:

When you run the code of this quiz, Oracle does actually make from an INDEX BY SIGNTYPE table an 'read-only' INDEX BY PLS_INTEGR table. You can only change the values for index (-1, 0, 1). See the following code:

19 comments:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collection_definition.htm#sthref2535"type_name can be BINARY_INTEGER, PLS_INTEGER, or VARCHAR2, or one of VARCHAR2 subtypes VARCHAR, STRING, or LONG. v_size specifies the length of the VARCHAR2 key."

The bug is compiling successfully SIGNTYPE as INDEX BY. The rest bug(s) are only side effects.

Such bugs teach nothing useful. It doesn't deserve challenge. But they are suitable for forums as joke.

I don't agree with this complain. I would like to know as much Oracle bugs as possible, and I prefer to get this knowledge before I encounter them in real life. Therefore, I reckon until this behaviour is corrected in production Oracle databases this topics must be used in PL/SQL Challenge.

If you are in the Challenge because you want to see how well you can play (or how well you code, or how clearly and quickly you think,...) then there is no place for bugs and "trick" questions (anyone remember the "end loop" without the semicolon?).

If you are in the Challenge because you want to learn new, interesting, or powerful features, then the occasional bug or trick is acceptable. However, the questions need to be reasonable. This particular question I felt was rather pointless because I could not imagine a case where indexing by a type as limited as SIGNTYPE would reasonably occur.

I agree with those who advocate against the inclusion of quizzes that exploit defects found in Oracle’s implementation of PL/SQL. Such defects are not features of the language and knowledge of them is not an indication of PL/SQL programming proficiency. The excerpt in Vitaliy’s post shows that Oracle’s documentation unambiguously restricts which types may be used indexes for associative arrays. Even if subtypes of BINARY_INTEGER were permitted, the code in question would still violate Oracle’s specification because constrained subtypes are not permitted to have values that violate their constraints.

The following lessons may be gleaned from the quiz on May 24 (as well as the quiz on May 17 when considering the scoring challenge):

My biggest complaint against this particular quiz is that competent developers are unlikely to have ever encountered this defect. This means that a correct answer was likely the result of a random click, a lucky guess that the author was being intentionally tricky, or running the code before responding. I have no issues with players testing the code prior to answering, the time penalty adjusts for that; however, it should be possible for a knowledgeable player to correctly identify correct choices without resorting to running the code.

Some argue that "bug" quizzes offer important information. I agree that knowledge of PL/SQL implementation defects may be important; however, discussion forums are more suitable venues for conveying such information.

I think there is room for the 'undocumented/unexpected behaviour as a warning' type questions. My concern about questions that are a real 'bug' is that it might get fixed in 10.2.0.6/11.2.0.3 or whatever, thus reducing the reliability of the archive of questions.

One thought is to classify these questions outside the beginner/intermediate/advanced category. Perhaps as 'Trivia' or 'Extreme'. If that category were given a very low (or zero) difficulty, the scoring would be irrelevant and people wouldn't feel punished for not knowing.

That's a nice proposal of Gary. It seems to be fair not to punish players for such questions. And still such questions are very useful for learning.I've raised that point, that this quiz doesn't deserved to be Advanced.Using Gary's proposal we may finally find a reasonable solution for such quizes.

As Vitaliy points out, the quiz is clearly outside of what Oracle documentation describes - based on the documentation this should not have compiled. I answered based on what I thought the code should do and I was wrong - yes it would be nice if I got a better score(and I do feel a bit hard done by), but I have certainly learnt something. Sometimes I can forget that the purpose of the challenge is to improve us as developers, rather than getting a good score.

Like jhall I feel that even "trick" questions should be such that highly experienced developers have a chance of knowing the answer without running the code.

But I also like to learn and be aware of "weird" behaviour of PL/SQL so that perhaps some day I may save days of frustrating debugging :-) So a sprinkle of "bug" oriented quizzes now and then are okay by me - just not too often and preferably "bug" quizzes should be bugs there is a chance that we might encounter unknowingly in our work.

This one is intrigueing to know but not really much chance I'll encounter it, since I would not have thought of using a constrained subtype to index by :-)

I like Gary's suggestion - that would allow such questions to be given now and then without complaints that it punishes competitive players. Perhaps call the category 'Educational' - meaning that "This quiz is given to educate you players, it is not really a part of the competition."

Alternatively it could be another quiz type in the new framework. We would have the daily PL/SQL quiz, the weekly(?) SQL quiz, and then perhaps every fortnight the "Beware of bugs" quiz (or maybe more generically the "Weekly Weirdo"?) Then it would be very clear for players, that these are quizzes that might stretch your mind in ways you hadn't thought of :-)

I think the only reason why every one is participating in this quiz is to know about PL-sql and to know more about it. To get the knowledge of bugs is great as every on will come to know the area of improvements. Rather then rectifying bugs in production.

Hello All,I will post my comments in several parts, because of length limitations.

== PART1 ==

Just a few clarifications, if anybody is interested to know how this quiz was born.

Maybe you remember that several months ago, on January 13 there was a quiz followed by a wide debate having a choice stating the following:

"A nested table can have as many elements in it as a relational table can have rows."

After initially scored as wrong, because nested tables do have an index limit set by thePLS_INTEGER type's upper limit itself, it was however proven afterwards thata nested table stored in the database CAN contain more elements than its index limit.

This was proven by a test case set up very cleverly by Nikotin, and the choice was rescored.

After that quiz I started to perform some simple tests around the behavior of the differentcollection types when we try to exceed their index values upper limits.

Thus I found for example that for a VARRAY type, as naturally expected, you CANNOT exceed the upper limit set in the TYPE defintion, neither manually, by direct assignment to an element nor by performing a BULK SELECT.

For example, if you perform a SELECT BULK COLLECT that returns more than 10 elementsinto a VARRAY(10), then at the 11-th element the following error is raised:

ORA-22165: given index [11] must be in the range of [1] to [10]

Then, naturally, I started asking whether a NESTED TABLE does exhibit similar behavior.But, since the upper limit of POWER(2,31)-1 is difficult to be reached practically,I thought of testing the same on an ASSOCIATIVE ARRAY, that closely mimics the NESTED TABLEbehavior with regard to populating through a BULK COLLECT, and which alsooffers the possibility to have its INDEX type declared as a "smaller range" typethan the usual PLS_INTEGER ...

I did not see any interdiction documented for using a SUBTYPE in any place where its parent typecan be used, subject, of course, to the natural limitations that the subtype values impose.

From here, up to considering the "smallest range" subtype, which is SIGNTYPE, there was only a small step left ...

Then, the quiz reviewers and I myself did perform tests using other subtypes of PLS_INTEGER, like NATURAL and POSITIVE and all of them compiled correctly, though, of course,we did not set up a test case for trying to exceed the limit of those subtypes,which is the same as that of PLS_INTEGER.( to be continued )

I thought even of suggesting such a case for the quiz itself, because a range of 100 elementslooks much more useful in practice than one of 3 rows only ... and I expected objections fromthis direction ...On the other hand, choosing a much smaller range limit already brought me back to the"small range" of SIGNTYPE, which maybe does have an "additional flavour of curiosity" ...

Sorry to say, but in my opinion nothing prevents us from using any SUBTYPE of a PLS_INTEGERas an array index in PL/SQL.If Oracle were considering such a restriction as limiting, then it would have probablyimplemented a compiler error similar to the one received when you try to BULK SELECT intoan associative array indexed by a VARCHAR2(n), namely:

The only problem here is that Oracle does not really impelement an "upper range exceeded"test for such a subtype, I really wonder whether it does implement it even for a PLS_INTEGERindex type when using BULK SELECT ... (as I reminded at the beginning, for a nested tablestored in the database this limit IS NOT enforced !).

It is true that this behavior is contrary to common sense, just as much as the VARRAY behavioris different and in fact conformant with what one would expect.(Once in the past there was even a quiz which stated that VARRAY can be used as a "limiting tool"for the number of rows returned from a cursor, some of you maybe remember it).

But by no means was any trick involved here, just a strange behavior of which maybe some of the players would prefer to be aware.

If already speaking of tricks ... does anybody remember Nikotin's quiz from March 23,regarding integer values with 41 digits that can be stored in a database INTEGER columnbut CANNOT be selected back into a PL/SQL variable anchored on the same column ?

Was that also a trick or just some strange Oracle behavior, at least up to the existing versions ?

Do we always strictly stick to the documentation ?

This question was also asked many times in the past.We can just think of the many quizes we had around the issue of procedures overloading in packages,about which Oracle documentation states clearly that they CANNOT be overloaded,however, they do compile successfully even in cases where they can never be executed withoutraising the "PLS-00307: too many declarations of match this call" error.

Reading of a few suggestions about having a separate "Extreme" or "Trivia" quizjust brings me one month back to that DBMS_XA quiz ... when I suggested something similarfor "Expert" quizes of "super-specialized" domains, while others opposed it, saying that ANY pl/sql question is welcome ...

It looked to me that once we agreed that the PL/SQL Challenge is meant to learn and to teach(or, better said, to communicate experience) and I would be very happy if I could find myself in both these positions over the time.

I agree with you that the PL/SQL challenge is meant to learn and teach besides being a competition - definitely!

And I am not bashing your quiz particularly - maybe I just feel there has been a bit too many quizzes lately where I have had the feeling that I could not possibly answer based on knowledge alone, but would have had to run the code to answer correctly.

I still say any PL/SQL question is welcome in the quiz. And even though I advocate a new quiz type for "Weekly Weirdo" - I do not say that ALL "special" quizzes should be relegated to "Weekly Weirdo". The daily challenge should still have some learning experience.

I just say it should be a balanced diet so we won't scare half the people away because they feel half the questions are impossible to know. Having the new 2.0 framework in place makes it so much easier to balance the quizzes that in my opinion it should be considered now.

Gary makes an important point: bugs may be corrected by a patch that does not alter the release level. It is also possible that defects may manifest differently with different patch levels. The problem with bug questions is that they depend on experience with a specific case that is not indicative of a developer’s competence or overall knowledge. For players lacking that specific experience the only reliable way to identify the correct responses is to run the code. This reduces the competitive aspect of the PL/SQL Challenge to a test of typing skills.

Knowledge of defects in Oracle’s PL/SQL implementation is important, but is not a proper domain for quizzes that purport to assess one’s PL/SQL language knowledge and skills. Bugs are not part of the language (if they were then they would be features, not defects). One means of using the Challenge to convey knowledge of these defects is to identify the issue in the question, present code that is affected by the bug, and ask which of the choices are viable workarounds. Framed in this manner the important bug details are presented while preserving language competence as the basis for competition. Encyclopedic knowledge of PL/SQL implementation defects is not the mark of a competent PL/SQL developer; however, the ability to effectively work around an identified defect is.

Iudith’s description of how her quiz evolved illustrates my point that bug quizzes rely on special experience. I laud her for her curiosity and efforts to explore this strange behavior. Although the results of her investigation are interesting, they demonstrate what most of us probably already knew: unexpected results may occur when one executes code that compiles in spite of violating language specifications.

I strongly agree with Kim's remark that the feeling of having to test code out for answering correctlyis very frequent in the last weeks ... though, I don't think that my quiz was the first on this line.I can definitely quote many others, that emphasized aspects that ARE NOT specified in the Documentation.

One that comes to my mind immediately is the quiz about raising a NO_DATA_FOUND in a functioncalled from SQL ... also not documented anywhere, though, most probably NOT a bug.

... and, by the way, each time when I am not sure of a choice correctness, if I try to go on withouttesting it, just based on "immediate common sense", I always regret it afterwards ... For sure, testing is also a way of learning, and even the best way, though, of course, painful enoughwhen it happens during the quiz.

In the light of our experience, which gets richer as we play the challenge, I think we can agreethat NOT every behavior that we effectively experience and which is NOT specifically mentioned in theDocumentation is definitely and always a bug, even if it may look so sometimes.

In the case of the nested tables and associative arrays, the behavior is consistent for bothnested tables stored in the database and for those in PL/SQL, namely, the upper limit CAN be exceeded,at least during specific operations, so probably those arrays are implemented internallysimply as indexed by PLS_INTEGER, just like in Nikotin's quiz the INTEGER datatype was in facta NUMBER(*,0) in the database, but NOT in PL/SQL ...

Who can say that these are indeed bugs, and not just undocumented internal implementations ?( as we all know, most bugs are first considered "features" :) :) :) ... )

This case strongly resembles the issue of the inheritance from the formal parameter of a procedureto the actual value passed to that parameter, namely, if a formal parameter is of a constrained subtype, then the actual parameter DOES NOT inherit the size of that subtype,which though documented, however does contradict common sense, for ex. how can you pass/store 10 characters in a varchar2(5) variable ?

(continued)I would not be worried if further versions will change certain behaviors,as Tom Kyte so much likes to say, "things change over time and I am learning every day something new about Oracle" ... so, changes will probably bring along new quizes, with different behaviors demanding different answers...

I should witness that I am also the kind of a player that "totaly involves" himself, and the resultsare not a little bit indifferent to me, though sometimes I say to myself:"My God, if I am such a bad player and answer so slowly a quiz that afterwards may seem easy,then what at all am I looking for in this competition ?" ...But, then, I always try to come back to the real value of this challenge: every day I learn something,even if probably not immediately going to apply that (tricky) knowledge somewhere,though this also did happen and I am very content of it.

By the way, and I ask this question to Steven, to the reviewers and to all the palyers as well,how can one explain that even on a such a quiz whose answer was, ok, you are right, practically impossible to be known based on knowledge alone, there are however many players that answered it correctly in only very few seconds ???

Are they testing it during those few seconds ? Or did they previously investigate the same issue in the past and already were "equipped" with the right answer ? And not only on one or two quizes, but day after day ?

As I said with other occasions as well, as the PL/SQL challenge matures, I am sure we can expect to seemore and more quizes on very specific issues, very specialized uses of features, which probablymany of us will not have explored effectively before that.

I was always among the first ones to express how frustrating this can be ( again, I cannot forget that DBMS_XA one ... ), but the general answer was that the challenge cannot be kept forever aliveby only asking questions based on very straight-forward knowledge, though, as we all could see,even such quizes are far from being answered 100% correctly always ...

As Kim said, it is a matter of balancing , and I can only hope that ultimately if will benefit all of us.

It is definitely the case that with the new architecture, I can start a weekly or even just occasional "Weirdo" quiz. I definitely see the usefulness of making programmers aware of aberrant behaviors related to bugs. We will likely in those cases want to have a specific bug number to publish with the quiz.

I will add this as an ER; right now, I need to get the new version settled neatly into place, launch the SQL and APEX quizzes (more on that soon on this blog), and then I can start planning other "extensions" to the daily quiz.