May 9, 2013 at 3:24 pm
A good example that things sometimes are not as simple as they seem to be at the first glance. Thanks!
May 9, 2013 at 3:50 pm
A nice way of showing how a spelling error can have bizarre results as a consequence of a piece of what (despite Hugo's explanation, which is nice and clear) is clearly appallingly bad language design, more an aid to obfuscation than an aid to writing clear and concise code.
Sensible language design would say that the items in the select list should be assumed to be either literal constants, @ variables, or column names from the tables listed in the from clause of the select statement begun by the select which introduces the list unless they are explicitly tagged with a table name or alias referring to an outer scope. I don't suggest that such a rule is needed for items occurring elsewhere in the statement (in ON and WHERE clauses) but for the select clause I don't believe that it is at all reasonable not to have it.
Tom
May 9, 2013 at 7:29 pm
Very nice question. I could definitely see this happening in real world scenarios. I have to share this one with my coworkers. Nice job!
May 9, 2013 at 10:19 pm
I agree - really good question. I originally didn't like that the difference in column names was so subtle, but after thinking about it for a bit I think it was a good idea. It demonstrates the behaviour of SQL Server, and, how easily a mistake could be made.
Thanks
May 9, 2013 at 10:53 pm
Nice one.....
May 13, 2013 at 2:53 am
Bit of a trick question, but nice nonetheless.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 13, 2013 at 7:14 am
Koen Verbeeck (5/13/2013)
Bit of a trick question, but nice nonetheless.
+1 Unfortunately, I didn't pick up on that until it was too late.
May 15, 2013 at 3:12 am
Good Trick Question.
I don't really like SQl server when it works by Accident raher than Logic.
David
May 16, 2013 at 4:17 pm
nuh.. uh.. the 'correct' answer is wrong. This will produce a syntax error due to the misspelt column name in the subquery.
May 17, 2013 at 12:53 am
james.of.rivendell (5/16/2013)
nuh.. uh.. the 'correct' answer is wrong. This will produce a syntax error due to the misspelt column name in the subquery.
No it won't. Try running it yourself, and maybe even reading some of the other comments.
May 23, 2013 at 2:16 am
It was very tricky. I would have easily fallen for it had I not experienced the same issue in one of my system. Thanks for this cool quesion. 🙂
June 14, 2013 at 5:14 am
subtility !
September 17, 2013 at 8:03 am
timothys (5/9/2013)
That was a great question. Especially the similarity of the column names to demonstrate how easily a typo can be overlooked and still return what you expect (since in this case the primary keys matched). A better example may have been to have the primary keys not match and the three rows are still returned. I think that would have caused some head scratching from some people.
Jepp, great idea to complicate it. I guess it would be even more realistic and an example of our daily business...
I really laughed when I found the result: I have to work on my concentration :w00t:
________________________________________________________
If you set out to do something, something else must be done first.
Viewing 13 posts - 31 through 42 (of 42 total)
You must be logged in to reply to this topic. Login to reply