That case filter

  • Ninja's_RGR'us (7/20/2011)


    bitbucket-25253 (7/19/2011)


    [Rant]

    After getting the supposed incorrect answer, I cut and pasted the code into SQL 2005 and then again into 2008. Ran each not less than 15 times and ALWAYS got 5 rows ...

    Can anyone explain WHY or REASON for my results?

    I do respect Ninja's_RGR'us knowledge and experience, but ah well

    [/Rant]

    Hey guys, here's the full story behind that question. And no there is no typo in it.

    I was trying to debug something fast for someone on ssc.com when I ran the exact code posted in the QOTD. I swear it took me a good 5 minutes to realize the extra union all was there (copy / paste error).

    By then I was wasting my time researching KB bugs about different set options.

    Steve and I went back and forth with this question and the exact wording to use. The bottom line was that I wanted you to be in the same situation I was when I was facing this "issue". I spent the first 2-3 minutes trying to debug the case filter and then about the same time researching for bugs and behavior on set options.

    Also the title was misleading so not to give the answer away (like I wasn't going to name this "Too many union alls???").

    I went back and forth on wether or not to post the question because I knew some of you wouldn't like it but I think this is a valuable lesson (it was for me anyways).

    When something just doesn't make sense you need to test the most basic assumptions.

    I respect why you did it, and I don't really think QotDs are really worth getting in too much of a huff over.

    But I'm not sure wording the question like this accomplishes your actual goal. As you say, you struggled with this for awhile because you had an output, and an expected output, and they didn't mesh, and you didn't know why. That was your problem to solve.

    But as a QotD this doesn't do that. It asks for expected output and is thus really just about reading comprehension. It is "can you spot the bug on first pass". Or perhaps more appropriately for some "can you sense from the ease of the question that its a trap, and look more closely for a problem before answering". (In my case you did get me. My spidey sense tingled as the problem seemed too basic, but by then I was focused only on the end select and not the insert, so I checked that closely, then shrugged and gave the stock answer).

    That has some value too, but does not simulate your real world "why doesn't this work" problem you encountered.

  • Shark Energy (7/20/2011)


    I can understand people complaining if there was a typo that meant the answer was wrong but to complain that you "assumed" the extra UNION ALL was a mistake, righted it and gave the wrong answer is ridiculous.

    Play the question in front of you - and then if it turns out there was an error they will put it right and give the points back.

    On the flip side of the coin though - I think maybe "Nothing" rather than "0" would have made more sense on the answer list but I can also see that this would have made it completely obvious and take away less analysis/thought.

    Thanx for the tip. I hadn't considered that wording option.

    Maybe next time :Whistling:

    .

    .

    .

    but I wouldn't hold my breath waiting for a next time 😀

  • cengland0 (7/20/2011)


    Ninja's_RGR'us (7/20/2011)


    BTW I can lend you some points of mine if that can dissipate some of that anxiety :w00t: :Whistling:

    Thanks but it's not about the points. I'm a perfectionist with OCD so I take these QOTD's very serious. I think of them as a test of my knowledge not as a way to learn but if I miss a question then I'll read about it and learn why.

    Sorry to hear that.

    I can't really help there. Maybe you could go order your ocd medecine by name and then by color untill the anxiety goes away :-D.

    (Stole that line from some SQL MS MVP, can't think of his name atm).

  • Ninja's_RGR'us (7/20/2011)


    Also I'd love to know how you would have phrased that question without giving the answer away.

    Ask it like a real-world situation.

    1. Put line numbers by each line of your code.

    2. State that you expected to get x number of results but you get no results

    3. As the reader of the QOTD to identify which line of code is causing the problem

  • cengland0 (7/20/2011)


    Ninja's_RGR'us (7/20/2011)


    Also I'd love to know how you would have phrased that question without giving the answer away.

    Ask it like a real-world situation.

    1. Put line numbers by each line of your code.

    2. State that you expected to get x number of results but you get no results

    3. As the reader of the QOTD to identify which line of code is causing the problem

    Thanks, I hadn't considered that approach, I'll keep that in mind to [not] trick you better next time ;-).

  • Nevyn (7/20/2011)


    I respect why you did it, and I don't really think QotDs are really worth getting in too much of a huff over.

    But I'm not sure wording the question like this accomplishes your actual goal. As you say, you struggled with this for awhile because you had an output, and an expected output, and they didn't mesh, and you didn't know why. That was your problem to solve.

    But as a QotD this doesn't do that. It asks for expected output and is thus really just about reading comprehension. It is "can you spot the bug on first pass". Or perhaps more appropriately for some "can you sense from the ease of the question that its a trap, and look more closely for a problem before answering". (In my case you did get me. My spidey sense tingled as the problem seemed too basic, but by then I was focused only on the end select and not the insert, so I checked that closely, then shrugged and gave the stock answer).

    That has some value too, but does not simulate your real world "why doesn't this work" problem you encountered.

    Thanks, amazing feedback.

    Sending points your way as we speak. 😉

  • What mistake (please re-read my full expaination in first post)?

    !

    Are you being serious you want me to read your explaination AFTER answering the question?

    Really?

    The explaination of the questions are now getting worse then the questions themselves.

  • Richard Warr (7/20/2011)


    I think all the people who cheated by running the code first and still managed to get the question wrong, and then complain about it, may be taking things just a little too seriously.;-)

    Good, I thought I was the only one that tries to answer the questions without running the code?

  • So can I take it from all the chatter this morning that we will have no shortage of submissions for future QoTD? 😛

    Thanks for the question. Totally got where you were taking this. I guess the lesson to take away is not to judge a QoTD by the title. 😀

  • mbova407 (7/20/2011)


    What mistake (please re-read my full explaination in first post)?

    !

    Are you being serious you want me to read your explaination AFTER answering the question?

    Really?

    The explaination of the questions are now getting worse then the questions themselves.

    I explain my frame of mind and thoughts process in the first post of this thread. It clears out the most obvious "issues" with the question.

  • Ask it like a real-world situation.

    1. Put line numbers by each line of your code.

    2. State that you expected to get x number of results but you get no results

    3. As the reader of the QOTD to identify which line of code is causing the problem

    Amen, now there is a question worth answering.

  • OzYbOi d(-_-)b (7/20/2011)


    So can I take it from all the chatter this morning that we will have no shortage of submissions for future QoTD? 😛

    Thanks for the question. Totally got where you were taking this. I guess the lesson to take away is not to judge a QoTD by the title. 😀

    You can't count on it. Sometimes that's the only (easiest) to conceal the answer and give any value to the question.

    BTW anyone took the MS certs? Those questions ain't easy either from what I've heard.

  • "How many rows returned by executing all of the following?"

    I ran "all of the following" under 2005 Enterprise and Express and under 2008 R2 Datacenter, Standard, Developer and Express and in every single case I got 5 rows.

  • mbova407 (7/20/2011)


    Ask it like a real-world situation.

    1. Put line numbers by each line of your code.

    2. State that you expected to get x number of results but you get no results

    3. As the reader of the QOTD to identify which line of code is causing the problem

    Amen, now there is a question worth answering.

    How do you add the pressure of real-life in it? That version of the questions seems a little too easy (people like to have harder questions after years doing this. Level 200 or 300 if possible).

  • Revenant (7/20/2011)


    "How many rows returned by executing all of the following?"

    I ran "all of the following" under 2005 Enterprise and Express and under 2008 R2 Datacenter, Standard, Developer and Express and in every single case I got 5 rows.

    If you didn't edit the question and that "returned" means you have a dataset returned then I have no clue how that's possible.

    BTW returned <> from the rows affected message.

    Maybe I should have added SET NOCOUNT ON to remove some of the confusion.

Viewing 15 posts - 91 through 105 (of 200 total)

You must be logged in to reply to this topic. Login to reply