Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

That case filter Expand / Collapse
Author
Message
Posted Tuesday, July 19, 2011 9:12 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 11:08 PM
Points: 21,385, Visits: 9,602
Comments posted to this topic are about the item That Case Filter

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?
Post #1144668
Posted Tuesday, July 19, 2011 9:17 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:44 AM
Points: 983, Visits: 1,623
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.
Post #1144670
Posted Tuesday, July 19, 2011 9:18 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, June 19, 2014 6:02 AM
Points: 1,972, Visits: 239
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.
Post #1144671
Posted Tuesday, July 19, 2011 9:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:29 PM
Points: 5,571, Visits: 24,786
[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]


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1144676
Posted Tuesday, July 19, 2011 10:55 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, November 17, 2013 11:53 AM
Points: 623, Visits: 237
Question is about how many rows returned and the ans should be 5. As query writes 5 values to the temporary table @tbl.
Post #1144694
Posted Tuesday, July 19, 2011 11:48 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Today @ 3:48 AM
Points: 687, Visits: 345
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
Post #1144721
Posted Wednesday, July 20, 2011 12:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 12:22 AM
Points: 1,297, Visits: 471
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?
Post #1144736
Posted Wednesday, July 20, 2011 12:43 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 3,198, Visits: 1,236
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."
Post #1144741
Posted Wednesday, July 20, 2011 1:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 6:24 AM
Points: 2,451, Visits: 2,342
But if that is not the case then the answer should be "There is NO query returning rows."

I agree with you!
Post #1144757
Posted Wednesday, July 20, 2011 2:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, August 21, 2012 1:15 AM
Points: 191, Visits: 224
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
Post #1144767
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse