July 19, 2011 at 9:12 pm
Comments posted to this topic are about the item That Case Filter[/url]
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 19, 2011 at 9:17 pm
Interesting question... Has nothing to do with a case filter at all. This is a gotcha question seeing if you can spot the extra UNION ALL... it's somewhat misleading.
I don't care about points personally... I just hate when you ask a question and then the answer is not the question itself.
July 19, 2011 at 9:18 pm
I think that this question is ambiguous and should be withdrawn.
There is NO query returning zero rows in the question.
There is one insert statement yielding five rows affected.
You might have missed the extra UNION ALL but others will just see it as it is and think it is a question about attempting to reference rows yet to be inserted.
Regards, Murray.
July 19, 2011 at 9:33 pm
[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]
July 19, 2011 at 10:55 pm
Question is about how many rows returned and the ans should be 5. As query writes 5 values to the temporary table @tbl.
July 19, 2011 at 11:48 pm
Hi bitbucket-25253,
The question says "How many rows returned by executing all of the following?" and there is only insert statement,
the rows would be returned only when we select from @tbl.
If you write SELECT * FROm @tbl at the end then it will return you the 5 rows.
Please correct me if I am wrong.
Deepak Kumar Sharma
July 20, 2011 at 12:29 am
I got the right answer, but please stop questions about CASE having really nothing to do with CASE itself! Or is QOD about fooling us around?
July 20, 2011 at 12:43 am
murray-906152 (7/19/2011)
I think that this question is ambiguous and should be withdrawn.There is NO query returning zero rows in the question.
There is one insert statement yielding five rows affected.
You might have missed the extra UNION ALL but others will just see it as it is and think it is a question about attempting to reference rows yet to be inserted.
Regards, Murray.
I fully agree with this statement.
I didn't miss the fact that UNION ALL was before the select, so thought that the question was about the 5 rows that were affected, instead of returned.
But if that is not the case then the answer should be "There is NO query returning rows."
July 20, 2011 at 1:33 am
But if that is not the case then the answer should be "There is NO query returning rows."
I agree with you!
July 20, 2011 at 2:02 am
murray-906152 (7/19/2011)
I think that this question is ambiguous and should be withdrawn.There is NO query returning zero rows in the question.
There is one insert statement yielding five rows affected.
You might have missed the extra UNION ALL but others will just see it as it is and think it is a question about attempting to reference rows yet to be inserted.
Regards, Murray.
QFT
/@devandreas
July 20, 2011 at 2:02 am
Hello,
The question is missleading !!!
The question is "How many rows returned by executing all of the following?" , and when I executed the script the result was "(5 row(s) affected)", there is no select statement ,If you write SELECT * FROm @tbl at the end then it will return you the 5 rows. !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
July 20, 2011 at 2:02 am
Hi Guys,
I agree with most comments on this question, asking us how many rows would bereturned should not have been the way to put it. As we know the answer one would get is "(5 row(s) affected)" because of the extra UNION ALL.
Also there's a huge difference between 0 Rows being returned and there being no Rows selected. In this instance the answer of 0 rows returned also becomes incorrect.
Regards,
Quilz 😎
July 20, 2011 at 2:04 am
This was removed by the editor as SPAM
July 20, 2011 at 2:19 am
I have to agree with the dissenters. I wasn't looking for a mistake and I too decided that it was testing an insert where it was conditional on rows earlier in the insert. I did initially think that as it was only an insert then there would be no rows returned, and assumed this was a case of a misused term in the question so put 5, as the number of rows that would be returned from the select and inserted into the table.
It's ambiguous at best. It only 'tests reading and reasoning' if you're specifically looking for something to analyse. We didn't know what you were trying to do, therefore we can't know that the extra union all was unintentional.
-------------------------------Oh no!
July 20, 2011 at 2:36 am
This was ambiguous and misleading!!! :angry:
Viewing 15 posts - 1 through 15 (of 200 total)
You must be logged in to reply to this topic. Login to reply