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.

29 January 2011

"Trickery!" cry some players re: 28 January quiz (1945)

Oh, this quiz definitely irritated some players - and some of the most devoted ones, at that. This quiz was tricky, no doubt about it. Here's the part of the code that played a role in the quiz:

Notice there is no semi-colon after END LOOP. Many players submitted a bug report about this, but it is not a mistake in the quiz. Instead, since there is no semi-colon, what appears to be a "COMMIT;" statement is actually treated by the PL/SQL compiler as the label for the END LOOP statement.

When Sergey submitted this quiz, my first reaction was to say "Nah, that's just a trick; what does it teach you?" But after looking at it more closely and reading Sergey's explanation, I decided there was a useful lesson or two here:

1. Challenge your assumptions: this seemed like a classic example of a scenario in which your code is not compiling or there is some other bug, and you stare and stare at the code and cannot see the problem. Usually this is because you make an assumption, it goes unexamined, unrecognized, and it is wrong.

2. Use your IDE formatters! Using Toad, SQL Navigator, PL/SQL Developer? They all have powerful formatters to help you read and understand your code. When I reformat the above code, it turns into:

So I am very sorry that some of you felt tricked (see comments below), but (a) I do think it was an interesting and worthwhile exercise and (b) remember, it's just one of many and I truly doubt they'll be many like this!

Comments from Players

"I would have rated this an "Advanced" question - based on the results it is obvious not many people (myself included) knew that you can omit the label before the loop. Myself, I would probably have interpreted the missing semicolon as a typo. If I'd known the difficulty level was not Basic, I would have looked for more than just "what is the result of COMMIT and ROLLBACK within a procedure"."

"I think deliberately missing a semi-colon to give unexpected result, without making it obvious that had been missed is very much cheating. As is rather obvious from the ludicrously low proportion of people who got it right. And I expect most of te people who did get it "right" actually did so for the wrong reason."

"The quiz on January 28, 2011 was more of a "trick" than a test of one's PL/SQL knowledge. The code was intentionally formatted to obfuscate its intent. I didn't notice the missing semicolon, and thus answered incorrectly; however, it has been stated repeatedly that one should not expect such trickery from the PL/SQL Challenge. I am extremely disappointed that such a question was accepted."

"I could imagine myself happening this to me in real life while debugging a code and me spending an hour or so to figure out what was going wrong. And finally submitting this as a quiz on PL/SQL Challenge. And getting delighted to see only 7% of all players could get it right)). To my opinion this quiz tests more of your debugging skills than PL/SQL knowledge. I am not complaining in any way but saw this type of quiz first time on PL/SQL Challenge so thought of sending a note. I would be more careful in reading the code now onwards PS: When I encountered this quiz it look so straightforward to me that in spite of knowing the correct answer (which was wrong of course) I tempted to actually write a sample code and test it before submitting my answer. I did that and confirmed that my answer was indeed right but it was wrong of course because I tested my sample code with semicolon on."

"While I'm not really in a position to cry foul with the 28/1/11 quiz, it does seem a little against the typical tone of the quizzes. Sure, there is a lesson to be learnt here, but I'm not entirely sure this was the way to teach it. No doubt you will get a few comments about this particular quiz, I'd be interested on your thoughts."

"Very un-funny trick in the quiz. You "forgot" the semi-colon after "end loop" so the compiler considers the commit a loop label. After all, "COMMIT" is not a reserved word as you have previously pointed out. I guess you wanted to check who would actually know and understand this feature. I can see how it could be a devious problem to debug. In a timed competition, however, I would have to call the question unfair. It seems particularly so since someone who truly does not understand transaction control might choose this answer because they are "double wrong." I enjoy your competition highly and appreciate that you put in all the effort it must take. I feel questions like this taint the experience. I look forward every week day to the Challenge. I hope to be able to continue for a long time to come."

37 comments:

Steven, I agree, there are useful lessons in the code, but not in the context of a PL/SQL Challenge quiz (at least not as it was presented). You stated two reasons for accepting the quiz, but neither seems reasonable. Point 1 would apply if we knew what the expected output was, but that is not the case. We were not asked to determine why the code wasn’t behaving as expected, instead we were asked to determine what the output would be. Given the frequent adamant statements that such minor syntax tricks would not be an intentional element of the quiz, it is not unreasonable to expect that players would assume a typo. In fact, there have been a few cases where a semicolon was inadvertently left out of code and those who answered incorrectly because they noticed the mistake received credit.

Point 2, while a valuable practice, is not applicable to the quizzes. Outside of the quiz I nearly always use a code formatter to help me spot unintended code structure; however, this is not viable with the Challenge because the code is presented as a graphic instead of text. The only way to see the code properly formatted is to enter it into an editor. For this quiz, I think it likely that one would type the missing semicolon when trying to enter the code quickly. While each of us may have differing preferences for how our code is formatted, I think it not unreasonable that we should expect the code presented in the quiz to be formatted to reflect its structure. With the exception of the COMMIT, this code in this quiz was appropriately formatted. Intentionally changing the formatting for just this portion of the code is a trick.

Early in the quiz I reported every apparent syntax error I noticed, but given the repeated assurances that these types of code defects were not and would not be an intentional part of the quiz, I quit spending time looking for them. The extremely small number of players who answered this quiz correctly indicates that others also relied on these past assurances. This quiz is the epitome of a trick question. I participate in the PL/SQL Challenge to test my knowledge of PL/SQL, not my ability to proofread code (which amounts to guesswork if one has not been informed of the codes intended behavior).

"Use your formatters" only applies if somebody typed it in exactly. Most people would have typed in what they thought they saw.

Had the question been asked with

"END LOOP commit;" it would have tested our knowledge of loop labels.

The commit was purposefully split away and indented to appear as a distinct statement and, obviously the choice of that particular word for a label was a trick. Especially when looking at the answer choices, the answers are designed to look like transaction control errors.

93% of players fell for it. 7% passed the visual acuity test.

Yes, I'm sure there were some guesses and mis-clicks in there but clearly this violates your own rule that you won't try to trick us.

On the other hand, all information to solve the question was there on the screen even if obfuscated. So, it's my fault for getting it wrong and I'm not asking for a rescore.

Well, I don't know what to say. I was among those who reported a mistake as I supposed it was really a mistake. Especially considering some of the recent quizzes where I had the same problem and my complaint was rejected. And I'm also partly on Sean's side considering the main idea of the quiz is to check PL/SQL knowledge but not your visual acuity (though I was able to note this missing semicolon at the very last moment). But - from the other side I can say I'll never forget this quiz and this possibility in PL/SQL. Especially 'cause it was something new to me - I pressed 'None' button. The method of achieving the goal doesn't really matter. I'm not here for prizes. The goal is achieved - I've learnt something new. So I cannot tell if the quiz was right or wrong.

P.S. Just one point. I was looking at the rankings and it was quite strange for me that the guy who created the quiz received 100% correctness for answering it. Does it really mean to be that way? I'm not complaining, just trying to understand ;)

This quiz rather points to the deficiency in the PL/SQL compiler. The documentation (Chapter 4 of PL/SQL User Guide and Reference, Oracle 10.2) states:"Like PL/SQL blocks, loops can be labeled. The optional label, an undeclared identifierenclosed by double angle brackets, MUST appear at the beginning of the LOOPstatement. The label name CAN ALSO appear at the end of the LOOP statement." Note MUST and CAN ALSO (uppercase is mine). To be in accordance with this statement the compiler shall report an error (at least a warning). It does not.

I was also disappointed with this trick question; not that I got it wrong, but that by not noticing the missing semicolon it appeared to be a beginner level question on transaction control. I feel that at least one answer should have been a fake compilation error. That would have drawn our attention to the missing semicolon, and got us thinking whether we had valid syntax. I probably would still have got it wrong thinking that commit is not a valid loop label, but would have put it down to a difficult but fair question, not feeling disappointed that I failed to notice one missing character!

I feel somewhat guilty about the oproar concerning the 28 Jan quiz. I mean, during my review I did not catch the missing ; until looking a second time, and the only reason I did that was due to the lack of the obvious choice. For added flavour, I recommended adding that choice and, as a consequence, pretty much everybody fell through the ice. Without the obvious choice made available, I reckon most players would have looked again, just like I did.

Despite all grief, the quiz is valid and I think Sergey provided an excellent example of why PL/SQL should reckognize a few reserved words and prevent them from being used for other things, like labels.

On a similar note, we recently had a quiz where INTEGER was used as a variable name. Does it make sense to do that, does it somehow improve readability and maintainability? I think not!

Should we rely on the IDE's to reveal issues through re-formatting of our work? I think not!

Seeing the reactions of a very devoted PL/SQL Challenge community, I think it is appropriate to suggest creation of an ER, for the purpose of limiting the use of words that have special meaning to the PL/SQL compiler. Since a big-bang solution could lead to some malfunction in existing code, a compiler warning is acceptable.

The lesson I learnt from this quizz I use favourite phrase of my favourite Gregory House: Everybody lies. Being said so often in the past that such a "typo" is not an intention and someone would not trick us with such thing and then suddenly gives such quizz.. Sad but true (using "phrase" of another favourite :p )..

I completely agree with Mike. Especially in the point of using IDEs. I never use them. Never. So I'd not use something as a label 'COMMIT' to obfuscate my own horizon, not speaking of other who will come after me to maintain the code

As I wrote already ER should be not on an use of reserved the word COMMIT as a label, but on permitted labeling of END LOOP without labeling the loop itself. It is in a cleat violation of rule described in the PL/SQL documentation (quoted in my previous post).

Submitting that question I was afraid that respected PL/SQL community would "eat me" for it. I am very sorry from tournament point of view that many of you missed some points, but I hope that price of that lost points is much less than a price of uncommited changes that may occur on Production and I hope that this question will be useful in this sense.Also many thanks to Steven who helped a lot to provide a very good explanation on this question.

I disagree with those who advocate against using code formatters to reveal code issues. Of course one should strive to write correct and readable code; however, even the most proficient programmers occasionally make mistakes. Code formatters and compiler warnings provide valuable feedback that can help one identify these lapses. These tools may be even more valuable when investigating or reviewing code that one didn’t write. As this quiz demonstrates, one can be easily influenced by expectations based on the visual structure of the code. Humans are notoriously inept when it comes to spotting minor textual mistakes. Consistent formatting provides additional visual cues that can assist one in correctly analyzing a piece of code.

This quiz used visual layout to intentionally mislead the players, much as a magician uses misdirection to trick the audience. There is a difference, however: a magician generally admits intent to trick the audience, but the players of the quiz were assured that there would be no such trickery. This quiz had several tricks: the aforementioned formatting, the deliberate choice of COMMIT as the label (as mentioned by Sean), the inclusion of ROLLBACK to reinforce the appearance of COMMIT as a statement, and the absence of the label preceding the FOR … LOOP portion of the code. This last item exposes a defect in the PL/SQL compiler: identifiers that are not defined as labels may be accepted where a reference to a label is expected.

Knowing the author’s intended topic and answer likely made it much easier for the reviewers to correctly analyze the code than it was for the players. Having this information may have made the code seem less tricky for the reviewers. There are changes to the quiz that could have reduced the appearance of trickery to the players; for example, presenting the intended output and then asking for the actual output due to an unidentified code defect.

Despite the uproar and the fact that I had it wrong too, the quiz is valid. Missing semicolons are a fact of life and are hard to debug. The code I maintain is formatted in a special way, that I don't like and for that reason I don't use the Toad formatter. I would have missed this one.Maybe time to investigate if Toad can use multiple formatting settings. My preferred choice and the format that is used in the code.

Hello All,I can hardly see how one can add anything more as a remark to this quiz.It WAS trickery, I would say even close to TREACHERY !!!

I should witness that in my about 20 years of PL/SQL I have NEVER made such a mistake of forgetting a semicolon after a statement !!!And I should also underline that I AM NOT using any code formatter !!!I think just the opposite is true:If YOU KNOW that you should put a semicolon at the end of a statement, then you will surely not forget it, while if you rely on a machine to correct your mistakes, then they may pass unnoticed by you ...

I truly think that this quiz DID NOT teach usANYTHING and the fact that almost all players made the same mistake just shows that we are normal people, normal pl/sql programmers that have a common sense of how code should look and behave !!!

But, YES !It again emphasizes those weaknesses of the PL/SQL compiler.I think that time has arrived for an essential change in the restrictions related to reserved words.In my opinion, both INTEGER and COMMIT should be considered as reserved words and be forbidden for being used otherwise that intended.The list of reserved words in not that longso that to make it impossible to have it enforced, I would say even through issuing compilation errors, not just warnings.

Also, the presence of a "label" at the end of a loop without its presence at the start of the loop should by all means have been signalled as a compilation error and I wonder that it is not !

A more severe compiler would surely not have allowed even for that space line that separates the END LOOP from its presumed "label" ...

In my opinion, the purpose of PL/SQL Challenge is also to teach the players best practicesand using reserved words in other places than expected IS CLEARLY A WRONG PRACTICE, that should be discouraged, even if (hopefully just still) allowed by the pl/sql compiler.

There exist not few organizations where the discipline of coding goes until imposing a specific number of blanks for code indentation, a.s.o. ...

The older ones like myself, that were born before the era of the IDE tools and who have always followed best practices well learnt and acknowledged and always doubled by common sense did NEVER feel the need for a mecahnical tool to do this work for them ...

It's a pity that so much was invested in toolsthat "help in correcting compilation errors",which can easily be solved by almost anyone,while most developers strive with real life and hard to solve performance issues, that most of the time remain without a satisfactory solution ... in spite of the money spent for"mechanical code reviewers".

I am sure that on a second thought, the reviewer team would have NOT chosen such a quiz for the Challenge ...It did not make anything except causing frustration to everybody ...

PLW-05004: identifier string is also declared in STANDARD or is a SQL builtin

Cause: The indicated identifier was also either: o) declared in package STANDARD, or o) a SQL builtin function, or o) a pseudo-column. This situation can result in name resolution issues since the STANDARD/builtin declaration will be chosen over the local declaration in SQL statements; however the local declaration will be chosen over the STANDARD/builtin declaration outside SQL scope. (See the documentation on PL/SQL name resolution.)

Action: Either: o) rename the given identifier, o) qualify any references to the identifier with the containing scope's name, or o) make sure the intended resolution occurs if using the identifier in SQL scope.

I have hardly ever learned as much from a quiz as from this one. This is exactly the kind of mistakes that come across in real life. And I feel it's no shame to use tools which help to find out errors like this one.IMHO the Challenge is not only about competition and awards, but mostly about learning and teaching things you didn't know before. From this point of view, this quiz has done a superb job.

If this quiz "did teach" anybody anything,then it indeed should have taught the pl/sql compiler creators that they have an ugly "flaw" in the compiler's logic:

A label SHOULD NOT be allowed to be specified in "referring" places like AFTER an END LOOP and similar, without having been specified at the relevant "defining" place, like at the start of that loop.It is the same thing as not allowing a closing bracket alone, without its opening counterpart.

Regarding IDE tools: All those that express attachment for using IDE tools do in fact forgetthat pl/sql challenge is exactly designed for NOT using any helper tool while playing,even not a simple cut/paste operation,so any recommendation related to such a toolin the context of this quiz is NOT in place.

In the real life, each one is free to act as it suits him best and as it makes him more productive.I myself was unable even to copy this quiz text word by word and reproduce the same mistake,I copied it without that mistake, just as probably many other players did ...

Without any false modesty, but I do indeed believe that those who are (still) making such mistakes should indeed NOT think yet of any competition like the Challenge ...

To be honest, this was a BEGINNER question,and not an Intermediate one ...Putting a semicolon at the end of a statementis taught I think in the first hour of the first level pl/sql class ...

It's nice to know that Oracle11g has already done something to improve the issue of warningabout such worse practices as using reserved words for custom purposes ...I would go that far as to turn it into an erroralways, not just on demand !.

If it were not for backward compatibility issues,maybe it would have been easier for Oracle to simply forbid all those usages, without caring to make differences between STANDARD package constructs, SQL built-ins, pseudocolumns, a.s.o.When it comes to best practices, ALL these should be treated alike.

to me it was beneficial. I did learn another form of mistake the compiler allows.

Is this a bad practise I do not follow? SureShould the compiler catch it? SureBut...Can I encounter it in legacy code I maintain? Sure! That's the reason I am grateful it appeared.

And this wasn't a typo. It was intended "mistake". And it wasn't malformatted to hide it. The code was formatted as any reasonable human would do it. I think that by creating "verification" code section Steven and his team of angels pretty much eliminated compile typo's. So no player should assume a "mistake" is a possible typo anymore.

You are, of course, welcome to continue commenting on this issue for as long as you'd like. I would like, however, to attempt to offer some closure and some advice.

First, while I really, really hate to read that players feel betrayed or tricked, that the Challenge might in some way be tainted for them, etc., I also feel very positive about the community that has come together around the Challenge. The level of connection that many of you have both evokes strong responses and leads to strong contributions.

Second, one thing that this thread reminded me of so strongly is that in any large, globe-spanning group of people, there will always be a strong mix of reactions and experiences. This has to do not only with the response to this quiz, but also your experiences with code in the "real world." Almost any piece of code we can imagine (or have nightmares about) has been written by someone, somewhere, or will be written tomorrow. And there are lessons to be learned from all of it. So if you ever find yourself saying "This quiz is worthless", take a deep breath and a step back, and consider the possibility that while this quiz didn't do much for you, it could very likely be of great assistance or relevance to others.

Third, if you ever experience dissatisfaction with a quiz, I strongly encourage you to consider submitting a quiz for use that demonstrates the much better way to do a quiz. It takes a whole lot of time and creative thinking to come up with five excellent quizzes a week - I will take all the help I can get, and of course I want to leverage the varied experiences you have and can bring to bear in a quiz. In other words, if you have a deep specialization in an area of Oracle technology (PL/SQL-related), share that with us through quizzes!

Fourth, please make every effort to be civil in your postings. I have, in the past, posted comments that offended some of you - with good reason (though not with intention). In this thread, one player, however tongue in cheek, accused me of lying. In another comment, one player categorically rejected another player's experience of this quiz. It's OK to say your experience was different, but everyone's interpretation and viewpoint is valid, and should be accepted as the starting point of discussion, not something you reject as if their experience is somehow invalid. So remember: in the world of email, texting and comments, we can't see your face. All we have are your words, and it is easy to take them the wrong way.

OK, back to work on version 2.0 of the PL/SQL Challenge. Won't be ready for a couple of months, but I am sure you will be delighted with the new features and flexibility. More on that to come....

I think JHall62 hits the nail on the head - if a programmer ran this code and it "worked" with incorrect results, that's part of the feedback a programmer receives in debugging code. That's missing in this can, as well as those who use IDE formatters.

Where the quiz had integer as a variable name, it was highlighted as a quiz looking at viable names. This question may have passed regular muster if it was identified as something like "why will this compile ok?"

In regard to identifying mistakes, I've found myself reading the quizzes not looking for them, hence I make the presumption that there are semi-colons at the end of each line - as there should be - moreso concentrating on the logic unless I've been otherwise directed by the question.

I did learn from this quiz, as I've learned from making silly mistakes in recent quizzes that I should have got right. I'm just not sure it was quite in the "spirit" of regular questions.

The "trick" part is not about the missing semicolon. The "trick" is that the question topic is not clear from the question text. It is very easy to assume this is beginner level question on transaction control. In the survey I clicked "Too easy" because the question very clearly in my mind was about transactions.

When I received the quiz results email I noticed the topic of the quiz stated "Labeling a PL/SQL loop" - my first reaction was "What! That wasn't the topic. They must have sent a mail about a different quiz?"

Advice to question submitters: The "topic name" of the quiz is not visible when players take the quiz. The question text by itself ought to be fairly clear to players what the question is testing our knowledge about...

Apart from that I second Stevens motion that more players submit quizzes. It is very enlightening to see other developers point of view. I'll even attempt to make a few myself, even though I am better at SQL than PL/SQL :-)

It would not be a trickery unless the PL/SQL challenge prehistory (quizes with missing semicolons, quizs where last-line slash was considered as an inherent part of PL/SQL code (it is not) and so on...But it this context - it is a trickery.

You have moved to a whole new level of deceit with this quiz. I have learned the hard way that, not only do I need to spend time deciphering the logic and intent of the quiz, but now I need to devote time to dotting every "i" and crossing every "t" - or, in this case, checking every line for a semicolon!

It goes without saying that I think this is a very unfair quiz. In the past you have used a fair amount of trickery, which I suppose is okay (more about that later). But now you expect us to watch for the most subtle typos - and, at the same time, have raised the bar for yourself with respect to absolute accuracy in every quiz.

Since I assumed the quiz was intended to delve into specific aspects of PL/SQL, I have enjoyed learning - and, occasionally, being punished for not knowing - both common and obscure details of PL/SQL. Changing the intent of the quiz to an exercise of "where's the typo?", not only undermines what I thought was the underlying philosophy, but demands a different mindset as well. And, quite frankly, I'm not sure I want to devote daily time to debugging possible typos.

Hello All,I strongly agree with Kim's remark above,I experienced exactly the same feeling and went through the same contradictions vis-a-vis this quiz ...

It is nice to have other players' quizes in the Challenge and there were really nice ones, like recently that of Gary Myers and a little earlier that of Jeff Kemp, but, exactly because our imagination is endless and, yes, as Steven said so truly, each one has a different life experience, I think that the overall close control of the Challenge team is at least at this stage most necessary.

It is true that it is very difficult to create a good quiz and I am exactly going through preparing a few these days, but I really would feel very unconfortable if someone felt that my quiz just intended to lead people away from the right answer, instead of really teaching something.

I do really appreciate Steven's feeling about the Challenge community, I personally feel less alone professionally speaking since I can express my thoughts especially on subjects that are my favorites or even defend sometimes agressively my opinions.

As a last remark and back to this poor quiz, as well as to the today's one, I still think that both of them are BEGINNER quizes and nothing more, in spite of the authors too visible tendency of "trying to be spectacular" at all price.

Though, as I saw, the author should specify a difficulty level when submitting a quiz, I think that this still should be revised and finally established by the Challenge leading team.

And yes, sometimes taking a refreshment with Steven and his team's quizes is also desirable,to "calm down" the challenge when it gets overheated ...

I hereby promise to not make every quiz a test of your ability to check each line to see if it contains a ";".

:-)

This was one quiz. It is obviously not a typical quiz.

Of course you are not going to see lots of quizzes like this - and based on the ferocity of the reaction, perhaps never again.

It would be a shame in any of you stopped playing the quiz just because you had such a strong disagreement with my judgment regarding this quiz (and to be clear: others may write the initial draft of a quiz, but once it is published on the PL/SQL Challenge, I take full responsibility).

If, however, that is what you need to do, then I wish you all the best in your work as an Oracle technologist.

I am extremely happy that I am not the only addicted one, and it's not only a figure of style !!!

Once you start, you cannot stop this daily joy or excitement, or sometimes annoyance ...

I am not sure whether sometimes Steven doesn't think by himself of already having had enough of some of us ... but in spite of all these heated discussions, I think that the PL/SQL Challenge team does have of what to be proud and does have all the reasons to only desire to see each one of us going on and growing with the Challenge, which is now part of our biographies just like that of their own !

Let's hope that no one will quit and that the big excitements are still awaiting us in this Challenge !

When I signed up to this site, I used to get really worked up about getting a question wrong for unfair reasons, like not being able to take the test because the site was down for the last 3+ hours of the day (not that I'm still bitter).

Now I think: 1) it's just a quiz, 2) what did I learn from this mistake? In other words, the plsql quiz is just like life: no sense standing around complaining; just learn your lesson and move on.

In my case, I learned to take my test earlier in the day. In this case, I learned that loop labels are not required and that commit is not a reserved word (or at least in regards to loop labels), and I learned that semicolons are super important, and I even learned to always auto-format my code if I suspect something is wrong.

The criticism is for the spirit of quizzes. Early it was "Look at root, determine a salt of question". And semicolon was never the salt early.

There are the hint ("rollback") to consider the (missing) semicolon as the salt of question. But this hint was insufficient, especially (as I can see) because of reviewers that introduce that faulty choice.

That is a pitty that the users are punished for the limitations in your IDE for the challenge. Not their skills but you presenting things incorrectly.An example question for you: Why the compiler would fail to execute the following statement?

dесlаrеbеgindbms_оutрut.рut_line('A test '' text " ');еnd;/

Why the code would fail to compile? Maybe because there are some fancy characters (I have used russian cyrylic characters there)? Or maybe I have misplaced an invisible character instead of invisible space? Or maybe...Please, try to copy/paste it and execute...