July 20, 2011 at 2:42 am
Misleading but got it right. I took a chance going with 0 as the answer is really that no rows are requested as its an Insert query, but the cloest answer to that for me, was 0 - so 😀
July 20, 2011 at 2:49 am
I would replace the question "How many rows returned by executing all of the following?" in "How many rows returned by the last SELECT?" and add another answer: "None, because of no SELECT in the batch".
July 20, 2011 at 2:56 am
My original thought was that there would be no rows returned as this was an insert.
But I then thought that was silly, and I was being unfair on the questioner who had made an honest and obvious mistake - there are too many questions that provoke pedantic discussions whyen the meaning of the question is clear.
So I answered the question as if it had been 'how many rows are inserted'.
Given recent questions, I think this was a fair assumption to make.
July 20, 2011 at 2:56 am
Excellent question. I got it wrong (even after runnign the code) but there's a lesson there for all of us for whom getting things right depends on how we interpret a somewhat unclear specification.
July 20, 2011 at 2:59 am
Well, I think this was a useful reminder to us to actually look at the code. Yeah you might have missed a point and there's an 'incorrect' answer against your name but if you read it properly before you answer you'll get it right. I don't think this was designed to trip us up so much as to test our debugging skills, when the OP was working on his issue he would have been thinking "What's wrong with my case filter?" so why not pose that question to us? It's real-world.
If we can't so much as read a simple question properly on a simple 1-pointer, how are we expected to fully comprehend a business' requirements when we are developing some code from a functional spec etc.
From my PoV the QotD is there, not as a status symbol or as bragging points but rather to help us do our jobs better. Sometimes that means showing us things we didnt already know, sometimes asking really complicated questions and other times simply to keep us on our toes so we don't get complacent in our day to day job. As the OP states, this came from a cut and paste error - we all get those from time to time and this is a good reminder to take a step back and really look at our code when we get unintented behaviour that we can't get our heads around.
//edit - don't forget, if this question - or it's answer - was actually wrong it wouldn't have been published.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
July 20, 2011 at 3:01 am
It seems that the question is trying to trick us completely: Either you read it as how many rows are returned by the Select - where the answer should be "there is NO select" to remain symanitically correct. Or you read the question with the last UNION ALL and realise there is nothing in the table for the last query so you have the initial five rows, which is five rows affected!
Do not try and trick us with the query and then blame copy and paste in denying everyone their points for logically trying to understand what you were thinking? Especially since the post title is to do with Case statements!
July 20, 2011 at 3:05 am
BenWard (7/20/2011)
don't forget, if this question - or it's answer - was actually wrong it wouldn't have been published.
Sadly that's not the case. There have been many recent questions with minor typos, how were we to know that this wasn't another?
July 20, 2011 at 3:17 am
Toreador (7/20/2011)
BenWard (7/20/2011)
don't forget, if this question - or it's answer - was actually wrong it wouldn't have been published.Sadly that's not the case. There have been many recent questions with minor typos, how were we to know that this wasn't another?
oh well I thought they were read over by the good people who run this place first.
It would appear that people are trying to overcomplicate the question:
"0 seems like a silly answer therefore I will assume the OP meant something entirely different from what he actually asked and answer accordingly... What? My answer was wrong? Don't be silly the questin was wrong!"
🙂
In management studio a query often outputs to 2 tabs, Messages and Results.
If anything is RETURNED by a query it goes in Results.
If anything is PRINTED by a query it goes to Messages.
'(5 rows affected)' comes up in Messages and there is no 'Results' tab. There is no results tab because there were no results, logically, 0 rows have been returned by the query batch.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
July 20, 2011 at 3:20 am
I think this question is ambiguous and could have been asked in a diferent way to buttress the point.
Anyways, one less point for me today 🙂
Kwex.
July 20, 2011 at 3:22 am
Dhruvesh Shah (7/19/2011)
Question is about how many rows returned and the ans should be 5. As query writes 5 values to the temporary table @tbl.
Got it wrong for this exact reason. There is no select statement so of course no records will be returned so I assumed they were asking how many records were going to be inserted into the temporary table. I did not miss the extra UNION ALL and knew it would insert only 5 records.
As someone else put it in an earlier QOTD, this one ranks high on the QOTD-sneak-o-meter.
July 20, 2011 at 3:30 am
BenWard (7/20/2011)
From my PoV the QotD is there, not as a status symbol or as bragging points but rather to help us do our jobs better.
Sorry but this one will not help me do my job better. It only serves to give me anxiety and provokes an emotion of rage.
July 20, 2011 at 5:24 am
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.
It's also a good reminder to not rush through things and not work when you're too tired (which I was that afternoon).
This is also meant as a fun discussion starter. You can use this scenario to laugh at me if you want (God<Some supreme being> knows I did).
So tell us about the last time you did something dumb, stupid, funny like this with Sql Server?
July 20, 2011 at 5:26 am
I saw the extra Union/gotcha in this Qotd...but...
Qotd (as far as I understand it) is generally about teaching (at least some of us) something that we weren't aware of, not sure that this one does that. Apart from attention to detail I guess...
Carlton.
July 20, 2011 at 5:29 am
Dhruvesh Shah (7/19/2011)
Question is about how many rows returned and the ans should be 5. As query writes 5 values to the temporary table @tbl.
My choice of words was really carefull here. The question says rows RETURNED by that statement. Not rows selected. That's the least tricky question Steve and I could come up with that still had value to offer.
Please join in on the fun and tell us about the last time you felt foolish about yourself!
Viewing 15 posts - 16 through 30 (of 200 total)
You must be logged in to reply to this topic. Login to reply