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:
DECLARE
TYPE t_bug_type IS TABLE OF all_source%ROWTYPE
INDEX BY SIGNTYPE;
v_bugs t_bug_type;
v_ndx PLS_INTEGER;
BEGIN
BEGIN
SELECT *
BULK COLLECT INTO v_bugs
FROM all_source
ORDER BY owner, name, line;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('ERROR');
END;
DBMS_OUTPUT.put_line (
'First = '
|| v_bugs.FIRST
|| ' Last = '
|| v_bugs.LAST
|| ' Count = '
|| v_bugs.COUNT);
v_ndx := v_bugs.FIRST;
DBMS_OUTPUT.put_line (v_bugs (v_ndx).text);
v_ndx := v_bugs.LAST;
DBMS_OUTPUT.put_line (v_bugs (v_ndx).text);
BEGIN
v_bugs (v_bugs.LAST).text := 'Oracle has many bugs !!';
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (
'You should not create quizzes based on bugs !!');
END;
END;
/
Oracle has many bugs, in my opinion it is not intended to make quizzes based on bugs.
So...what do you think?
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collection_definition.htm#sthref2535
ReplyDelete"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.
ReplyDeleteI can see a few sides to this.
ReplyDeleteIf 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.
Knowledge of "bugs" teach us to be better PL/SQL developers. Bottom line - I believe "bugs" are a fair and good topic for the daily quiz.
ReplyDeleteBugs to some could be a feature for others.
ReplyDeleteIt's a challenge. You take it in a true spirit. Know the facts and learn a bit.
I say bring it on...bugs, features, tricks, pitfalls etc.
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.
ReplyDeleteThe following lessons may be gleaned from the quiz on May 24 (as well as the quiz on May 17 when considering the scoring challenge):
1. Oracle’s PL/SQL compiler isn’t perfect.
2. Oracle’s PL/SQL runtime isn’t perfect.
3. Code that successfully compiles despite violating language specifications may behave in unexpected ways.
3. Always run the code in the quiz before answering; don’t rely on knowledge of specified behavior (you might be punished).
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.
ReplyDeleteOne 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.
ReplyDeleteI'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.
ReplyDeleteLike 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.
ReplyDeleteBut 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.
ReplyDeleteHello All,
ReplyDeleteI 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 the
PLS_INTEGER type's upper limit itself, it was however proven afterwards that
a 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 different
collection 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 elements
into 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 TABLE
behavior with regard to populating through a BULK COLLECT, and which also
offers the possibility to have its INDEX type declared as a "smaller range" type
than the usual PLS_INTEGER ...
I did not see any interdiction documented for using a SUBTYPE in any place where its parent type
can 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 )
== PART2 ==
ReplyDeleteBut I can suggest also the following test case:
rem - index by a user-defined subtype
DECLARE
SUBTYPE t_index IS PLS_INTEGER RANGE 1..100 ;
TYPE t_parts IS TABLE OF plch_parts%ROWTYPE
INDEX BY t_index ;
l_parts t_parts;
BEGIN
BEGIN
FOR rec IN (SELECT * FROM plch_parts ORDER BY partnum)
LOOP
l_parts(rec.partnum) := rec;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
-- DBMS_OUTPUT.put_line('ERROR');
DBMS_OUTPUT.put_line(SQLERRM);
END;
DBMS_OUTPUT.put_line( l_parts(l_parts.LAST).partname );
BEGIN
SELECT * BULK COLLECT INTO l_parts
FROM plch_parts
ORDER BY partnum ;
EXCEPTION
WHEN OTHERS
THEN
-- DBMS_OUTPUT.put_line('ERROR');
DBMS_OUTPUT.put_line(SQLERRM);
END;
DBMS_OUTPUT.put_line( l_parts(l_parts.LAST).partname );
END;
/
I thought even of suggesting such a case for the quiz itself, because a range of 100 elements
looks much more useful in practice than one of 3 rows only ... and I expected objections from
this 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_INTEGER
as an array index in PL/SQL.
If Oracle were considering such a restriction as limiting, then it would have probably
implemented a compiler error similar to the one received when you try to BULK SELECT into
an associative array indexed by a VARCHAR2(n), namely:
PLS-00657: Implementation restriction: bulk SQL with associative arrays with VARCHAR2 key is not
supported.
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_INTEGER
index type when using BULK SELECT ... (as I reminded at the beginning, for a nested table
stored 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 behavior
is 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.
( to be continued )
== PART3 ==
ReplyDeleteIf 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 column
but 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 without
raising the "PLS-00307: too many declarations of match this call" error.
Reading of a few suggestions about having a separate "Extreme" or "Trivia" quiz
just brings me one month back to that DBMS_XA quiz ... when I suggested something similar
for "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.
Hope that many others still think the same.
Best Regards,
Iudith Mentzel
Iudith,
ReplyDeleteI 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.
ReplyDeleteKnowledge 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.
Hello All,
ReplyDeleteOk, these last 2 posts sound indeed more peaceful ...
I strongly agree with Kim's remark that the feeling of having to test code out for answering correctly
is 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 function
called 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 without
testing 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 enough
when it happens during the quiz.
In the light of our experience, which gets richer as we play the challenge, I think we can agree
that NOT every behavior that we effectively experience and which is NOT specifically mentioned in the
Documentation 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 both
nested 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 internally
simply as indexed by PLS_INTEGER, just like in Nikotin's quiz the INTEGER datatype was in fact
a 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 procedure
to 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 ?
(to be continued)
(continued)
ReplyDeleteI 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 results
are 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 see
more and more quizes on very specific issues, very specialized uses of features, which probably
many 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 alive
by 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.
Best Regards & Good luck further !
Iudith Mentzel
What an excellent set of comments and suggestions!
ReplyDeleteIt 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.
Thanks so much for your feedback and help!
SF